Data Querying And Analysis practice questions

NVL

Why it fails: NVL is Oracle-specific.

IFNULL

Why it fails: IFNULL is MySQL-specific.

ISNULL

Why it fails: ISNULL is specific to certain SQL dialects like T-SQL but not standard SQL.

replace()

Why it fails: replace() substitutes specified values with given ones, so it needs the mean computed separately and passed in; it does not calculate the mean itself. It is tempting because it targets nulls directly, and would be right for mapping known values such as -1 to NaN.

dropna()

Why it fails: dropna() removes rows containing missing values, reducing record count rather than substituting the column mean. It is tempting as a quick way to eliminate nulls, and would be correct when incomplete rows should be excluded from analysis entirely, not imputed.

interpolate()

Why it fails: interpolate() fills gaps by estimating values along an index or time axis, typically linear or polynomial interpolation between known points, so it cannot compute a column mean. It is tempting because it handles missing numeric data, and would be right for time-series gaps where neighbouring values imply the trend.

map()

Why it fails: map() applies a per-element transformation using a dictionary, Series or function, and cannot derive a column aggregate like the mean. It is tempting because it transforms column values, and would be correct for recoding categorical labels into numeric codes.

Data normalization

Why it fails: Normalization splits data into separate tables.

Data aggregation

Why it fails: Aggregation performs calculations like SUM or AVG.

Data pivoting

Why it fails: Pivoting rotates rows to columns.

Time taken to complete a task

Why it fails: Task completion time is continuous quantitative data measured in units, not a label set. It tempts because durations can be grouped into bands, but the underlying variable stays numeric. Categorical variables assign observations to named groups such as status or region.

Temperature in Celsius

Why it fails: Celsius temperature is continuous quantitative data, so it has no fixed category labels. It tempts because numeric scales feel classifiable, yet only binned ranges become ordinal. Categorical variables hold discrete labels such as colour or department, where arithmetic is meaningless.

Price in dollars

Why it fails: Price in dollars is continuous quantitative data supporting arithmetic, so it cannot be categorical. It tempts because currency values can be bucketed into ranges, but those bins are derived, not inherent. Categorical variables hold discrete labels like product type or payment method.

Normalization to 0-1 range

Why it fails: Normalization preserves outliers rather than removing them.

Ignoring the data quality

Why it fails: Ignoring outliers is not a handling method.

Increasing the number of dimensions

Why it fails: This does not address outliers.

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

Why it fails: Partition pruning reduces the number of rows read from the fact table, but it does not change how rows are redistributed across the cluster during the join. The out-of-memory error is caused by the broadcast exchange moving the dimension table to every executor, so limiting the scan range does not resolve the shuffle-memory pressure for the rows that remain.

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

Why it fails: Adding memory may postpone the failure but does not reduce the shuffle volume that caused the out-of-memory error. The broadcast exchange still moves the dimension table to every executor, so the underlying data movement and memory pressure remain. The analyst needs a join strategy change, not just more resources.

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

Why it fails: A columnar format like Parquet improves scan efficiency and compression, but it does not eliminate the shuffle that occurs when the fact table is redistributed for the join. The out-of-memory error stems from the broadcast exchange, so changing the storage format alone will not resolve the memory pressure on the executors.

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.

Why it fails: The default frame for ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows with the same ORDER BY value (peers). This would make the running total for a given day equal to the cumulative sum up to the end of that day, not a row-by-row running total. However, the symptom described is that the running total is not cumulative within the day, which suggests the frame is not the issue but rather the ordering. If the frame were the issue, the running total would be the same for all rows of the same day, which is a different symptom.

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

Why it fails: Removing PARTITION BY would compute a running total across all categories combined, which is not the desired per-category running total. The requirement is to calculate running total per product category, so partitioning by category is correct. The issue is not the partition but the ordering within each partition.

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

Why it fails: SUM with OVER is a standard SQL feature for windowed aggregations, including running totals. Self-joins are an older technique but not required. The query syntax is valid; the problem lies in the ordering ambiguity, not the choice of window function versus self-join.

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

Why it fails: Using WHERE with an aggregate function like SUM(Amount) is invalid in standard SQL because WHERE is evaluated before grouping and cannot contain aggregate functions. The query would fail at parse time. While the GROUP BY and ORDER BY clauses are appropriate, the filtering must occur with HAVING, not WHERE, for this aggregated condition.

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

Why it fails: This query filters individual rows where Amount exceeds 10000 before grouping, rather than filtering the aggregated total per region. It would exclude smaller sales that still contribute to a region's overall total, producing incorrect results. It also places WHERE after GROUP BY, which is invalid syntax in standard SQL.

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

Why it fails: The logical order of SQL clauses places HAVING before ORDER BY. Writing HAVING after ORDER BY is syntactically invalid in standard SQL and most database engines, so the statement will not execute. Although the intent to filter aggregated totals and sort them is right, the clause placement makes this option unusable.

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

Why it fails: A scalar subquery per row can execute repeatedly and often performs worse than a set-based join, especially on large fact tables. It may also change semantics if the dimension has multiple matches, causing errors or unexpected results. This approach does not address the full table scan and typically increases execution time rather than reducing it.

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

Why it fails: Repeating the filter in a HAVING clause does not change access methods and may force additional processing after aggregation or joining. HAVING filters grouped results, not base rows, so it cannot replace a WHERE filter on the fact table. It adds overhead and does not address the full table scan, making it an ineffective tuning step here.

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

Why it fails: A CROSS JOIN produces the Cartesian product of both tables, which dramatically increases intermediate row counts before filtering. This is almost always slower and more resource-intensive than a proper join. It also risks incorrect results if the filter does not fully emulate the join condition, so it is not a valid performance improvement for this scenario.

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

Why it fails: This query uses NOT EXISTS to return customers with no recent orders, which is logically correct. However, the question asks for the query that correctly returns the desired customers, and both NOT IN and NOT EXISTS can achieve this. But NOT IN can have issues with NULLs, while NOT EXISTS is generally safer. In this scenario, if the orders table has NULL customer_id, NOT IN would return no rows, but the question does not specify that. The NOT EXISTS is also correct, but the question likely expects the NOT IN version as the straightforward answer. However, to avoid ambiguity, the NOT IN version is correct assuming no NULLs.

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

Why it fails: This query returns customers who have placed orders older than 90 days, which is the opposite of the requirement. It includes customers who may have also placed recent orders, and it excludes customers who have never ordered. The logic is inverted and does not filter out recent orders.

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

Why it fails: This LEFT JOIN with a WHERE clause on orders effectively filters out customers with recent orders, but it also includes customers who have orders older than 90 days. It does not exclude customers who have both old and recent orders, because the WHERE condition would include them if any old order exists. The requirement is to exclude any customer with a recent order.

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

Why it fails: RANGE with an interval frame is valid in some databases, but in Snowflake the RANGE frame with INTERVAL is not supported for window functions in the same way; moreover, if there are missing dates, RANGE would include rows within the date range but still not generate rows for missing days. The requirement to handle days with no activity is better met by generating a date spine and left joining, not by changing the frame type alone.

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

Why it fails: Filtering out zero-activity days would remove them from the window frame, causing the 7-day average to be computed over fewer than seven days and skewing the result upward. The goal is to include zero-activity days in the average, not exclude them. QUALIFY is useful for filtering window results, but here it would defeat the purpose.

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

Why it fails: Partitioning by user ID would compute a moving average per user, not the overall daily active user count. The scenario asks for a 7-day moving average of daily active users, which is an aggregate across all users per day. Partitioning by user would change the granularity and produce incorrect results for the intended metric.

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

Why it fails: Listwise deletion discards potentially large portions of the dataset and can introduce selection bias if missingness correlates with other variables. It also wastes information from otherwise valid rows. Imputation preserves sample size and is generally preferred when missingness is moderate, as described here.

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

Why it fails: Equal-width binning of a right-skewed variable produces bins with very uneven populations and loses ordinal information. It discards the continuous nature of income and can harm model performance for a target like lifetime value. Binning is not a standard remedy for missing values or outliers in this context.

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

Why it fails: Winsorizing is reasonable for outliers, but mean imputation is not robust when the distribution is right-skewed, because the mean is inflated by the tail. This combination leaves the skew problem partially unaddressed and can distort the imputed values. A robust central tendency measure such as the median is more appropriate.

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

Why it fails: GROUP BY with SUM aggregates all dates into a single total per category, which loses the day-by-day progression entirely. It also does not guarantee any row order, and SQL result sets are unordered unless ORDER BY is specified. This approach cannot produce a running total because it collapses the time dimension instead of accumulating across it.

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.

Why it fails: This correlated subquery sums only earlier dates and would exclude the current row's sales, so it produces a cumulative total up to but not including the current day. It also executes once per outer row, which can be slow on large tables. To include the current row, the predicate would need to be less than or equal, and a window function remains the better choice.

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

Why it fails: A self-join can produce a running total, but it requires matching every row to all preceding rows, which causes quadratic growth in intermediate results as data volume increases. It is also more complex to write and maintain than a window function. While it may work on small datasets, it is not the preferred or scalable technique for this cumulative calculation.

Mean imputation

Why it fails: Mean imputation replaces missing values with the average of the observed values. While simple, it reduces variance and can distort the distribution, especially if the data is skewed or has outliers. It does not preserve the original distribution and can introduce bias, making it less appropriate for preserving distribution.

Mode imputation

Why it fails: Mode imputation replaces missing values with the most frequent value. It is typically used for categorical data, not numerical. For numerical data, the mode is often not meaningful, and using it can create artificial spikes at a single value, severely distorting the distribution.

Listwise deletion

Why it fails: Listwise deletion removes any row with missing values. This reduces sample size and can introduce bias if missingness is not completely at random. It does not impute values and thus does not preserve the distribution; it simply discards data, which is generally not appropriate when imputation is desired.

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

Why it fails: Columnar storage and predicate pushdown help scan efficiency, but the bottleneck described is the join strategy, not the scan. Broadcast nested loop join cost scales with the product of fact rows and broadcasted rows, which columnar storage does not change. The join method and access path must be addressed directly.

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

Why it fails: Materializing the full join still requires executing the expensive join once, so the initial cost remains. It also consumes significant storage and may be stale for subsequent queries. Materialization helps repeated access patterns, but it does not address the root cause of the inefficient join strategy.

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

Why it fails: Removing the selective filter eliminates the main advantage of the dimension table and forces the engine to hash all 2 million dimension rows. A hash join might still be better than broadcast nested loop, but discarding the filter changes the result set and increases work. The filter is essential to correctness and performance here.

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

Why it fails: Fresh statistics help the optimizer choose between available plans, but they cannot create a missing access path. If no index supports the order_date filter with region_code and revenue, the best available plan remains a scan. Statistics maintenance is valuable routinely, yet here it does not address the structural gap in indexing that causes the scan.

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

Why it fails: Making region_code the leading key destroys the index's usefulness for the order_date range predicate, because the optimizer can no longer seek directly to the month-to-date window. The query would likely scan more index pages or revert to the table, worsening performance. The filter is on order_date, so order_date must remain the leading key column for an efficient range seek.

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

Why it fails: Recompilation can help with parameter sniffing or skewed statistics, but it does not change the fundamental access path when no suitable index exists. Without an index covering order_date, region_code, and revenue, the optimizer still has to scan. Recompiling adds CPU overhead per execution and leaves the underlying scan untouched, so it will not deliver the needed reduction in runtime.

WHERE order_date LIKE '2023%'

Why it fails: The LIKE operator is for pattern matching on string data, not for date comparisons. Applying LIKE to a DATE column will cause an implicit conversion to string, which may work in some databases but is non-standard and can lead to unexpected results or errors. It also prevents index usage, making it inefficient.

WHERE order_date = '2023'

Why it fails: Comparing a DATE column to the string '2023' is invalid because the string does not represent a full date. The database will likely raise a conversion error or return no results. To filter by year, you must use date functions or a range condition. This option does not correctly filter for the year 2023.

WHERE YEAR(order_date) = 2023

Why it fails: While this may work in some databases, using a function on the column in the WHERE clause can prevent the use of an index, leading to poor performance. Additionally, not all SQL dialects support the YEAR() function. The BETWEEN approach is more universally supported and sargable, making it preferable for this scenario.

Use SELECT * to include all columns for easier verification.

Why it fails: Using SELECT * is not optimal when aggregating. It would include all columns, many of which are not part of the aggregation or grouping, leading to errors in standard SQL because non-aggregated columns must appear in the GROUP BY clause. It also increases I/O and network traffic. For this query, only department and average salary are needed.

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

Why it fails: Using a subquery in the SELECT clause to compute average salary per department would be inefficient and complex. It would likely require a correlated subquery that executes for each row, leading to poor performance. The straightforward GROUP BY with AVG() is simpler and more efficient. Subqueries are not needed for this aggregation.

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

Why it fails: A WHERE clause cannot be used to filter on aggregate results like COUNT(*). It filters individual rows before grouping. To filter departments based on the number of employees, you must use HAVING after GROUP BY. Using WHERE here would cause an error or incorrect results, so this technique is not appropriate.

Automatic indexing of the result set

Why it fails: A CTE is an inline named result set evaluated at query time; the database creates no indexes on it automatically. It is tempting because materialisation can occur, but that stores rows without indexing them. The actual benefit is improved readability and reuse of the pre-aggregated result within the query.

Performance is always superior to subqueries

Why it fails: A CTE is inlined and evaluated like a derived table, so the optimiser may produce an identical plan; no guarantee of superiority exists. The claim is tempting because CTEs improve readability and can be reused, which would justify them when the same aggregate feeds several joins.

Lower memory consumption during execution

Why it fails: A CTE is typically materialised or inlined by the optimiser, consuming comparable or greater memory than an equivalent subquery, so reduced memory is not guaranteed. They are tempting because materialisation can cache a reused result set, which helps when the same aggregate is referenced repeatedly rather than once.

CROSS JOIN

Why it fails: CROSS JOIN produces the Cartesian product of both tables, pairing every left row with every right row, so it neither preserves unmatched left rows meaningfully nor links on a key. It is tempting as a join variant, but it is correct for generating all combinations, not matching records.

INNER JOIN

Why it fails: INNER JOIN returns only rows where keys match in both tables, discarding left-table records that lack a right-side match, which contradicts the requirement. It is tempting because it is the most common join, and it is correct when unmatched rows should be excluded.

FULL OUTER JOIN

Why it fails: FULL OUTER JOIN returns unmatched rows from both tables, so it also emits right-table records with no left match, exceeding the requirement to preserve only the left table. It is tempting because it includes unmatched left rows, but it is correct when both sides must be retained.