Data Querying And Analysis

An analyst is using a CTE (Common Table Expression) to pre-aggregate sales data before performing a join. What is the primary benefit of using a CTE instead of a subquery in this scenario?

When joining two tables in SQL where you need to return all records from the left table even if there is no match in the right table, which JOIN type is appropriate?

A data analyst is writing a SQL query to retrieve unique department names from the 'Employees' table. Which keyword should be utilized to ensure the results do not contain duplicate entries?

In Power BI, which visualization component is best suited to show the relationship between two numerical variables and identify potential trends or clusters?

When cleaning a dataset, you notice that column names contain inconsistent casing (e.g., 'Name', 'NAME', 'name'). Which transformation step should be prioritized to facilitate join operations?

A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?

You are debugging a SQL query that uses an aggregate function. You receive an error stating that a column in your SELECT list is not part of an aggregate or a GROUP BY clause. What is the cause?

A report needs to identify the 'top 10' customers by total spend. In SQL, which clause would you use to return only the first 10 results after sorting by spend descending?

In data visualization, what is the 'Data-Ink Ratio' intended to improve?

When working with time-series data, what does a 'lag' feature represent in an analysis?

Which of the following is an example of an unstructured data source that a data analyst might need to ingest?

An analyst notices that a database query is performing full table scans even though a WHERE clause is present on a indexed column. What is a likely reason for this?

When performing sentiment analysis on customer feedback, which preprocessing step is most critical to ensure words like 'Running' and 'run' are treated as the same base concept?

Which dashboarding practice helps prevent users from being overwhelmed by too much data at once?

What is the benefit of using a 'Dashboard' over a static report?

What does the 'P' in the acronym 'KPI' stand for?

When analyzing JSON data stored in a relational database using SQL, how do you typically access nested key-value pairs?

When creating an ETL pipeline, which stage involves validating that the data meets business rules (e.g., age must be positive)?

When normalizing a database, what is the primary goal of moving data into a third normal form (3NF)?

When troubleshooting a performance bottleneck in a visualization, which action is most effective if the data source is a large SQL view?

When using Python's 'Matplotlib' library, which function is used to add a new figure to the canvas?

Which SQL command is used to add new records to an existing table?

In the context of data quality, what does 'Completeness' refer to?

Which of the following is a limitation of the 'Star Schema' design compared to a fully normalized schema?

Which TWO of the following are common types of data visualizations used to represent trends over time?

Which THREE of the following are components of a standard SQL SELECT statement?

Which THREE of the following SQL aggregate functions are commonly supported in most relational databases?

Which THREE of the following are common data types in relational databases?

Which TWO of the following actions are considered best practices for maintaining data integrity in a data pipeline?

Which TWO of the following are legitimate ways to optimize SQL query performance?

Which TWO of the following are benefits of using a data warehouse?

Which THREE of the following are examples of descriptive statistics?

Which THREE of the following are types of data joins in SQL?

Which THREE of the following represent common data transformation tasks in a preparation workflow?

Which TWO of the following are common issues encountered when integrating data from disparate sources?

A data engineer is profiling a customer table and finds that the 'email' column contains 12% NULL values, while the 'customer_id' column is fully populated and unique. The business rule states that every customer record must have a valid email address. Which action should the engineer take to enforce this rule in the pipeline?

A data analyst is writing a query to find the average order value (AOV) per customer from an orders table that includes cancelled and returned orders. They want to exclude those orders from the calculation but still show all customers, including those with no valid orders. Which SQL construct should they use to ensure customers with no valid orders appear with a NULL or zero AOV?

A data analyst is querying a sales table with columns order_id, order_date, customer_id, and order_total. They need to return each order along with the running total of order_total for each customer, ordered by order_date. Which SQL feature should they use?

A junior data analyst runs the following query against a retail sales table and receives 42,000 rows instead of the expected 12,000: SELECT c.customer_id, c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_status <> 'cancelled'; Which action should the analyst take to return all customers, including those with no orders, while still excluding cancelled orders?

A data analyst needs to retrieve the average order value for each region from a sales table. The table has columns region, order_id, and order_total. Which SQL statement correctly returns one row per region with its average order value?

A data engineer must load a 400 GB Parquet dataset from an object store into a columnar warehouse. The source files are partitioned by date but each partition contains many small files averaging 5 MB. Queries against the loaded table are slow because the warehouse scans far more data than necessary. Which action most directly improves query performance by reducing scanned data?

A data analyst runs a query against a PostgreSQL 15 data warehouse to calculate month-over-month revenue growth. The query uses the LAG window function partitioned by region and ordered by month. The analyst notices that the first month for each region returns a NULL growth value, which is causing downstream dashboard errors. Which action should the analyst take to handle this NULL value appropriately?

A data engineer is profiling a customer table and notices that the Email column contains several NULL values, duplicate addresses, and inconsistent formatting such as mixed uppercase and lowercase characters. The engineer wants to quantify how many rows have a NULL Email before deciding on a cleansing strategy. Which SQL expression should be used to return that count?

A data analyst is building a monthly customer churn report. The churn definition is: a customer is churned in month M if they had at least one transaction in month M-3 but zero transactions in months M-2, M-1, and M. Which SQL approach correctly identifies churned customers for each month without missing customers who simply had no activity at all?

An analyst joins a large fact table to a small lookup table using an inner join and notices that the result contains more rows than the original fact table. After reviewing the data, the analyst discovers that the lookup table contains duplicate values in its join key. Which action should the analyst take to ensure each fact row is matched to at most one lookup row?

A data analyst is using a subquery in the WHERE clause to filter customers who have placed at least one order. The subquery returns customer_id values from the orders table. Which SQL construct correctly expresses this requirement?

A data analyst is reviewing a query that joins three tables: orders, customers, and products. The query returns more rows than expected. Which TWO of the following are likely causes of the row multiplication? (Choose two.)

A business analyst is preparing a quarterly sales report and needs to categorize customers into 'High', 'Medium', and 'Low' value tiers based on their total annual spend. The analyst writes a SQL query against a Microsoft SQL Server database and wants to assign the tier label in the result set without modifying the underlying table. Which SQL construct should the analyst use?

A data analyst is preparing a query to compare average order values across customer segments. Before running the analysis, the analyst reviews the dataset and finds that the OrderAmount column has missing values and extreme outliers. Which TWO actions should the analyst take to improve the reliability of the comparison? (Choose two.)

A data analyst is querying a large partitioned table in Apache Hive that stores clickstream events. The table is partitioned by event_date and bucketed by user_id. The analyst needs to retrieve the latest event for each user within the last 30 days. The query currently uses a subquery with MAX(event_timestamp) and a join back to the table, but it runs slowly. Which optimization should the analyst apply to improve performance while preserving the correct result?

A data analyst is using a window function to rank products by sales within each region. They write: RANK() OVER (PARTITION BY region ORDER BY sales DESC). They notice that products with identical sales receive the same rank, but the next rank is skipped (e.g., 1,2,2,4). They want to assign consecutive ranks without gaps. Which function should they use instead?

A data analyst is building a report that joins a fact table of retail transactions to a dimension table of products. The product dimension contains a column 'category' that is frequently used in filters, but the analyst notices that many transactions have a NULL category because the product was not properly matched during the ETL load. The analyst wants to ensure that all transactions appear in the report, even when the category is missing, and that missing categories are labeled as 'Uncategorized'. Which SQL join type and function combination should the analyst use?

A data analyst needs to calculate the total sales for each product category, but only for categories where the total sales exceed $10,000. The sales table contains columns sale_id, product_id, category, and amount. Which SQL query correctly produces the desired result?

A data analyst is examining a query that uses a LEFT JOIN between a customers table and an orders table. The analyst notices that some customers appear multiple times in the result set. Which of the following is the most likely cause?

A data analyst is working with a dataset that contains a column with values such as 'N/A', 'null', ' ', and 'unknown'. The analyst needs to count the number of truly missing values in this column. Which approach is most appropriate?