DEPLOYED — AI Engineer & Forward Deployed Engineer Interview Prep

Window functions compute across a set of rows related to the current row without collapsing them — the tool that separates a fluent analyst from a beginner. CTEs (WITH clauses) name intermediate steps so complex logic reads top-to-bottom.

Ranking: ROW_NUMBER() (unique 1..n), RANK() (ties share a rank, leaves gaps), DENSE_RANK() (ties share, no gaps).
Offset: LAG()/LEAD() reach the previous/next row — month-over-month deltas, retention.
Running totals / moving averages: aggregates with an OVER (... ORDER BY ...) frame.
The PARTITION BY clause is a per-window GROUP BY; ORDER BY inside OVER defines sequence.

-- Each customer's orders ranked newest-first, plus days since prior order
WITH ranked AS (
  SELECT
    customer_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn,
    order_date - LAG(order_date) OVER (
      PARTITION BY customer_id ORDER BY order_date
    ) AS days_since_prev
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;  -- most recent order per customer

Top-N-per-groupThe 'top order / latest event per customer' question is a window-function tell. ROW_NUMBER() OVER (PARTITION BY id ORDER BY ...) then filter rn = 1. Doing it with a correlated subquery works but signals you don't know windows.

You can't filter a window in WHEREWindow functions are computed after WHERE/GROUP BY, so you cannot reference rn in the same query's WHERE. Wrap it in a CTE or subquery and filter in the outer query.

Window functions & CTEs

◆ Lock it in