SQL fundamentals

SQL window functions, one example at a time

Window functions are the single highest-leverage SQL skill for data work — and the topic most interviews use to separate real practitioners from tutorial readers. Every example below runs as-is on PostgreSQL, Snowflake, BigQuery, Databricks or DuckDB.
Become a member — 7 days free Practice SQL interview questions

The anatomy of OVER()

function(expr) OVER (
    PARTITION BY  <split rows into groups>
    ORDER BY      <order inside each group>
    ROWS/RANGE    <which rows form the frame>
)

PARTITION BY is "per customer / per day / per device". ORDER BY gives the rows a sequence, which is what makes running totals and LAG meaningful. The frame decides how many rows around the current one are included.

Deduplication: the pattern you'll use weekly

CDC feeds and event streams deliver the same key many times. Keep the latest:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY order_id
               ORDER BY updated_at DESC
           ) AS rn
    FROM raw.orders_stream
)
SELECT * FROM ranked WHERE rn = 1;

-- Snowflake / BigQuery / Databricks shortcut:
SELECT * FROM raw.orders_stream
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

Ranking: ROW_NUMBER vs RANK vs DENSE_RANK

SELECT player,
       score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM scores;

player | score | row_num | rnk | dense
-------+-------+---------+-----+------
 ana   |   980 |       1 |   1 |     1
 bruno |   940 |       2 |   2 |     2
 caio  |   940 |       3 |   2 |     2
 dora  |   900 |       4 |   4 |     3

Look at Caio: same score as Bruno, three different answers. That distinction is a classic interview question.

Running totals and moving averages

SELECT ordered_at,
       amount,
       SUM(amount) OVER (
           ORDER BY ordered_at
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue,
       AVG(amount) OVER (
           ORDER BY ordered_at
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS revenue_7d_avg
FROM daily_revenue
ORDER BY ordered_at;

Write the frame out every time. The implicit default (RANGE UNBOUNDED PRECEDING) lumps tied ORDER BY values together and silently produces a different number than most people expect.

LAG and LEAD: comparing a row to its neighbours

Month-over-month growth per customer, plus gap between purchases:

SELECT customer_id,
       month,
       revenue,
       LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month))
             / NULLIF(LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month), 0), 1) AS mom_pct,
       LEAD(month) OVER (PARTITION BY customer_id ORDER BY month) AS next_active_month
FROM monthly_customer_revenue;

NULLIF(..., 0) is what stops the whole query dying on a division by zero the first month a customer appears.

Sessionization: the senior-level example

Group events into sessions when the gap exceeds 30 minutes — LAG plus a cumulative sum:

WITH gaps AS (
    SELECT user_id, event_at,
           CASE WHEN event_at - LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at)
                     > INTERVAL '30 minutes'
                THEN 1 ELSE 0 END AS is_new_session
    FROM events
)
SELECT user_id, event_at,
       SUM(is_new_session) OVER (
           PARTITION BY user_id ORDER BY event_at
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_number
FROM gaps;

If you can write this from scratch and explain it, you are past the SQL bar at most companies. We drill it as a hands-on exercise inside the SQL track.

Three mistakes reviewers always catch

  • Filtering on the window column in WHERE — wrap it in a CTE or use QUALIFY.
  • Leaving the frame implicit and getting the wrong running total on tied timestamps.
  • ORDER BY on a non-unique column for deduplication — add a tiebreaker so the result is deterministic.

FAQ

What is a window function in SQL?
A window function computes a value across a set of rows related to the current row (its window) without collapsing them into one row. Unlike GROUP BY, every input row stays in the output and gains an extra computed column.
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
For ties: ROW_NUMBER gives 1,2,3,4 (arbitrary but unique), RANK gives 1,2,2,4 (gaps after ties), DENSE_RANK gives 1,2,2,3 (no gaps). Use ROW_NUMBER for deduplication, RANK/DENSE_RANK for leaderboards.
How do I deduplicate rows with a window function?
ROW_NUMBER() OVER (PARTITION BY the_business_key ORDER BY updated_at DESC) in a CTE, then filter WHERE rn = 1 in the outer query. This is the standard latest-record-wins pattern in ELT pipelines.
What is PARTITION BY vs GROUP BY?
PARTITION BY splits rows into groups for the window calculation but keeps all rows. GROUP BY collapses each group into a single row. If you need the detail and the aggregate side by side, that is PARTITION BY.
Can I use a window function in a WHERE clause?
No — windows are evaluated after WHERE. Wrap the query in a CTE or subquery and filter on the window column in the outer query. Alternatively use QUALIFY in Snowflake, BigQuery or Databricks.
What is a running total in SQL?
SUM(amount) OVER (ORDER BY ordered_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Always state the frame explicitly — the default frame changes behaviour when there are duplicate ORDER BY values.

Ready to become a member?

7 days free. Then less than a coffee per month — cancel anytime.

Become a member — 7 days free