SQL Window Functions Cheat Sheet: From Zero to Interview-Ready

If you've ever looked at ROW_NUMBER() or LAG() and thought "this looks powerful but I don't fully get it", you're not alone.

Most analysts and engineers learn window functions in a rushed way – just enough to ship a task – and then struggle when these show up in interviews.

This guide will walk you through the core window function patterns that keep coming up in real SQL interviews and day‑to‑day analytics work.

1. What is a window function, really?

A window function lets you look at other rows while still returning one row per input row.

Think of it as: "aggregate, but without collapsing the rows."

We always combine two parts:

• The function – e.g. ROW_NUMBER(), SUM(), AVG(), LAG()
• The window – defined by OVER (PARTITION BY ... ORDER BY ...)

sql
ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY order_date
)

You should always ask:

1. Partition by what group? (Which rows belong together?)
2. Order by what column? (What is the sequence within each group?)

2. Ranking rows: ROW_NUMBER vs RANK vs DENSE_RANK

"Find the top 3 orders per customer by amount."

This is the classic ranking pattern.

sql
SELECT
  customer_id,
  order_id,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
  ) AS row_num,
  RANK() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
  ) AS rnk,
  DENSE_RANK() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
  ) AS dense_rnk
FROM orders;

How to remember the difference:

• ROW_NUMBER() – no ties, always 1,2,3,4…
• RANK() – ties leave gaps (1,2,2,4)
• DENSE_RANK() – ties do not leave gaps (1,2,2,3)

In interviews, when they say:

• "Top 3 orders per customer" → usually ROW_NUMBER()
• "Top 3 ranks including ties" → RANK() or DENSE_RANK() depending on gaps

3. Top N per group pattern

"For each country, find the top 2 products by revenue."

This pattern shows up in almost every interview.

sql
WITH product_revenue AS (
  SELECT
    country,
    product_id,
    SUM(revenue) AS total_revenue
  FROM sales
  GROUP BY country, product_id
),
ranked AS (
  SELECT
    country,
    product_id,
    total_revenue,
    ROW_NUMBER() OVER (
      PARTITION BY country
      ORDER BY total_revenue DESC
    ) AS row_num
  FROM product_revenue
)
SELECT
  country,
  product_id,
  total_revenue
FROM ranked
WHERE row_num <= 2
ORDER BY country, total_revenue DESC;

If you can write this from scratch under light pressure, you're already ahead of many candidates.

4. Running totals (cumulative sums)

"Show daily revenue and cumulative revenue over time."

This is where ROWS BETWEEN finally makes sense.

sql
WITH daily_revenue AS (
  SELECT
    order_date,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_revenue
ORDER BY order_date;

Key ideas:

• UNBOUNDED PRECEDING – start from the very first row
• CURRENT ROW – end at the current row
• Together: running total from start up to today

5. LAG and LEAD for day‑over‑day change

"For each day, compare revenue to the previous day."

Perfect use case for LAG():

sql
WITH daily_revenue AS (
  SELECT
    order_date,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date,
  revenue,
  LAG(revenue) OVER (
    ORDER BY order_date
  ) AS prev_day_revenue,
  revenue - LAG(revenue) OVER (
    ORDER BY order_date
  ) AS diff_from_prev_day
FROM daily_revenue
ORDER BY order_date;

Interviewers like to ask follow‑ups:

• "How would you calculate percentage change?"
• "What happens on the first row?" (LAG returns NULL by default.)

6. Moving averages (rolling windows)

"Compute a 7‑day rolling average of daily active users."

This is a very realistic product‑analytics question.

sql
WITH dau AS (
  SELECT
    activity_date,
    COUNT(DISTINCT user_id) AS daily_users
  FROM user_activity
  GROUP BY activity_date
)
SELECT
  activity_date,
  daily_users,
  AVG(daily_users) OVER (
    ORDER BY activity_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS dau_7d_avg
FROM dau
ORDER BY activity_date;

The window 6 PRECEDING AND CURRENT ROW means:

• Look at the current day + previous 6 days → 7‑day window.

7. Last non‑NULL value with window functions

"Forward‑fill the last known non‑NULL price for each product."

This is more advanced but extremely useful.

sql
WITH price_points AS (
  SELECT
    product_id,
    price_date,
    price
  FROM product_prices
),
with_groups AS (
  SELECT
    product_id,
    price_date,
    price,
    SUM(CASE WHEN price IS NOT NULL THEN 1 ELSE 0 END)
      OVER (
        PARTITION BY product_id
        ORDER BY price_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS grp
  FROM price_points
)
SELECT
  product_id,
  price_date,
  MAX(price) OVER (
    PARTITION BY product_id, grp
  ) AS filled_price
FROM with_groups
ORDER BY product_id, price_date;

This pattern can turn into a great discussion point in senior interviews.

8. How to practice window functions the right way

Reading this cheat sheet once won't make you fluent.

Here's how to practice effectively:

1. Re‑implement the examples in your own SQL environment.
2. Change the partitioning or ordering and predict what will happen before you run the query.
3. Take any analytics question you've already solved and ask: "Could a window function make this cleaner?"
4. Explain each query aloud as if you were teaching a junior teammate.

If you want structured practice with hands‑on problems that use these exact patterns in realistic schemas, explore all problems tagged around window functions in the catalog: Browse SQL catalog about window functions.

It trains you on the query patterns that keep showing up at top companies – so window functions feel natural, not scary, in your next interview.