TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 12

Subqueries Part 2 and LATERAL: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CORRELATED & LATERAL - CONCEPTUAL

  1. Q1What is a correlated subquery?
  2. Q2Difference between correlated and uncorrelated.
  3. Q3What is LATERAL JOIN?
  4. Q4CROSS JOIN LATERAL vs LEFT JOIN LATERAL?
  5. Q5Why must LATERAL reference earlier FROM items?
  6. Q6Performance: per-row evaluation.
  7. Q7When is LATERAL the only correct choice?
  8. Q8LATERAL + LIMIT for top-N per group.
  9. Q9Compare correlated subquery in SELECT vs LATERAL in FROM.
  10. Q10LATERAL with returning subquery vs LATERAL with table.
  11. Q11LATERAL inside a CTE - allowed?
  12. Q12LATERAL with generate_series.
  13. Q13LATERAL with unnest.
  14. Q14LATERAL with jsonb_array_elements.
  15. Q15Why is LATERAL essential for top-N per partition pre-window functions?
  16. Q16Does LATERAL fan-out?
  17. Q17CROSS JOIN LATERAL with empty subquery - what happens?
  18. Q18LEFT JOIN LATERAL with empty subquery?
  19. Q19LATERAL in DELETE / UPDATE?
  20. Q20Compare LATERAL vs row_to_json.
  21. Q21LATERAL re-execution cost - index helps?
  22. Q22LATERAL replacing scalar subquery - when faster?
  23. Q23LATERAL replacing correlated subquery - when more readable.
  24. Q24LATERAL with aggregate inside.
  25. Q25LATERAL returning multiple columns + multiple rows.

CORRELATED SUBQUERIES

  1. Q26For each order, count of same-customer orders.
  2. Q27For each customer, count of their orders.
  3. Q28For each product, avg rating.
  4. Q29For each store, count employees.
  5. Q30For each campaign, total spend.
  6. Q31For each customer, latest order_date.
  7. Q32For each product, latest review.
  8. Q33For each ticket, count comments.
  9. Q34For each employee, latest pay_slip.
  10. Q35For each shipment, customer email via correlated.
  11. Q36Customers WHERE (subquery COUNT) > N.
  12. Q37Customers WHERE EXISTS (correlated).
  13. Q38Products WHERE (subquery AVG) < threshold.
  14. Q39Orders WHERE net_total > correlated AVG by customer.
  15. Q40Employees WHERE salary > correlated AVG by dept.
  16. Q41Customers WHERE last_order > N days ago.
  17. Q42Products WHERE last_review > N days ago.
  18. Q43Tickets WHERE (correlated comment count) = 0.
  19. Q44Reviews WHERE rating < (cust's avg rating).
  20. Q45Orders WHERE net_total < (store's median).
  21. Q46Correlated UPDATE: set tier_id = (subquery from loyalty).
  22. Q47Correlated DELETE: delete orphan rows.
  23. Q48Correlated EXISTS with extra filter.
  24. Q49Correlated NOT EXISTS.
  25. Q50Correlated subquery returning aggregate.

LATERAL BASICS

  1. Q51LATERAL: per customer, get count.
  2. Q52LATERAL: per customer, get latest order.
  3. Q53LATERAL: per product, get latest review.
  4. Q54LATERAL: per store, get employee count.
  5. Q55LATERAL: per campaign, get sum of spend.
  6. Q56LATERAL: per region, get sum of revenue.
  7. Q57LATERAL: per agent, get count of tickets.
  8. Q58LATERAL: per warehouse, get latest snapshot.
  9. Q59LATERAL: per supplier, get count of products.
  10. Q60LATERAL: per courier, get avg delivery time.
  11. Q61CROSS JOIN LATERAL vs LEFT JOIN LATERAL example.
  12. Q62LATERAL returning multiple columns.
  13. Q63LATERAL with WHERE inside.
  14. Q64LATERAL with ORDER BY inside.
  15. Q65LATERAL with LIMIT inside.
  16. Q66LATERAL with aggregate function.
  17. Q67LATERAL with no rows - INNER vs LEFT.
  18. Q68LATERAL with generate_series.
  19. Q69LATERAL with unnest array.
  20. Q70LATERAL with jsonb_array_elements.
  21. Q71LATERAL chain: A -> B -> C.
  22. Q72LATERAL in subquery.
  23. Q73LATERAL with conditional return.
  24. Q74LATERAL with EXISTS check inside.
  25. Q75LATERAL in CTE.

TOP-N PER GROUP

  1. Q76Per customer, top 3 orders by net_total.
  2. Q77Per customer, latest 5 orders.
  3. Q78Per customer, first 3 orders.
  4. Q79Per region, top 5 stores by revenue.
  5. Q80Per category, top 3 products by units.
  6. Q81Per brand, top 5 products by sales.
  7. Q82Per agent, top 3 tickets by priority.
  8. Q83Per platform, top 5 campaigns by ROI.
  9. Q84Per supplier, 3 most recent shipments.
  10. Q85Per warehouse, top 5 products by quantity.
  11. Q86Per dept, top 3 highest paid employees.
  12. Q87Per store, employee with most tickets.
  13. Q88Per call_reason, longest call.
  14. Q89Per session, the URL visited last.
  15. Q90Per cohort, top customer LTV.
  16. Q91Per region, top 3 cities by orders.
  17. Q92Per tier, top 5 by spend.
  18. Q93Per category, top 3 brands by sales.
  19. Q94Per warehouse, oldest snapshot.
  20. Q95Per courier, fastest shipment.
  21. Q96Per campaign, top attribution day.
  22. Q97Per customer, last interaction (any source).
  23. Q98Per product, first-buyer.
  24. Q99Per agent, first-resolved ticket.
  25. Q100Per region per month, top store.

Combined ideas, multi-step thinking

DEEPER CONCEPTUAL

  1. Q1When does Postgres unnest a correlated subquery to a JOIN?
  2. Q2Why does LATERAL fan out the outer row?
  3. Q3LATERAL with a subquery referencing 2 outer FROM items.
  4. Q4Compare LATERAL JOIN ON TRUE vs CROSS JOIN LATERAL.
  5. Q5LATERAL inside view definition - when problematic.
  6. Q6Why is "Nested Loop with LATERAL" the common plan?
  7. Q7Compare LATERAL to apply (SQL Server).
  8. Q8LATERAL with parameterized subquery.
  9. Q9Multi-LATERAL chain - each refers to previous.
  10. Q10LATERAL + GROUP BY parent - interaction.
  11. Q11LATERAL inside FROM (subquery).
  12. Q12LATERAL inside RETURNING (INSERT/UPDATE).
  13. Q13Compare LATERAL with COMPOSITE TYPE.
  14. Q14LATERAL returning derived rows from function.
  15. Q15Why is LATERAL slow without right index?
  16. Q16LATERAL + window inside subquery.
  17. Q17Compare LATERAL + LIMIT 1 vs DISTINCT ON.
  18. Q18LATERAL semantic: 1 evaluation per outer row.
  19. Q19LATERAL doesn't propagate ORDER BY of subquery to outer query.
  20. Q20LATERAL with row constructor return.
  21. Q21LATERAL + UNION ALL.
  22. Q22LATERAL + EXISTS.
  23. Q23LATERAL + NOT EXISTS.
  24. Q24LATERAL with conditional subquery.
  25. Q25LATERAL with recursive CTE inside.

CORRELATED DEEPER

  1. Q26Correlated SELECT with 5 metrics per customer.
  2. Q27Correlated MIN/MAX.
  3. Q28Correlated PERCENTILE per group.
  4. Q29Correlated COUNT FILTER.
  5. Q30Correlated with multi-column join.
  6. Q31Correlated AS view-like inline.
  7. Q32Correlated subquery with EXISTS chain.
  8. Q33Correlated NOT EXISTS chain.
  9. Q34Correlated in HAVING.
  10. Q35Correlated in ORDER BY.
  11. Q36Correlated in WHERE returning 0 or 1.
  12. Q37Correlated returning ARRAY.
  13. Q38Correlated returning JSON.
  14. Q39Correlated returning composite type.
  15. Q40Correlated for "find prior event".
  16. Q41Correlated for "find next event".
  17. Q42Correlated for "is currently active".
  18. Q43Correlated for "is delinquent".
  19. Q44Correlated for "is fraud-risk".
  20. Q45Correlated for "tier-upgrade eligible".
  21. Q46Correlated for "churn-risk".
  22. Q47Correlated for "loyalty member".
  23. Q48Correlated for "lifetime stage".
  24. Q49Correlated for "premium-customer flag".
  25. Q50Correlated for "geo-zone tag".

LATERAL CHAINS

  1. Q51LATERAL 1 -> LATERAL 2 (sequential dependency).
  2. Q52LATERAL 1 -> LATERAL 2 -> LATERAL 3.
  3. Q53LATERAL with parent + grandparent ref.
  4. Q54LATERAL with multiple subqueries in one row.
  5. Q55LATERAL with conditional return.
  6. Q56LATERAL with CTE inside subquery.
  7. Q57LATERAL with set ops inside.
  8. Q58LATERAL with window inside.
  9. Q59LATERAL with aggregate + group inside.
  10. Q60LATERAL with EXISTS + CASE.
  11. Q61LATERAL with derived calendar.
  12. Q62LATERAL with date series.
  13. Q63LATERAL with array_agg.
  14. Q64LATERAL with jsonb_object_agg.
  15. Q65LATERAL with multi-column ORDER BY.
  16. Q66LATERAL "top-3 per group + total".
  17. Q67LATERAL "first non-null".
  18. Q68LATERAL with NULL handling.
  19. Q69LATERAL "is_above_avg" via inline.
  20. Q70LATERAL with multi-table subquery JOIN.
  21. Q71LATERAL with EXCEPT inside.
  22. Q72LATERAL with UNION inside.
  23. Q73LATERAL with HAVING inside.
  24. Q74LATERAL with GROUP BY ROLLUP inside.
  25. Q75LATERAL with PERCENTILE inside.

LATERAL + WINDOW COMBO

  1. Q76LATERAL with ROW_NUMBER inside subquery.
  2. Q77LATERAL with RANK inside.
  3. Q78LATERAL with NTILE.
  4. Q79LATERAL with LAG/LEAD.
  5. Q80LATERAL with FIRST_VALUE / LAST_VALUE.
  6. Q81LATERAL with PERCENT_RANK.
  7. Q82LATERAL with CUME_DIST.
  8. Q83LATERAL with running SUM.
  9. Q84LATERAL with running AVG.
  10. Q85LATERAL with moving window.
  11. Q86LATERAL with partition-by-customer + ORDER BY.
  12. Q87LATERAL with "first event in window".
  13. Q88LATERAL with "n-th event in window".
  14. Q89LATERAL with "last event in window".
  15. Q90LATERAL with "delta vs previous".
  16. Q91LATERAL with "delta vs first".
  17. Q92LATERAL with "rank within group".
  18. Q93LATERAL with "rank within tier + region".
  19. Q94LATERAL with "percentile within cohort".
  20. Q95LATERAL "year-over-year compare".
  21. Q96LATERAL "month-over-month compare".
  22. Q97LATERAL "rolling 7-day".
  23. Q98LATERAL "rolling 30-day".
  24. Q99LATERAL "rolling 90-day".
  25. Q100LATERAL + 5-metric per-row computation.

Interview grade, edge cases

LATERAL + SET OPS

  1. Q1LATERAL with UNION ALL inside.
  2. Q2LATERAL with INTERSECT inside.
  3. Q3LATERAL with EXCEPT inside.
  4. Q4Per customer, union of orders + reviews + tickets via LATERAL.
  5. Q5Per product, all event types via LATERAL UNION.
  6. Q6Per region, sum of revenue + spend via LATERAL UNION ALL.
  7. Q7Per agent, intersection of ticket and call customers.
  8. Q8Per supplier, products NOT in any order via LATERAL EXCEPT.
  9. Q9Per category, brands present in BOTH high+low revenue cohorts.
  10. Q10Per tier, count customers in each lifecycle via LATERAL UNION.
  11. Q11Per campaign, attribution + spend in single row via LATERAL UNION.
  12. Q12Per warehouse, in+out inventory deltas via LATERAL.
  13. Q13Per courier, success rate via LATERAL counts.
  14. Q14Per customer, "first 5 + last 5 orders".
  15. Q15Per product, top + bottom rated review.
  16. Q16Per region, top + bottom store revenue.
  17. Q17Per dept, top + bottom salary employee.
  18. Q18Per session, first + last page_view.
  19. Q19Per ticket, first + last comment.
  20. Q20Per call, transcript + sentiment in same row.
  21. Q21LATERAL + ROLLUP inside.
  22. Q22LATERAL + CUBE inside.
  23. Q23LATERAL + GROUPING SETS inside.
  24. Q24LATERAL + FILTER inside.
  25. Q25LATERAL + HAVING inside subquery.

PERFORMANCE

  1. Q26EXPLAIN a slow correlated subquery.
  2. Q27Rewrite to LATERAL - compare plans.
  3. Q28Rewrite to window - compare plans.
  4. Q29Add index for LATERAL subquery.
  5. Q30Index FK columns for fast LATERAL.
  6. Q31Use partial index for LATERAL filter.
  7. Q32Use expression index.
  8. Q33Pre-aggregate via CTE for LATERAL inner.
  9. Q34Force Nested Loop with LATERAL.
  10. Q35Force Hash Join (disable nestloop).
  11. Q36Increase work_mem for LATERAL aggregates.
  12. Q37Reduce subquery output to needed columns.
  13. Q38Use LIMIT to bound LATERAL fan-out.
  14. Q39Replace SELECT * with SELECT cols inside LATERAL.
  15. Q40Diagnose "no rows from LATERAL" - verify ON clause.
  16. Q41Compare CROSS JOIN LATERAL vs LEFT JOIN LATERAL - performance.
  17. Q42LATERAL inside view - composability cost.
  18. Q43LATERAL inside materialized view - caching benefits.
  19. Q44LATERAL with parallel scan - when possible.
  20. Q45LATERAL with partition-wise join.
  21. Q46LATERAL with prepared statement.
  22. Q47LATERAL with EXPLAIN ANALYZE breakdown.
  23. Q48Drop unused LATERAL (single-value can be scalar subquery).
  24. Q49Monitor LATERAL via pg_stat_statements.
  25. Q50Anti-pattern: LATERAL in ORDER BY (slow).

ANTI-PATTERNS

  1. Q51ANTIPATTERN: LATERAL for a single-value (use scalar subquery).
  2. Q52ANTIPATTERN: LATERAL with no index on FK.
  3. Q53ANTIPATTERN: LATERAL returning entire row.
  4. Q54ANTIPATTERN: LATERAL in WHERE clause as predicate.
  5. Q55ANTIPATTERN: Correlated subquery instead of LATERAL when ORDER BY+LIMIT needed.
  6. Q56ANTIPATTERN: LATERAL fanning out 1B rows.
  7. Q57ANTIPATTERN: LATERAL + GROUP BY parent on fan-out result.
  8. Q58ANTIPATTERN: LATERAL with random() - non-deterministic.
  9. Q59ANTIPATTERN: LATERAL with now() - re-evaluated.
  10. Q60ANTIPATTERN: LATERAL with side effects (volatile function).
  11. Q61ANTIPATTERN: LATERAL returning too many columns.
  12. Q62ANTIPATTERN: LATERAL when JOIN suffices.
  13. Q63ANTIPATTERN: Correlated subquery in DML loop.
  14. Q64ANTIPATTERN: Correlated subquery in JOIN ON.
  15. Q65ANTIPATTERN: Correlated subquery without index.
  16. Q66ANTIPATTERN: Mutating function in LATERAL.
  17. Q67ANTIPATTERN: LATERAL hiding fan-out from later aggregates.
  18. Q68ANTIPATTERN: LATERAL in CTE without MATERIALIZED hint.
  19. Q69ANTIPATTERN: LATERAL with complex ORDER BY (sort cost).
  20. Q70ANTIPATTERN: LATERAL with non-deterministic ORDER BY.
  21. Q71ANTIPATTERN: LATERAL + window in same query.
  22. Q72ANTIPATTERN: LATERAL with WHERE TRUE only.
  23. Q73ANTIPATTERN: LATERAL with CROSS JOIN on outer cols.
  24. Q74ANTIPATTERN: LATERAL when GROUP BY parent would aggregate.
  25. Q75ANTIPATTERN: LATERAL as the only way (refactor opportunity).

REAL PRODUCTION

  1. Q76Build a "customer top-3 events" report.
  2. Q77Build "product latest review + first review" report.
  3. Q78Build "store top-N employees" report.
  4. Q79Build "region monthly top sellers".
  5. Q80Build "category breadcrumb + product top-3".
  6. Q81Build "supplier top shipments + count".
  7. Q82Build "campaign attribution + ROI".
  8. Q83Build "warehouse low-stock + reorder suggestions".
  9. Q84Build "agent leaderboard".
  10. Q85Build "courier scorecard".
  11. Q86Build "tier members + their top order".
  12. Q87Build "loyalty redemptions + remaining balance".
  13. Q88Build "RFM + LATERAL top product" report.
  14. Q89Build "customer journey via LATERAL chain".
  15. Q90Build "marketing funnel via LATERAL".
  16. Q91Build "return reason analysis via LATERAL".
  17. Q92Build "ticket SLA via LATERAL".
  18. Q93Build "call category mix via LATERAL".
  19. Q94Build "review sentiment via LATERAL".
  20. Q95Build "anomaly detector via LATERAL".
  21. Q96Build "outlier finder via LATERAL".
  22. Q97Build "cohort retention via LATERAL".
  23. Q98Build "churn predictor via LATERAL".
  24. Q99Build "executive dashboard via LATERAL chain".
  25. Q100Master: combine LATERAL + window + set ops + recursive in 1 query.

Production scenarios, optimisation

LATERAL + RECURSIVE

  1. Q1LATERAL with recursive CTE inside.
  2. Q2Recursive depth bounded by LATERAL.
  3. Q3LATERAL with recursive graph walk.
  4. Q4LATERAL with hierarchy traversal.
  5. Q5LATERAL with category tree walk.
  6. Q6LATERAL with manager chain.
  7. Q7LATERAL with friend-of-friend.
  8. Q8LATERAL with BOM (bill of materials).
  9. Q9LATERAL with dependency chain.
  10. Q10LATERAL with cycle detection.
  11. Q11LATERAL with shortest path.
  12. Q12LATERAL with date series generation.
  13. Q13LATERAL with calendar generation.
  14. Q14LATERAL with Fibonacci/recursive numerics.
  15. Q15LATERAL with rolling-window via recursive.
  16. Q16LATERAL with "next N events".
  17. Q17LATERAL with "prev N events".
  18. Q18LATERAL with "find all ancestors".
  19. Q19LATERAL with "find all descendants".
  20. Q20LATERAL with depth-limit recursive.
  21. Q21LATERAL with state-machine traversal.
  22. Q22LATERAL with state-history rollup.
  23. Q23LATERAL with version chain.
  24. Q24LATERAL with audit-trail traversal.
  25. Q25Master: LATERAL with recursive top-N.

LATERAL + WINDOW MEGA

  1. Q26LATERAL with PERCENTILE inside.
  2. Q27LATERAL with MODE inside.
  3. Q28LATERAL with cohort RFM scoring.
  4. Q29LATERAL with rolling 7-day.
  5. Q30LATERAL with rolling 30-day.
  6. Q31LATERAL with EWMA.
  7. Q32LATERAL with seasonality compare.
  8. Q33LATERAL with YoY growth.
  9. Q34LATERAL with regression slope.
  10. Q35LATERAL with correlation.
  11. Q36LATERAL with z-score.
  12. Q37LATERAL with outlier flagging.
  13. Q38LATERAL with anomaly detect.
  14. Q39LATERAL with running rank.
  15. Q40LATERAL with cumulative count.
  16. Q41LATERAL with cumulative distinct.
  17. Q42LATERAL with cumulative sum.
  18. Q43LATERAL with rolling avg.
  19. Q44LATERAL with rolling median.
  20. Q45LATERAL with first/last value.
  21. Q46LATERAL with LAG/LEAD chain.
  22. Q47LATERAL with PARTITION BY tier.
  23. Q48LATERAL with PARTITION BY region.
  24. Q49LATERAL with PARTITION BY month.
  25. Q50LATERAL with multi-PARTITION.

LATERAL CHAINS 4-5 LEVELS

  1. Q51customer -> last order -> first item -> product -> brand.
  2. Q52region -> top store -> top employee -> top ticket.
  3. Q53campaign -> top platform -> top customer -> top order.
  4. Q54warehouse -> top product -> top supplier -> latest shipment.
  5. Q55tier -> top customer -> top product -> top brand.
  6. Q56agent -> top ticket -> customer -> top order.
  7. Q57category -> top brand -> top product -> top buyer.
  8. Q58courier -> busiest day -> top customer -> top order.
  9. Q59dept -> top employee -> top ticket -> resolution time.
  10. Q60supplier -> top product -> top warehouse -> quantity.
  11. Q61month -> top region -> top store -> top customer.
  12. Q62day -> top hour -> top order -> top product.
  13. Q63brand -> top product -> top customer -> repeat purchases.
  14. Q64region -> top city -> top customer -> top product.
  15. Q65tier -> top member -> latest redemption -> product redeemed.
  16. Q66campaign -> top platform -> top creative -> top attribution.
  17. Q67session -> first url -> last url -> conversion event.
  18. Q68story -> orders -> items -> shipment -> delivery.
  19. Q69customer -> loyalty member -> tier -> next-tier delta.
  20. Q70order -> payment -> fraud-check -> status -> refund.
  21. Q71agent -> ticket -> comment -> resolution.
  22. Q72ticket -> customer -> past orders -> past reviews.
  23. Q73order -> return -> refund -> audit_log.
  24. Q74page_view -> session -> cart -> checkout -> order.
  25. Q75customer -> 5 events -> 5 derived metrics -> score.

PRODUCTION MEGA

  1. Q76Customer 360deg via LATERAL chain.
  2. Q77Product 360deg via LATERAL chain.
  3. Q78Region 360deg via LATERAL chain.
  4. Q79Campaign 360deg via LATERAL chain.
  5. Q80Store 360deg via LATERAL chain.
  6. Q81Brand 360deg via LATERAL chain.
  7. Q82Supplier 360deg via LATERAL chain.
  8. Q83Agent 360deg via LATERAL chain.
  9. Q84Courier 360deg via LATERAL chain.
  10. Q85Tier 360deg via LATERAL chain.
  11. Q86Warehouse 360deg via LATERAL chain.
  12. Q87Department 360deg via LATERAL chain.
  13. Q88Employee 360deg via LATERAL chain.
  14. Q89Category 360deg via LATERAL chain.
  15. Q90Channel 360deg via LATERAL chain.
  16. Q91Executive dashboard via LATERAL mega.
  17. Q92Operations dashboard via LATERAL.
  18. Q93Marketing dashboard via LATERAL.
  19. Q94Finance dashboard via LATERAL.
  20. Q95HR dashboard via LATERAL.
  21. Q96Cust service dashboard via LATERAL.
  22. Q97Sales dashboard via LATERAL.
  23. Q98Inventory dashboard via LATERAL.
  24. Q99Supply chain dashboard via LATERAL.
  25. Q100Master: LATERAL + recursive + window + set ops + DML in 1 mega query.