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

39 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

29 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.

MediumTechnical

27 practiced

An executive dashboard needs the top 3 products by revenue in each region. If multiple products tie at the cutoff, every tied product must appear, but revenue should be computed from raw line items without double counting order-level facts. How would you build the query so the aggregation and ranking both stay correct?

Sample Answer

**Approach**First aggregate at the raw line-item grain, then rank the product totals within each region. A line item is one row of product revenue, so summing those rows avoids double counting order-level facts. For the tie rule, use `DENSE_RANK()` or `RANK()` on the final product totals, then keep ranks `<= 3`.

sql

WITH product_revenue AS (
  SELECT
    region,
    product_id,
    SUM(line_revenue) AS revenue
  FROM raw_line_items
  GROUP BY region, product_id
), ranked AS (
  SELECT
    region,
    product_id,
    revenue,
    DENSE_RANK() OVER (
      PARTITION BY region
      ORDER BY revenue DESC
    ) AS revenue_rank
  FROM product_revenue
)
SELECT region, product_id, revenue
FROM ranked
WHERE revenue_rank <= 3;

**Why this is correct**- The aggregation happens before ranking, so each product appears once per region.- `DENSE_RANK()` includes every product tied at the cutoff.- If you also need order-level facts, aggregate them in a separate CTE at order grain and join the finished summaries, not the raw tables.

**Worked example**If a region has revenues 100, 90, 90, 80, the ranks are 1, 2, 2, 3. Filtering `<= 3` returns all four products, which matches the tie requirement.

HardTechnical

40 practiced

A finance team wants monthly customer revenue reported against the customer segment that was valid when each order happened, not the segment the customer has today. Some dimension updates arrive late, and some orders are backfilled after the month closes. How would you structure the SQL transformation so the numbers are reproducible, auditable, and easy to reconcile?

Sample Answer

**Approach**I would model the customer segment as a slowly changing dimension, meaning a history table with `valid_from` and `valid_to` timestamps. Then I would do an as-of join, which matches each order to the dimension row that was valid when the order happened, not the current row.

sql

SELECT
  DATE_TRUNC('month', o.order_ts) AS month,
  d.segment,
  SUM(o.revenue) AS revenue
FROM orders o
JOIN dim_customer_history d
  ON o.customer_id = d.customer_id
 AND o.order_ts >= d.valid_from
 AND o.order_ts <  d.valid_to
GROUP BY 1, 2;

**Why this is auditable**- The join is driven by the order timestamp, so reclassifying a customer today does not rewrite history.- I would keep immutable raw orders, the dimension history, and the monthly mart as separate layers.- I would store the load batch or snapshot date so finance can reproduce the exact closing report.

**Late data handling**If an order is backfilled after month close, I would rerun the affected month from raw facts, but only against the dimension history that was effective at the order time. That keeps the numbers consistent and explainable during reconciliation.

HardTechnical

51 practiced

A query that used to run in seconds now takes minutes after a rewrite into several CTEs for readability. The result is still correct, but the warehouse scan shows repeated work on the same large tables. How would you investigate whether the CTE structure is helping or hurting, and what would you change first if the execution plan looks suspicious?

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.