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.

HardTechnical

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

HardTechnical

66 practiced

A marketing team has impression, click, and conversion tables with user_id and event_time. Design a SQL analysis that attributes each conversion to the first click within a 7-day lookback window, then reports conversion rate by campaign. Explain how you would structure the CTEs and how you would avoid double-attributing one conversion to multiple clicks.

Sample Answer

I’d structure this as a conversion-attribution pipeline with one row per conversion, then attach the qualifying click.

sql

WITH clicks AS (
  SELECT user_id, campaign_id, event_time AS click_time,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, event_time
           ORDER BY event_time
         ) AS click_seq
  FROM click_table
), conversions AS (
  SELECT user_id, event_time AS conv_time
  FROM conversion_table
), attributed AS (
  SELECT c.user_id, c.conv_time, ck.campaign_id, ck.click_time,
         ROW_NUMBER() OVER (
           PARTITION BY c.user_id, c.conv_time
           ORDER BY ck.click_time ASC
         ) AS rn
  FROM conversions c
  JOIN clicks ck
    ON c.user_id = ck.user_id
   AND ck.click_time <= c.conv_time
   AND ck.click_time >= c.conv_time - INTERVAL '7 days'
)
SELECT campaign_id,
       COUNT(*) AS attributed_conversions,
       COUNT(*)::FLOAT / COUNT(DISTINCT conv_time) AS conversion_rate
FROM attributed
WHERE rn = 1
GROUP BY campaign_id;

**Why this structure**- The first CTE normalizes clicks.- The second isolates conversions.- The attribution CTE ranks eligible clicks and keeps only the first one per conversion.

**Avoiding double attribution**A conversion should be matched to exactly one click. If I skipped the `ROW_NUMBER()` filter, one conversion could join to many clicks inside the 7-day window and be counted multiple times. I’d also define a clear lookback policy for same-timestamp ties and test that each conversion appears at most once.

MediumTechnical

65 practiced

Design a SQL query for cohort retention analysis using tables users(user_id, signup_date) and events(user_id, event_date). For each signup month, return the percentage of users who were active in month 0, month 1, and month 2 after signup. Use CTEs to make the logic easy to audit.

Sample Answer

I’d build this as a cohort pipeline in CTEs so each step is easy to audit.

sql

WITH cohort_users AS (
  SELECT
    user_id,
    DATE_TRUNC('month', signup_date) AS signup_month,
    DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
user_activity AS (
  SELECT DISTINCT
    u.user_id,
    u.signup_month,
    DATE_PART('year', AGE(DATE_TRUNC('month', e.event_date), u.signup_month)) * 12
      + DATE_PART('month', AGE(DATE_TRUNC('month', e.event_date), u.signup_month)) AS month_n
  FROM cohort_users u
  JOIN events e ON e.user_id = u.user_id
  WHERE e.event_date >= u.signup_month
),
cohort_sizes AS (
  SELECT signup_month, COUNT(*) AS users
  FROM cohort_users
  GROUP BY signup_month
),
retention AS (
  SELECT signup_month, month_n, COUNT(DISTINCT user_id) AS active_users
  FROM user_activity
  WHERE month_n BETWEEN 0 AND 2
  GROUP BY signup_month, month_n
)
SELECT
  r.signup_month,
  ROUND(100.0 * MAX(CASE WHEN month_n = 0 THEN active_users END) / c.users, 2) AS m0_pct,
  ROUND(100.0 * MAX(CASE WHEN month_n = 1 THEN active_users END) / c.users, 2) AS m1_pct,
  ROUND(100.0 * MAX(CASE WHEN month_n = 2 THEN active_users END) / c.users, 2) AS m2_pct
FROM cohort_sizes c
LEFT JOIN retention r USING (signup_month)
GROUP BY r.signup_month, c.users
ORDER BY r.signup_month;

I’d define “active” carefully: any event, or a meaningful event like purchase/login. I’d also confirm partial months, timezone handling, and whether month 0 should include the signup month only or a 30-day window.

MediumTechnical

47 practiced

Given event_log(user_id, event_time, event_name), write a CTE-based SQL query that computes a three-step funnel: visited landing page, viewed product, purchased. Return step-level counts and conversion rates, and make sure each user is counted at most once per step in the correct chronological order.

Sample Answer

I’d structure this as a stepwise funnel so each stage only counts users who completed the prior stage in order.

sql

WITH landing AS (
  SELECT user_id, MIN(event_time) AS landing_time
  FROM event_log
  WHERE event_name = 'visited_landing_page'
  GROUP BY user_id
),
product AS (
  SELECT e.user_id, MIN(e.event_time) AS product_time
  FROM event_log e
  JOIN landing l ON e.user_id = l.user_id
  WHERE e.event_name = 'viewed_product'
    AND e.event_time > l.landing_time
  GROUP BY e.user_id
),
purchase AS (
  SELECT e.user_id, MIN(e.event_time) AS purchase_time
  FROM event_log e
  JOIN product p ON e.user_id = p.user_id
  WHERE e.event_name = 'purchased'
    AND e.event_time > p.product_time
  GROUP BY e.user_id
)
SELECT 'landing' AS step, COUNT(*) AS users, 1.0 AS conversion_rate FROM landing
UNION ALL
SELECT 'product', COUNT(*), COUNT(*)::numeric / (SELECT COUNT(*) FROM landing) FROM product
UNION ALL
SELECT 'purchase', COUNT(*), COUNT(*)::numeric / (SELECT COUNT(*) FROM landing) FROM purchase;

**Key idea**I use `MIN(event_time)` so each user is counted once per step. The joins enforce chronological order.

**Notes**- I’d define whether the funnel is same-session or across all time.- I’d also decide how to treat repeated events and whether late-arriving events can rewrite history.- For stakeholders, I’d present both step counts and step-to-step conversion, because the denominator choice changes the story.

HardTechnical

57 practiced

Given segment_scores(segment, user_id, score), write a query that returns the median score and the 90th percentile score for each segment. If your SQL dialect does not support a native percentile function, explain a window-function-based workaround and its trade-offs.

Sample Answer

I’d compute the median and 90th percentile per segment using ordered window logic or a native percentile function if available.

sql

WITH ranked AS (
  SELECT segment, score,
         ROW_NUMBER() OVER (PARTITION BY segment ORDER BY score) AS rn,
         COUNT(*) OVER (PARTITION BY segment) AS cnt
  FROM segment_scores
)
SELECT segment,
       AVG(CASE WHEN rn IN ((cnt + 1) / 2, (cnt + 2) / 2) THEN score END) AS median_score,
       MAX(CASE WHEN rn = CEIL(0.9 * cnt) THEN score END) AS p90_score
FROM ranked
GROUP BY segment;

**How it works**- `ROW_NUMBER()` gives each score an ordered position within its segment.- `COUNT()` tells us how many rows are in the segment.- The median is the middle value, or the average of the two middle values when the count is even.

**Trade-offs**This workaround is portable, but it is approximate in the sense that percentile definitions vary by dialect. Native functions like `PERCENTILE_CONT` are usually clearer and more statistically standard. I’d document the interpolation rule so business users interpret the metric consistently across dashboards.

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.