Query Processing

Check operators for cost and correctness

Reviewing a query plan: find where the row count peaks, which operators block, what each holds in memory and which orderings it needs; then a separate pass for seven silent wrong answers.

Choose a join algorithm

Two inputs must be joined: a non-equality join, an index lookup, a merge, a local join on existing partitions, a broadcast or a shuffle, and then which hash variant fits memory? Then check key frequency and NULLs, which override it all.

Choose an aggregation strategy

A query groups and aggregates: does the function allow partial results, is an exact answer needed, and does pre-aggregating before the shuffle pay? Three questions, then sizing, sort reuse and determinism checks.

Plan a sort

A query needs data in order: check that order (not just grouping) is needed, take it for free if the data already has it, then size the sort: passes, fan-in, key normalization, and sampling for a distributed sort.

Choose a dedup strategy

Duplicates must be removed or counted: a sketch for counting, streaming dedup over sorted input, hash dedup in memory, or sort-based dedup when it will not fit? Plus wide keys and why approximate dedup can lose rows.

Same answer, different cost

The same report two ways on 20 million orders in DuckDB, then with the filter forced above the join: time and rows out of the join.

Blocking versus streaming

Time to first row and total time for LIMIT 10, a full ORDER BY and ORDER BY … LIMIT 10 over 20 million rows in DuckDB.

The NULL traps

NOT IN against NOT EXISTS before and after one NULL; a LEFT JOIN filtered in WHERE against in ON; a SUM after a join that fans out.

Join algorithms and the memory threshold

A 20-million-row probe joined to a 5-million-row build side with a payload in DuckDB, under memory limits from 4 GB to 100 MB, and at 1 and 8 threads.

As-of join versus range join

The price at order time for 1,000 products × 1,000 price changes: a range join with ROW_NUMBER against DuckDB's ASOF JOIN.

When does partial aggregation pay?

Local pre-aggregation of 100,000 rows per partition for G from 100 to 4 million groups: measured reduction against N/(P·G), and its cost.

Sketches versus sampling for COUNT DISTINCT

Distinct users in 20 million events: exact, HyperLogLog, and "count distinct in a 1% sample × 100"; then the same sample for a SUM.

Float summation is not associative

One million doubles of very different magnitudes summed in five random orders with a plain loop, against math.fsum.

The sort that is not an I/O problem: count the passes

DuckDB sorts 20 million rows (about 2 GB) under a 500 MB memory limit: temp files, wall and CPU time, random and pre-sorted input, and the one-pass capacity M²/B.

Normalized keys and sorting a pointer array

Sort 300,000 three-column rows with a typed comparison and with normalized byte keys; then sort prefix + index pairs.

Digest collisions and Bloom-filter data loss

The birthday formula with 32-bit digests; then a Bloom filter used as a dedup over a million distinct keys, and as a negative oracle in front of an exact set.

Interview questions

Short questions on how queries are executed, such as plans and enforcers, SQL semantics, join algorithms, aggregation, sorting and deduplication, each with an answer you could give aloud and a link to the full explanation.

Scenarios

Longer problems on query execution, of the kind asked in senior interviews: each one estimated with numbers, diagnosed, and answered with its trade-offs.