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

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

EasyTechnical

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

HardTechnical

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

MediumTechnical

30 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

36 practiced

You inherit a parent-child category table for a product catalog. The business needs each category's full ancestor path, its depth in the hierarchy, and a safe rollup of sales to all ancestors. Some records are malformed and create cycles or orphan nodes. How would you query this with a recursive CTE while protecting the warehouse from runaway recursion?

Sample Answer

**Approach**I would use a recursive CTE. A recursive CTE is a query that repeatedly joins a result back to the base table, which is a standard way to walk a tree. I would start from each category, climb to its parent, and carry a path string plus a cycle check so bad data cannot recurse forever.

sql

WITH RECURSIVE ancestry AS (
  SELECT
    c.category_id,
    c.category_id AS ancestor_id,
    c.parent_id,
    0 AS depth,
    CAST('>' || c.category_id || '>' AS text) AS path
  FROM categories c

  UNION ALL

  SELECT
    a.category_id,
    p.category_id AS ancestor_id,
    p.parent_id,
    a.depth + 1,
    a.path || p.category_id || '>'
  FROM ancestry a
  JOIN categories p
    ON p.category_id = a.parent_id
  WHERE POSITION('>' || p.category_id || '>' IN a.path) = 0
)
SELECT *
FROM ancestry;

**How I would use it**- Build the full ancestor path by grouping the rows for each `category_id`.- Roll sales up by joining facts to `category_id`, then summing again by `ancestor_id`.- Exclude or quarantine orphans, where a parent is missing.- Add a max-depth guard if the warehouse supports it.

**Worked example**For `Shoes -> Apparel -> Root`, the CTE emits three rows with depths `0, 1, 2`. That gives a safe path and lets sales from Shoes roll up to Apparel and Root without double counting.

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.