Advanced SQL: Window Functions & CTEs for Complex Analysis Questions

Advanced SQL techniques using window functions (ROW_NUMBER, RANK, DENSE_RANK, etc.) and common table expressions (CTEs), including recursive queries, for complex data analysis, ranking and analytics patterns, cumulative totals, and multi-step data transformations within relational databases and data warehousing contexts.

MediumTechnical

20 practiced

You are given an event table with one row per order and irregular timestamps. A product manager wants a rolling 7-day order count per store, but analysts disagree on whether that means the previous 168 hours or the current day plus the previous 6 calendar days. How would you clarify the requirement and implement the query so boundary cases are unambiguous?

Sample Answer

**Clarify first**I would ask whether the product manager wants a time-based window or a calendar window. A 168-hour window means the last 7 times 24 hours from each event timestamp. A calendar window means the current day plus the previous 6 calendar days in the store's business timezone. Those are not the same at midnight boundaries.

**Implementation choices**- If they want 168 hours, use a timestamp window.- If they want calendar days, aggregate by date first, then roll up daily counts.

sql

-- Calendar-day version
WITH daily AS (
  SELECT
    store_id,
    CAST(order_ts AS date) AS order_date,
    COUNT(*) AS orders
  FROM orders
  GROUP BY store_id, CAST(order_ts AS date)
)
SELECT
  store_id,
  order_date,
  SUM(orders) OVER (
    PARTITION BY store_id
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_day_orders
FROM daily;

**Boundary example**An order at `2025-01-08 00:05` UTC is inside the calendar-day window for Jan 8 through Jan 2, but in a strict 168-hour window it depends on the exact timestamp cutoff. I would document that choice in the metric definition so analysts get the same answer.

HardTechnical

23 practiced

Your team owns a daily leaderboard built from a fact table with billions of rows. The query uses several window calculations over partitions by customer, country, and day, and it starts spilling to disk during peak load. What would you look at to tune it without changing the business result?

MediumTechnical

22 practiced

Finance wants a month-to-date revenue trend by product from a daily sales fact table, but some product-day combinations are missing because there were no sales. The report still needs to show zero-revenue days and reset correctly at each month boundary. How would you structure the query and what reference data, if any, would you need?

Sample Answer

**Approach**I would build a date spine, which is a table of every calendar date in the reporting range, then cross join it to the product list. That guarantees zero-sales days are present. After that, I would left join the daily sales fact and use a running sum partitioned by product and month.

sql

WITH spine AS (
  SELECT d::date AS dt
  FROM generate_series(date '2025-01-01', date '2025-01-31', interval '1 day') AS g(d)
), daily AS (
  SELECT product_id, sale_date, SUM(revenue) AS revenue
  FROM sales_fact
  GROUP BY product_id, sale_date
), base AS (
  SELECT p.product_id, s.dt
  FROM products p
  CROSS JOIN spine s
)
SELECT
  b.product_id,
  b.dt,
  COALESCE(d.revenue, 0) AS revenue,
  SUM(COALESCE(d.revenue, 0)) OVER (
    PARTITION BY b.product_id, DATE_TRUNC('month', b.dt)
    ORDER BY b.dt
  ) AS mtd_revenue
FROM base b
LEFT JOIN daily d
  ON d.product_id = b.product_id
 AND d.sale_date = b.dt;

**Reference data needed**- A product dimension.- A calendar or date spine table, or a generated date series.

**Why it works**Partitioning by month resets the running total at each boundary, and the spine ensures missing product-day combinations still show as zero.

MediumTechnical

28 practiced

An app team wants to find each user's longest streak of consecutive active days, and also return the start date, end date, and streak length. Multiple events can occur on the same day, and some users have gaps caused by missing source data. How would you solve this in SQL, and how would you guard against counting bad data as broken streaks?

Sample Answer

**Approach**I would first dedupe to one row per user per active day, because multiple events on the same day should not create extra streak length. Then I would use the gaps-and-islands pattern. An island is a run of consecutive dates. A simple way to label each island is to subtract the row number from the date, because consecutive dates keep the same shifted value.

sql

WITH active_days AS (
  SELECT DISTINCT user_id, activity_date
  FROM raw_events
  WHERE activity_date IS NOT NULL
), islands AS (
  SELECT
    user_id,
    activity_date,
    activity_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY activity_date
    ) * INTERVAL '1 day') AS island_key
  FROM active_days
)
SELECT user_id,
       MIN(activity_date) AS start_date,
       MAX(activity_date) AS end_date,
       COUNT(*) AS streak_length
FROM islands
GROUP BY user_id, island_key;

**Handling bad data**If there are known missing source days, I would join to a data-quality calendar and exclude those outage dates before building streaks. That prevents a pipeline failure from looking like a user break.

**Worked example**For dates `1, 2, 3, 5`, the first three share one island. The `5` starts a new one. If day `4` was a confirmed outage, I would not count it as a true break.

EasyTechnical

21 practiced

Your orders table can contain multiple rows per customer because of updates and replays. A downstream report needs exactly one row per customer: the most recent order, and if two rows share the same timestamp the choice must be deterministic. How would you write the SQL, and how would you adjust it if the business later asks to keep every row tied for the latest timestamp?

Sample Answer

**One deterministic row per customer**Use `ROW_NUMBER()`, which assigns 1 to a single row after you specify a full tie-break order. `ROW_NUMBER()` is a window function, meaning it ranks rows inside each customer group without collapsing them.

sql

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC, order_id DESC
    ) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

The timestamp decides recency, and `order_id DESC` makes ties deterministic.

**If business later wants every row tied for the latest timestamp**Switch to `RANK()` and filter on `1`.

sql

WITH ranked AS (
  SELECT
    o.*,
    RANK() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC
    ) AS rnk
  FROM orders o
)
SELECT *
FROM ranked
WHERE rnk = 1;

**Example**If customer 7 has rows at `10:00`, `10:00`, and `09:30`, the first query returns one row. The second returns both `10:00` rows.

Unlock Full Question Bank

Get access to hundreds of Advanced SQL: Window Functions & CTEs for Complex Analysis interview questions and detailed answers.

Join thousands of developers preparing for their dream job.