SQL for data science quiz

Question 1

What is the key behavioral difference between WHERE and HAVING clauses in SQL aggregation queries?

Accepted Answer

WHERE filters rows before grouping; HAVING filters groups after aggregation

Answer

HAVING runs before WHERE in all engines

Answer

WHERE can use aggregates but HAVING cannot

Answer

They are interchangeable with identical performance

Question 2

In standard SQL logical processing order, where does SELECT fall relative to GROUP BY and ORDER BY?

Accepted Answer

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

Answer

SELECT -> FROM -> WHERE -> GROUP BY -> ORDER BY

Answer

FROM -> GROUP BY -> WHERE -> SELECT -> ORDER BY

Answer

FROM -> SELECT -> WHERE -> ORDER BY -> GROUP BY

Question 3

What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() window functions?

Accepted Answer

ROW_NUMBER is unique; RANK leaves gaps on ties; DENSE_RANK has no gaps

Answer

All three behave identically with ties

Answer

DENSE_RANK leaves gaps; RANK has no gaps

Answer

ROW_NUMBER repeats values on ties

Question 4

Why might COUNT(column) return a different value than COUNT(*) on the same table?

Accepted Answer

COUNT(column) ignores NULLs while COUNT(*) counts every row

Answer

COUNT(*) ignores NULLs while COUNT(column) counts them

Answer

They always return the same value

Answer

COUNT(*) counts only distinct rows

Question 5

A data scientist needs the top 1 most recent record per user from an events table. What is the canonical SQL pattern?

Accepted Answer

ROW_NUMBER() OVER (PARTITION BY user ORDER BY ts DESC) then WHERE rn=1

Answer

SELECT MAX(*) GROUP BY user

Answer

DISTINCT user with ORDER BY ts

Answer

LIMIT 1 without partitioning

Question 6

What does the SQL window frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW compute when paired with SUM()?

Accepted Answer

A running cumulative sum up to the current row

Answer

The sum of the entire partition regardless of order

Answer

Only the current row's value

Answer

The average of the previous row and current row

Question 7

What is the difference between RANGE and ROWS as window frame units?

Accepted Answer

ROWS uses physical row offsets; RANGE uses logical value peers

Answer

ROWS and RANGE are synonyms

Answer

RANGE counts physical rows; ROWS uses value ranges

Answer

Neither can be combined with ORDER BY

Question 8

How do LEFT JOIN, INNER JOIN, and FULL OUTER JOIN differ in which rows they retain?

Accepted Answer

INNER keeps matches only; LEFT keeps all left rows; FULL keeps all rows from both

Answer

LEFT keeps only matches; INNER keeps all left rows

Answer

FULL keeps only matched rows

Answer

All three return identical row sets

Question 9

Why can a LEFT JOIN unexpectedly behave like an INNER JOIN when filtering the right table in the WHERE clause?

Accepted Answer

A WHERE predicate on the right table removes NULL rows, dropping unmatched left rows

Answer

WHERE always preserves outer-join NULLs

Answer

The behavior only changes for RIGHT JOIN

Answer

Moving the predicate to ON breaks the query

Question 10

What is a correlated subquery and what is its main performance concern?

Accepted Answer

It references the outer query and may run per outer row, risking poor performance

Answer

It runs exactly once and is always fastest

Answer

It cannot reference outer columns

Answer

It only works inside the FROM clause

Question 11

When should you use EXISTS instead of IN with a subquery in SQL?

Accepted Answer

EXISTS short-circuits and NOT EXISTS is NULL-safe unlike NOT IN

Answer

IN is always faster and NULL-safe

Answer

EXISTS cannot be correlated

Answer

NOT IN is safe with NULLs but NOT EXISTS is not

Question 12

What is the gaps-and-islands problem and a common SQL technique to solve it?

Accepted Answer

Subtract ROW_NUMBER() from the ordered value so each run shares a constant group key

Answer

Use DISTINCT to find consecutive runs

Answer

Only a recursive CTE can solve it

Answer

Use COUNT(*) without grouping

Question 13

How do you compute the median of a column in standard SQL?

Accepted Answer

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col)

Answer

AVG(col) always equals the median

Answer

MEDIAN() is ANSI standard in all engines

Answer

ROUND(MAX(col)/2)

Question 14

What is the difference between PERCENTILE_CONT and PERCENTILE_DISC?

Accepted Answer

CONT interpolates and may return a non-existent value; DISC returns an actual value

Answer

DISC interpolates; CONT returns an actual value

Answer

Both always return existing data values

Answer

Both always interpolate identically

Question 15

How does LAG() help compute period-over-period change in time-series SQL?

Accepted Answer

LAG returns the previous row's value within the partition for delta computation

Answer

LAG returns the next row's value

Answer

LAG aggregates the whole partition

Answer

LAG only works without ORDER BY

Question 16

What is the purpose of a recursive CTE, and what are its required parts?

Accepted Answer

An anchor member, UNION ALL, and a recursive member referencing the CTE

Answer

A single SELECT with GROUP BY

Answer

Two anchors and no recursive member

Answer

Only an ORDER BY and LIMIT

Question 17

In SQL, what does NULL = NULL evaluate to and why does it matter for joins and filters?

Accepted Answer

It evaluates to NULL (unknown), so equality never matches NULLs

Answer

It evaluates to TRUE

Answer

It evaluates to FALSE always

Answer

It raises a runtime error

Question 18

What is the difference between UNION and UNION ALL, and which should a data scientist prefer for large pipelines?

Accepted Answer

UNION dedupes (slower); UNION ALL keeps all rows (faster)

Answer

UNION ALL dedupes; UNION keeps all rows

Answer

Both deduplicate identically

Answer

UNION ALL sorts but UNION does not

Question 19

How would you pivot long-format rows into wide columns using conditional aggregation?

Accepted Answer

Wrap CASE expressions inside aggregates grouped by the entity key

Answer

Use ORDER BY with DISTINCT

Answer

Use multiple LEFT JOINs on the same table by row number

Answer

Pivoting is impossible without PIVOT keyword

Question 20

What does a clustered index determine versus a non-clustered index in a relational database?

Accepted Answer

Clustered sets physical row order (one per table); non-clustered is a separate pointer structure

Answer

Both reorder the physical table

Answer

You can have many clustered indexes per table

Answer

Non-clustered indexes store the actual rows

Question 21

What is a covering index and why does it speed up analytical queries?

Accepted Answer

It contains all columns the query needs, avoiding base-table lookups

Answer

It indexes every column in the database

Answer

It covers only the primary key

Answer

It forces a full table scan

Question 22

When analyzing a slow query, what does a SEQ SCAN / full table scan in the execution plan typically indicate?

Accepted Answer

The engine reads all rows; consider indexing or sargable predicates for selective queries

Answer

The query is always optimal

Answer

It means an index is being used

Answer

Scans only occur on empty tables

Question 23

What makes a WHERE predicate non-sargable, and why does it hurt index usage?

Accepted Answer

Wrapping the indexed column in a function blocks index seeks

Answer

Any predicate with an index is non-sargable

Answer

Range comparisons are always non-sargable

Answer

Sargability has no effect on indexes

Question 24

How do you safely de-duplicate rows keeping one record per key in SQL?

Accepted Answer

ROW_NUMBER partitioned by key with deterministic ORDER BY, keep rn=1

Answer

SELECT DISTINCT * always picks the right row

Answer

GROUP BY the key returns all columns correctly

Answer

TRUNCATE the table

Question 25

What is the difference between a temporary table, a CTE, and a materialized view for intermediate results?

Accepted Answer

CTE is query-scoped; temp table persists per session; materialized view persists precomputed results

Answer

All three persist permanently

Answer

CTEs are always materialized to disk

Answer

Materialized views auto-refresh instantly on every source change

Question 26

Why can chaining multiple CTEs sometimes perform worse than a temp table in some engines?

Accepted Answer

CTEs may be recomputed/materialized without indexes, while temp tables persist and can be indexed

Answer

CTEs are always faster than temp tables

Answer

Temp tables cannot be indexed

Answer

CTEs always cache results across queries

Question 27

How do you perform stratified random sampling per group in SQL?

Accepted Answer

ROW_NUMBER partitioned by stratum ordered by RANDOM(), keep first k per group

Answer

TABLESAMPLE guarantees equal rows per group

Answer

ORDER BY RANDOM() with one global LIMIT

Answer

DISTINCT on the stratum column

Question 28

What is the standard SQL approach to building a retention/cohort analysis?

Accepted Answer

Assign cohort by first-activity period, compute period offset, count distinct users per cohort/offset

Answer

Count total rows without grouping by first activity

Answer

Use only COUNT(*) over all users

Answer

Retention requires no notion of cohort

Question 29

How do you sessionize event data so events within a 30-minute inactivity gap belong to the same session?

Accepted Answer

LAG to detect gaps > threshold, then cumulative SUM of the new-session flag as session id

Answer

GROUP BY user only

Answer

COUNT events per hour bucket

Answer

DISTINCT on event type

Question 30

What is the difference between GROUP BY ROLLUP and GROUP BY CUBE?

Accepted Answer

ROLLUP gives hierarchical subtotals; CUBE gives all combinations of subtotals

Answer

CUBE gives only a grand total

Answer

ROLLUP computes 2^n combinations

Answer

They are identical

Test yourself