Common Table Expressions and Subqueries Questions

Covers writing and structuring complex SQL queries using Common Table Expressions and subqueries, including when to prefer one approach over another for readability, maintainability, and performance. Candidates should be able to author WITH clauses to break multi step logic into clear stages, implement recursive CTEs for hierarchical data, and use subqueries in SELECT, FROM, and WHERE clauses. This topic also includes understanding correlated versus non correlated subqueries, how subqueries interact with joins and window functions, and practical guidance on choosing CTEs, subqueries, or joins based on clarity and execution characteristics. Interviewers may probe syntax, typical pitfalls, refactoring nested queries into CTEs, testing and validating each step of a CTE pipeline, and trade offs that affect execution plans and index usage.

HardTechnical

34 practiced

Refactor the following nested correlated subqueries into a CTE + JOIN + window-function approach for efficiency. Original (conceptual):

SELECT t.id, (SELECT COUNT(*) FROM events e WHERE e.tid = t.id AND e.value > (SELECT AVG(value) FROM events WHERE tid = t.id))
FROM targets t;

Explain your steps and provide the optimized SQL.

Sample Answer

Approach — replace nested correlated subqueries by computing per-tid aggregates once, then joining and counting. Two efficient patterns: (A) use a window function to attach the per-tid average to every event row and aggregate; (B) compute avg per tid in a grouped CTE and join to events to count. Both avoid repeated AVG calculations per event and are significantly faster on large data.

Optimized SQL (window-function version):

sql

WITH events_with_avg AS (
  SELECT
    tid,
    value,
    AVG(value) OVER (PARTITION BY tid) AS avg_value
  FROM events
)
SELECT
  t.id,
  COALESCE(e.cnt, 0) AS events_above_avg
FROM targets t
LEFT JOIN (
  SELECT
    tid,
    SUM(CASE WHEN value > avg_value THEN 1 ELSE 0 END) AS cnt
  FROM events_with_avg
  GROUP BY tid
) e ON e.tid = t.id;

Alternative (grouped-aggregate + join — often just as fast and clearer):

sql

WITH avg_per_tid AS (
  SELECT tid, AVG(value) AS avg_value
  FROM events
  GROUP BY tid
)
SELECT
  t.id,
  COALESCE(SUM(CASE WHEN e.value > a.avg_value THEN 1 ELSE 0 END), 0) AS events_above_avg
FROM targets t
LEFT JOIN avg_per_tid a ON a.tid = t.id
LEFT JOIN events e ON e.tid = t.id
GROUP BY t.id;

Why this is better:- Eliminates per-row correlated subqueries; avg computed once per tid.- Uses set-based operations and avoids repeated scans.- Window version is convenient when you want row-level access to the group metric; grouped-aggregate is simple and often uses indexes well.- Use LEFT JOIN + COALESCE to ensure targets with no events return 0.

Performance tips:- Index events(tid) and events(tid, value) for faster grouping/filtering.- For very large event tables, consider partitioning by tid or pre-aggregating into summary tables if tid cardinality is manageable.

EasyTechnical

38 practiced

Explain the differences between: (a) an inline subquery / derived table, (b) a CTE (WITH clause), and (c) a temporary table. Cover scope, reuse, lifecycle, and typical scenarios where a data analyst would prefer one over the others.

MediumTechnical

34 practiced

Given a table of product reviews with columns (product_id, rating, review_date), write a SQL query that computes the weighted average rating per product where more recent reviews have higher weight. Use a CTE to compute weights and handle NULL ratings safely.

Sample Answer

Approach: compute a recency-based weight per review in a CTE (using days from the most recent review per product), ignore NULL ratings in the weighted sum (so they don't bias numerator/denominator), then aggregate per product as SUM(rating*weight)/SUM(weight). Below example uses an exponential decay weight (0.95^days_diff) — easy to tune.

sql

WITH latest AS (
  -- most recent review_date per product
  SELECT product_id, MAX(review_date) AS max_date
  FROM product_reviews
  GROUP BY product_id
),
weighted_reviews AS (
  -- compute days difference and weight; ignore NULL ratings by filtering here
  SELECT
    r.product_id,
    r.rating,
    l.max_date,
    CAST(julianday(l.max_date) - julianday(r.review_date) AS INTEGER) AS days_diff,
    POWER(0.95, CAST(julianday(l.max_date) - julianday(r.review_date) AS INTEGER)) AS weight
  FROM product_reviews r
  JOIN latest l USING (product_id)
  WHERE r.rating IS NOT NULL
)
SELECT
  product_id,
  -- safe division: returns NULL if no non-null ratings
  CASE WHEN SUM(weight) = 0 THEN NULL
       ELSE ROUND(SUM(rating * weight) / SUM(weight), 3)
  END AS weighted_avg_rating,
  SUM(weight) AS total_weight,
  COUNT(*) AS non_null_rating_count
FROM weighted_reviews
GROUP BY product_id;

Key points:- CTEs separate concerns: latest date, weight calc, aggregation.- NULL ratings excluded so they don't add weight; adjust if business wants to penalize missing ratings.- Exponential decay (0.95^days) emphasizes recent reviews; tune base (0.9–0.99) or replace with linear/inverse formula.- Uses safe division to return NULL when no valid ratings.

HardTechnical

27 practiced

Implement sessionization using SQL and CTEs: assign session_id per user where sessions are defined as sequences of events separated by less than 30 minutes. Table:

-- events(user_id int, occurred_at timestamp)

Return: user_id, session_start, session_end, event_count for each session. Explain window-function steps and edge cases.

Sample Answer

Approach: Use ordering per user, compute time difference from previous event, mark session boundaries where gap >= 30 minutes, assign session ids via cumulative sum, then aggregate session start/end/count.

sql

WITH ordered AS (
  SELECT
    user_id,
    occurred_at,
    LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_at
  FROM events
),
flags AS (
  SELECT
    user_id,
    occurred_at,
    CASE
      WHEN prev_at IS NULL THEN 1                    -- first event for user -> new session
      WHEN occurred_at - prev_at >= INTERVAL '30 minutes' THEN 1 -- gap -> new session
      ELSE 0
    END AS is_new_session
  FROM ordered
),
session_ids AS (
  SELECT
    user_id,
    occurred_at,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY occurred_at ROWS UNBOUNDED PRECEDING) AS session_num
  FROM flags
)
SELECT
  user_id,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  COUNT(*) AS event_count
FROM session_ids
GROUP BY user_id, session_num
ORDER BY user_id, session_start;

Key window-function steps:- LAG(...) finds previous event timestamp per user.- CASE converts gaps to boundary flags.- Cumulative SUM over ordered rows produces a dense session number per user (increments at each boundary).- Final GROUP BY session_num collapses events into session intervals.

Edge cases & notes:- Exactly 30 minutes: above query treats >= 30m as new session; adjust operator if you want strictly greater.- Out-of-order or duplicate timestamps: ORDER BY occurred_at only — break ties with an event id if needed.- Sparse data types: ensure timezone consistency on timestamps.- Very large tables: pre-filter date ranges and add indexes on (user_id, occurred_at) for performance.

EasyTechnical

35 practiced

Given the following tables:

-- customers(customer_id int, name text)
-- orders(order_id int, customer_id int, total_amount numeric, order_date date)

Write a SQL query (PostgreSQL) using a CTE to return customers whose average order value is greater than the overall average order value across all customers. Show the WITH clause and the final SELECT.

Sample Answer

Approach: compute each customer's average order value, compute the overall average across customers, then select customers whose per-customer average exceeds the overall average. Use two CTEs for clarity.

sql

WITH per_customer_avg AS (
  SELECT
    o.customer_id,
    AVG(o.total_amount) AS avg_order_value
  FROM orders o
  GROUP BY o.customer_id
),
overall_avg AS (
  -- average of per-customer averages (gives equal weight per customer)
  SELECT AVG(avg_order_value) AS overall_avg_value
  FROM per_customer_avg
)
SELECT
  c.customer_id,
  c.name,
  p.avg_order_value
FROM per_customer_avg p
JOIN customers c ON c.customer_id = p.customer_id
CROSS JOIN overall_avg oa
WHERE p.avg_order_value > oa.overall_avg_value
ORDER BY p.avg_order_value DESC;

Key points:- per_customer_avg groups orders by customer and computes their average.- overall_avg computes the average of those per-customer averages (each customer weighted equally). If you instead want overall average across all orders, compute AVG(total_amount) directly from orders.Edge cases:- Customers with no orders are excluded (no entries in per_customer_avg). If you want them shown with NULL or 0, LEFT JOIN customers and handle NULLs.- Handle NULL total_amounts by filtering them out in the aggregation (e.g., WHERE total_amount IS NOT NULL). Continuous numeric types may need casting/rounding for display.

Unlock Full Question Bank

Get access to hundreds of Common Table Expressions and Subqueries interview questions and detailed answers.

Join thousands of developers preparing for their dream job.