Data and databases interview bank

GROUP BY changes the result grain: it combines each group into one output row unless another operation expands it later. A window aggregate keeps the input grain and attaches a value calculated over a partition to every row. That makes windows suitable for percentages of a group, running totals, and comparisons with nearby rows. You must still define partitioning, window order, and often the frame. The window order controls calculation but not final display order, so the outer query needs its own ORDER BY when presentation must be deterministic.

ROWS moves a frame boundary by positions in the ordered row sequence. RANGE derives the boundary from ordering values and normally treats equal ordering values as peers. With two rows sharing the current timestamp or amount, a default RANGE frame can include both in each row’s total, while an equivalent-looking ROWS frame grows one physical row at a time. Exact RANGE syntax and interval support vary by database. I write the frame explicitly, add duplicate values at its boundary, and verify the behavior on the production dialect instead of relying on a default.

Start by stating the anchor set and the one-step relationship separately. Then prove what moves each recursive round toward termination. Tree-shaped data may still be dirty, so carry a visited identifier set or delimited path and define what happens when a node repeats. Keep a depth, row, or statement-time limit as resource protection, not as the proof of correctness. In multitenant systems, tenant and authorization predicates belong in both the anchor and recursive member. Finally, test cycles, multiple parents, orphan nodes, an empty anchor, and the maximum legitimate depth.

NOT EXISTS is safer for exclusion when the subquery column can contain NULL. NOT IN compares the outer value with every value on the right under SQL three-valued logic. If no value matches but one is NULL, the condition can be UNKNOWN, and WHERE removes the row. A correlated NOT EXISTS asks only whether an equality match exists, so an unrelated null does not poison the result. I still define how a null outer key should behave, test empty and duplicate right-hand sets, and inspect the target plan rather than assuming one spelling is always faster.