Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Example—running totals: WITH base AS (SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total FROM orders), enriched AS (SELECT b.*, c.tier FROM base b JOIN customers c ON b.customer_id = c.id) SELECT * FROM...
Example—running totals: WITH base AS (SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total FROM orders), enriched AS (SELECT b., c.tier FROM base b JOIN customers c ON b.customer_id = c.id) SELECT FROM enriched WHERE running_total > 1000. Why: CTEs for readability and optimizer; window over correlated subquery; filter early. Scalability: Index join columns; EXPLAIN ANALYZE to validate plan.
Red Flag: Correlated subquery when window avoids it. Pro-Move: 'Window + CTE; EXPLAIN before/after; filter before join.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.