DS0-001 Data Querying And Analysis — Timed 10 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