Aggregate without losing row detail

GROUP BY reduces each group to fewer rows. A window function calculates across related rows while retaining each input row, which makes it useful for rankings and running values.

SELECT customer_id, order_id, total_amount,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY total_amount DESC, order_id
       ) AS position
FROM orders;

Choose the ranking function deliberately

ROW_NUMBER assigns a unique sequence. RANK leaves gaps after ties; DENSE_RANK does not. For exactly three orders per customer, filter ROW_NUMBER to three. For all orders tied in the top three ranks, use a rank function and state the tie policy.

Running totals need a frame

A running sum uses an ordered window such as SUM(amount) OVER (PARTITION BY account_id ORDER BY posted_at, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Include a stable tie-breaker. Be explicit about the frame when peer rows could otherwise change the result.

Compare a row with its neighbor

LAG and LEAD expose values from a previous or next row in the ordered partition. They are useful for month-over-month changes, consecutive events, and gaps without a self-join.

Interview takeaway

Describe the partition, ordering, tie behavior, and frame. Those choices define the result more than the function name alone.