Self-Assessment Quiz: SQL Transformations

Twenty questions. Aim for 16 or more. This is the most transferable chapter in the book; treat 18 as the bar.

Question 1

Which is NOT a tell that you are thinking procedurally?

• A. A loop over query results issuing more queries
• B. A correlated subquery in the SELECT list
• C. A self-join comparing a row to its neighbor
• D. A CTE with several named steps

Question 2

A window function differs from GROUP BY in that it:

• A. Is faster
• B. Computes across related rows without collapsing them
• C. Cannot be filtered
• D. Requires an index

Question 3

For deduplication you must use:

• A. RANK()
• B. DENSE_RANK()
• C. ROW_NUMBER()
• D. NTILE(1)

Question 4

Why?

• A. It is faster
• B. RANK gives tied rows the same number, so WHERE rank = 1 keeps all of them
• C. RANK cannot be used with PARTITION BY
• D. ROW_NUMBER handles nulls better

Question 5

With an ORDER BY and no explicit frame, the default frame is:

• A. The whole partition
• B. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a running aggregate
• C. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
• D. RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Question 6

RANGE BETWEEN 2 PRECEDING AND CURRENT ROW differs from ROWS in that it:

• A. Is faster
• B. Includes all rows whose ORDER BY value is within range, including peers
• C. Only works with numeric columns
• D. Ignores the partition

Question 7

LAST_VALUE under the default frame returns:

• A. The last row of the partition
• B. The last row of the current peer group — the current row itself when the ordering is unique
• C. NULL
• D. The first row

Question 8

"CTEs are optimization barriers" is:

• A. True in all engines
• B. True in PostgreSQL before version 12, and repeated as a general fact
• C. Never true
• D. True only for recursive CTEs

Question 9

A recursive CTE must use UNION ALL rather than UNION because:

• A. UNION is not supported
• B. UNION deduplicates every iteration — slow, and it masks a cycle
• C. UNION ALL is required by the standard
• D. UNION cannot reference the CTE

Question 10

A depth guard in a recursive CTE:

• A. Improves performance
• B. Converts an infinite loop into a wrong answer you can detect
• C. Is required syntax
• D. Prevents duplicates

Question 11

Prefer a flattened dimension over recursion when:

• A. The hierarchy is large
• B. The depth is bounded and known
• C. The engine lacks WITH RECURSIVE
• D. Never

Question 12

The constraint on pivoting in SQL is:

• A. It requires a specific engine
• B. You must know the columns in advance, so a dynamic pivot generates SQL
• C. It cannot aggregate
• D. It loses null rows

Question 13

Which deduplication pattern silently composes rows that never existed?

• A. ROW_NUMBER with a tiebreaker
• B. DISTINCT ON
• C. GROUP BY with MAX() per column
• D. DISTINCT *

Question 14

Why is the tiebreaker in ROW_NUMBER() OVER (... ORDER BY ts DESC) not optional?

• A. It improves performance
• B. Without it, deduplication is non-deterministic and re-runs produce different results
• C. The engine requires two ORDER BY columns
• D. It prevents nulls

Question 15

After a deduplication you should assert:

• A. That rows equal distinct keys
• B. That no keys were dropped
• C. Both — a dedup fails in two directions
• D. Neither; the query is deterministic

Question 16

The general gaps-and-islands technique is:

• A. Sort and compare adjacent rows
• B. Create a group identifier constant within an island, then group by it
• C. Use a recursive CTE
• D. Self-join on offset

Question 17

The general form of that technique is:

• A. date - row_number
• B. Flag the breaks, then take a running sum of the flag
• C. DENSE_RANK over the ordering
• D. NTILE on the gap size

Question 18

A backward-looking window function at a batch boundary produces:

• A. An error
• B. Missing rows
• C. Artifacts — an excess of short sessions at each window start, which looks like real behavior
• D. Duplicates

Question 19

The fix is:

• A. A larger batch
• B. Overlap the input and emit only records whose start falls in the target window
• C. Sort differently
• D. Disable the window function

Question 20

Before checking join strategy and estimates in a transformation's plan, look for:

• A. Index usage
• B. Sorts you did not ask for — window functions with different partitionings force one each
• C. Parallelism
• D. Buffer hits

Answer Key

1. D — §18.1. A CTE with named steps is a readability practice, not a procedural tell.

2. B — §18.2. That clause is the whole difference.

3. C — §18.2.

4. B — §18.2. Which is exactly the bug that produces duplicates in a deduplication step.

5. B — §18.3. So adding an ORDER BY silently turns a total into a running total.

6. B — §18.3. Two rows on the same date show the same running total.

7. B — §18.3, ⚠️ callout. Under RANGE, CURRENT ROW means the last peer of the current row — the same peer rule as Question 6. With a unique ordering that is the current row, which is the form the surprise is usually reported in. A is the answer people expect; the default frame ends at the current row, not at the partition's end.

8. B — §18.4. Modern engines inline and decide.

9. B — §18.5. And masking a cycle is worse than surfacing it.

10. B — §18.5. Strictly better than running forever.

11. B — §18.5. Simpler, faster, and easier for analysts. Recursion earns its complexity when depth is genuinely variable.

12. B — §18.6. And a new value silently adds a column, which is a schema change every run.

13. C — §18.7. MAX(quantity) is the maximum across versions, not the quantity from the latest one. If the columns are not independent — and they are not — you get a row that never existed.

14. B — §18.7. Which breaks reproducibility in a way that is very hard to diagnose.

15. C — §18.7, 🔎 callout.

16. B — §18.8.

17. B — §18.8. date - row_number is a special case of it.

18. C — §18.9, ⚠️ callout. Which is why it survives.

19. B — §18.9. Chapter 13 §13.4's overlap window applied to a transformation, needing the same idempotent write.

20. B — §18.10. Consolidating them is frequently a large free win and is invisible unless you read the plan.

Topic map