DS0-001 Data Querying And Analysis — 20 Questions

Question 1

Which SQL function is used to convert a NULL value in a query result into a default value, such as 0?

Accepted Answer

COALESCE. COALESCE is the ANSI SQL standard function that returns the first non-NULL expression from a list, making it the portable, standard-compliant way to substitute a default value for NULL. It is supported across virtually all major RDBMS platforms (PostgreSQL, SQL Server, Oracle, MySQL), unlike vendor-specific alternatives.

Answer

NVL

Answer

IFNULL

Answer

ISNULL

Question 2

You are performing data transformation in Python using Pandas. You have a 'DataFrame' with missing values in the 'Revenue' column. Which method should be used to replace these missing values with the column mean?

Accepted Answer

fillna(). The fillna() method in Pandas is specifically designed to fill missing values (NaN) with a specified value or method. To replace missing values in the 'Revenue' column with the column mean, you would use df['Revenue'].fillna(df['Revenue'].mean()). This directly addresses the requirement by substituting each NaN with the computed mean. Other methods like replace() can substitute values but are not tailored for NaN imputation with a statistic like mean; dropna() removes missing values; interpolate() estimates missing values based on other data points; and map() applies a function element-wise but does not handle NaN filling by default.

Answer

replace()

Answer

dropna()

Answer

interpolate()

Answer

map()

Question 3

A data analyst needs to combine data from three tables. If the tables are joined sequentially, what is the result called?

Accepted Answer

Data denormalization. A series of joins creates a combined result set, often referred to as a denormalized view or result set.

Answer

Data normalization

Answer

Data aggregation

Answer

Data pivoting

Question 4

Which of the following is a 'categorical' variable?

Accepted Answer

Product category. A categorical variable represents discrete groups or labels that cannot be meaningfully ordered or measured numerically. 'Product category' is a label (e.g., Electronics, Clothing) and is therefore categorical. The other options are numerical and represent continuous measurements.

Answer

Time taken to complete a task

Answer

Temperature in Celsius

Answer

Price in dollars

Question 5

Which TWO of the following are commonly used methods for handling outliers during data analysis?

Accepted Answer

Deleting all rows containing outliers. Deleting all rows containing outliers (C) is a commonly used method because it removes extreme values entirely so they cannot distort statistical measures such as the mean, variance, or model coefficients, though it should be applied carefully to avoid losing valid data. Winsorization (D) is also a standard outlier-handling technique that caps extreme values at a specified percentile (for example, the 5th and 95th percentiles) or a fixed threshold, reducing the influence of outliers while retaining the observations. Normalization to 0-1 range (A) is a scaling technique that changes the range of values but does not remove or reduce outliers, so it is not an outlier-handling method. Ignoring data quality (B) is not a valid analytical practice and does nothing to address outliers. Increasing the number of dimensions (E) is a dimensionality-related operation, such as adding features, and does not itself handle outliers.

Answer

Normalization to 0-1 range

Answer

Ignoring the data quality

Answer

Increasing the number of dimensions

Question 6

A data analyst at a retail company runs a daily report that joins a 200-million-row sales fact table to a small product dimension table. The query plan shows a broadcast exchange, and the job fails with an out-of-memory error on the executor nodes. The analyst wants to reduce shuffle volume without changing the report's output. Which technique should the analyst apply?

Accepted Answer

Use a broadcast hash join hint so the small dimension table is replicated to each executor instead of shuffled.. The out-of-memory error occurs because the broadcast exchange is moving a large table across the cluster. A broadcast hash join replicates only the small dimension table to each executor, so the large fact table is not shuffled. This reduces shuffle volume and memory pressure while preserving the report's output, directly addressing the failure.

Answer

Enable partition pruning on the fact table's date column so only recent partitions are scanned.

Answer

Increase the executor memory allocation and rerun the same query without other changes.

Answer

Convert the fact table to a columnar file format such as Parquet to reduce I/O during the join.

Question 7

A data engineer is designing a query to calculate a running total of sales per day for each product category. The sales table has columns sale_date, category, and amount. They write a query using SUM(amount) OVER (PARTITION BY category ORDER BY sale_date) but notice that for days with multiple sales in the same category, the running total is not cumulative within the day. What is the most likely cause?

Accepted Answer

The ORDER BY clause within the window function does not include a unique tiebreaker, causing non-deterministic ordering for same-day sales.. The running total fails to accumulate within a day because the window's ORDER BY lacks a unique tiebreaker. When multiple rows have the same sale_date, the database's ordering among them is arbitrary, so the cumulative sum may not increase row by row. Adding a unique column to the ORDER BY ensures deterministic results.

Answer

The default window frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows with the same sale_date, so the running total jumps to the total for the day.

Answer

The PARTITION BY clause should be removed to allow the running total to span across all categories.

Answer

The SUM function cannot be used with the OVER clause; a self-join is required for running totals.

Question 8

A data analyst has a table named Sales with columns SaleDate (DATE), Region (VARCHAR), and Amount (DECIMAL). The analyst needs a result set that shows the total Amount for each Region, but only for regions whose total Amount exceeds 10000, sorted from highest total to lowest. Which SQL statement will produce this result?

Accepted Answer

SELECT Region, SUM(Amount) AS Total FROM Sales GROUP BY Region HAVING SUM(Amount) > 10000 ORDER BY Total DESC;. To aggregate sales by region and then filter on the aggregate, the HAVING clause must be used because it operates after GROUP BY, whereas WHERE filters rows before aggregation. Ordering descending by the sum requires ORDER BY on the aggregate or its alias. The correct statement combines GROUP BY Region, HAVING SUM(Amount) > 10000, and ORDER BY Total DESC in the proper clause sequence.

Answer

SELECT Region, SUM(Amount) AS Total FROM Sales WHERE SUM(Amount) > 10000 GROUP BY Region ORDER BY Total DESC;

Answer

SELECT Region, SUM(Amount) AS Total FROM Sales GROUP BY Region WHERE Amount > 10000 ORDER BY Total DESC;

Answer

SELECT Region, SUM(Amount) AS Total FROM Sales GROUP BY Region ORDER BY Total DESC HAVING SUM(Amount) > 10000;

Question 9

A data analyst is tuning a query that joins a large fact table to a dimension table and filters on a low-cardinality column in the fact table. The query currently performs a full table scan on the fact table. Which action is most likely to improve performance without changing the result set?

Accepted Answer

Create an index on the low-cardinality filter column and update statistics on the fact table. The full table scan on the fact table is best addressed by enabling an index-based access path on the filtered column and ensuring the optimizer has current statistics. Accurate statistics allow the optimizer to estimate selectivity and choose an index seek or scan when beneficial. Rewriting joins or filters in ways that expand intermediate results or add per-row subqueries does not target the root cause and usually degrades performance.

Answer

Replace the dimension table join with a scalar subquery in the SELECT list

Answer

Add a HAVING clause that repeats the filter condition after the join

Answer

Convert the join to a CROSS JOIN and filter in the WHERE clause

Question 10

A data analyst is asked to provide a list of all customers who have not placed any orders in the last 90 days. The database has a customers table and an orders table with a foreign key customer_id. Which SQL query correctly returns the desired customers?

Accepted Answer

SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE order_date >= DATEADD(day, -90, GETDATE())). The requirement is to find customers with no orders in the last 90 days. The NOT IN subquery correctly excludes any customer who has a recent order, while including those with no orders at all. The other options either invert the logic, include customers with old orders, or use a join that does not properly exclude recent orders.

Answer

SELECT * FROM customers WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id AND order_date >= DATEADD(day, -90, GETDATE()))

Answer

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date < DATEADD(day, -90, GETDATE()))

Answer

SELECT * FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.order_date < DATEADD(day, -90, GETDATE()) OR orders.order_date IS NULL

Question 11

A data engineer is designing a query to calculate a 7-day moving average of daily active users from a large fact table in a Snowflake environment. The table contains one row per user per day. The engineer wants to ensure the moving average is computed correctly across date boundaries and handles days with no activity. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Generate a date spine covering the full date range and left join the fact table to it before applying the window function.. To compute a correct 7-day moving average that spans calendar days, the engineer must define a frame of seven rows using ROWS BETWEEN 6 PRECEDING AND CURRENT ROW and ensure all dates are present by generating a date spine and left joining the fact table. Together, these actions handle missing days and maintain the correct window size. Using RANGE or filtering zero days would produce incorrect or incomplete averages.

Answer

Use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW to handle missing dates.

Answer

Use the QUALIFY clause to filter out days with zero active users before computing the average.

Answer

Partition the window by user ID to compute a separate moving average for each user.

Question 12

A data analyst is preparing a dataset for a machine learning model that predicts customer lifetime value. The dataset contains a numeric column 'annual_income' with missing values, extreme outliers, and a right-skewed distribution. Which TWO preprocessing steps are appropriate before feeding the column into the model? (Choose two.)

Accepted Answer

Impute missing values using the median and apply a log transformation to reduce skew.. The column has three issues: missing values, outliers, and right skew. Median imputation handles missing values robustly despite outliers, and a log transform reduces skew and tames extreme values. Standardization then scales the feature for models sensitive to magnitude. These steps are complementary and directly address the stated data problems without discarding data or losing information.

Answer

Remove every row containing a missing value to avoid imputation bias.

Answer

Encode the numeric column as categorical bins using equal-width binning.

Answer

Cap outliers at the 1st and 99th percentiles and impute missing values with the mean.

Question 13

A reporting team must calculate a running total of daily sales for each product category. The source table contains one row per category per day, and the report must display, for each row, the cumulative sum of sales from the earliest date up to and including the current date within that category. Which SQL technique should be used?

Accepted Answer

A window function using SUM(sales) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).. A running total per category is a classic window function use case. SUM(sales) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) restarts the accumulation for each category and includes all rows up to the current date. It preserves one row per input row and scales far better than self-joins or correlated subqueries, which either explode intermediate results or run per-row.

Answer

A GROUP BY on category with SUM(sales) and no ordering, relying on the database to return rows in date order.

Answer

A correlated subquery in the SELECT list that sums sales for the same category where sale_date is less than the outer row's date.

Answer

A self-join that matches each row to all rows with an earlier or equal date in the same category and sums their sales.

Question 14

A data analyst is preparing a dataset for a machine learning model to predict customer churn. They need to handle missing values in several columns. Which TWO of the following techniques are appropriate for imputing missing numerical values while preserving the distribution of the data? (Choose two.)

Accepted Answer

Multiple imputation by chained equations (MICE). Median imputation and MICE are both appropriate for numerical missing values when preserving distribution is a goal. Median imputation is robust to outliers and skew, while MICE uses relationships among variables to generate plausible values. Mean imputation and mode imputation distort distribution, and listwise deletion discards data rather than imputing.

Answer

Mean imputation

Answer

Mode imputation

Answer

Listwise deletion

Question 15

A data analyst writes a query that joins a 500-million-row fact table to a 2-million-row dimension table. The query plan shows a broadcast nested loop join, and the query runs for hours. The dimension table is filtered to a few hundred rows by a highly selective predicate. Which action should the analyst take to improve performance while preserving correctness?

Accepted Answer

Add an index on the fact table's foreign key column and rewrite the query to use a semi-join with EXISTS on the filtered dimension.. The plan broadcasts rows for a nested loop join, which is inefficient when the outer side is huge. Because the dimension filter is highly selective, the better strategy is to probe the fact table using an index on the foreign key with a semi-join, which returns matching fact rows without duplication. This preserves correctness and reduces the work from a product of row counts to indexed lookups.

Answer

Convert the fact table to a columnar format and rely on predicate pushdown alone.

Answer

Materialize the join result into a temporary table and query the temporary table instead.

Answer

Force a hash join by adding an optimizer hint and remove the dimension filter from the WHERE clause.

Question 16

A data analyst queries a sales table containing 40 million rows to calculate month-to-date revenue. The query filters on order_date and groups by region_code. The table has a clustered index on order_id and a nonclustered index on order_date, yet the query performs a full table scan and takes several minutes. The analyst needs to reduce execution time without changing the result set. Which of the following actions is the MOST appropriate first step?

Accepted Answer

Create a covering nonclustered index on (order_date, region_code) that includes the revenue column.. The scan persists because no index satisfies both the order_date range filter and the grouping/aggregation on region_code and revenue. A covering index with order_date and region_code as keys and revenue included allows an index seek plus direct reads of the aggregated column, removing the scan and key lookups. The other actions either degrade the existing index, add recompilation overhead, or improve estimates without creating a usable access path.

Answer

Update statistics on the sales table to give the optimizer more accurate cardinality estimates.

Answer

Reorder the columns in the existing nonclustered index on order_date so region_code becomes the leading key.

Answer

Add OPTION (RECOMPILE) to the query so the optimizer generates a fresh plan for each execution.

Question 17

A data analyst is working with a table named orders that has a column order_date of type DATE. The analyst needs to retrieve all orders placed in the year 2023. Which SQL WHERE clause correctly filters the data?

Accepted Answer

WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'. Filtering a DATE column for a specific year is best done with a range condition using BETWEEN, which is inclusive and sargable. Using functions like YEAR() or pattern matching with LIKE can cause performance issues and may not be portable across database systems. The BETWEEN clause correctly captures all dates in 2023.

Answer

WHERE order_date LIKE '2023%'

Answer

WHERE order_date = '2023'

Answer

WHERE YEAR(order_date) = 2023

Question 18

A data analyst is tasked with writing a SQL query to calculate the average salary per department from an employees table. The analyst wants to ensure the query is optimized for performance and returns only departments with more than 5 employees. Which TWO of the following techniques should the analyst use? (Choose two.)

Accepted Answer

Use a HAVING clause to filter departments with more than 5 employees after grouping.. To filter departments by employee count after grouping, the HAVING clause is required. Additionally, indexing the department column can improve the performance of the GROUP BY operation. These two techniques directly address the requirements of filtering aggregated results and optimizing query speed.

Answer

Use SELECT * to include all columns for easier verification.

Answer

Use a subquery in the SELECT clause to calculate the average salary.

Answer

Use a WHERE clause to filter out departments with fewer than 5 employees before grouping.

Question 19

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?

Accepted Answer

Better readability and recursive capability. CTEs (WITH clauses) improve query readability by naming intermediate result sets and, unlike most subqueries, support recursion via WITH RECURSIVE — essential for hierarchical data like org charts or bill-of-materials. In the pre-aggregation scenario, the CTE makes the aggregation step explicit and self-documenting, and it can be referenced multiple times in the outer query.

Answer

Automatic indexing of the result set

Answer

Performance is always superior to subqueries

Answer

Lower memory consumption during execution

Question 20

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?

Accepted Answer

LEFT JOIN. A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right side. This exactly matches the requirement to return all records from the left table even if there is no match in the right table. Therefore, LEFT JOIN is correct.

Answer

CROSS JOIN

Answer

INNER JOIN

Answer

FULL OUTER JOIN