4 SQL Window Function Patterns You Need for Interviews

Window functions still show up all the time in analytics and data interviews because they test whether you understand row-level context, ordering, and grouping without collapsing the result set. The good news is that you do not need to memorize every function in the SQL manual. Most interview questions reduce to a small set of patterns.

The four patterns below cover a large share of what companies ask in warehouse SQL, whether the environment is closest to Postgres, BigQuery, Snowflake, or Databricks SQL.

1. Running totals and cumulative metrics

Use this pattern when you need a metric that keeps accumulating over time. Typical prompts include cumulative revenue, rolling signups, or the running share of a goal.

SELECT
    event_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY event_date
    ) AS running_revenue
FROM daily_revenue
ORDER BY event_date;

The key idea is that the window follows the sort order. If you forget the ORDER BY inside OVER (...), you will get the grand total repeated on every row instead of a cumulative value.

2. Latest row per entity

This pattern is everywhere: latest subscription status per user, most recent device per account, newest salary record per employee. The usual tool is ROW_NUMBER().

SELECT
    user_id,
    status,
    updated_at
FROM (
    SELECT
        user_id,
        status,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY updated_at DESC
        ) AS row_num
    FROM user_status_history
) ranked
WHERE row_num = 1;

This is cleaner than trying to join back to a MAX(updated_at) subquery, especially when ties or extra columns are involved.

3. Ranking within a group

Use ranking functions when the prompt asks for top-N products by country, top creators by week, or the highest-performing campaigns in each channel. The main choice is between ROW_NUMBER(), RANK(), and DENSE_RANK().

SELECT
    country,
    product_id,
    orders,
    DENSE_RANK() OVER (
        PARTITION BY country
        ORDER BY orders DESC
    ) AS country_rank
FROM product_orders;

If ties should share the same position and you still want consecutive ranks, DENSE_RANK() is usually the right answer. If ties should break arbitrarily, ROW_NUMBER() may be enough.

4. Period-over-period change

Interviewers love prompts about week-over-week or month-over-month change because they mirror real dashboard work. That is where LAG() and LEAD() become valuable.

SELECT
    month,
    signups,
    LAG(signups) OVER (
        ORDER BY month
    ) AS prior_month_signups,
    signups - LAG(signups) OVER (
        ORDER BY month
    ) AS signup_change
FROM monthly_signups
ORDER BY month;

This pattern helps you compare each row to the previous or next row without a self-join. In interviews, say out loud how you would handle missing prior periods or duplicate dates.

How to practice window functions the right way

Do not memorize isolated formulas. Instead, practice translating business questions into one of the patterns above:

• running metric,
• latest record,
• ranking,
• or comparison to a neighboring period.

Once you recognize the pattern, the SQL gets much easier. If you want hands-on reps, solve a few ranking and retention questions in the SQLPad question set and then rerun the logic in the playground so you can inspect each step.