Question 5 of 6 Advanced Scenario

When would a window function be preferable to GROUP BY?

Short answer

Use a window function when you need group-level calculations while retaining the original rows. GROUP BY collapses rows into aggregate groups.

Why

In longitudinal assay data, you may want every timepoint alongside each patient’s baseline, rank or running average. A window function such as ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY visit_date, visit_id) assigns an order without removing measurements. An aggregate grouped by patient_id can compute patient totals but cannot retain each individual timepoint without an additional join.

Explicit ORDER BY and a deterministic tie-breaker matter when timestamps repeat. Understand the window frame for cumulative sums and moving averages, since defaults may not match the desired clinical interval. If the goal is precisely one patient-level result, GROUP BY may be simpler.

Confirm that partition keys match scientific entities and verify no data leakage when ranking temporal observations.

Common mistakes

Using ROW_NUMBER without stable tie-breaking, or believing window functions automatically deduplicate rows

The interviewer might ask next

How would you select the most recent valid measurement per patient when timestamps tie?