TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 10

Joins Part 2: 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

JOINS PART 2 - CONCEPTUAL

  1. Q1What is a SELF JOIN - and why do you need it?
  2. Q2Why are aliases mandatory in self-joins?
  3. Q3What is a FULL OUTER JOIN - when do both LEFT and RIGHT unmatched rows matter?
  4. Q4Compare INNER vs LEFT vs RIGHT vs FULL - which rows each preserves.
  5. Q5What is a CROSS JOIN - when do you actually want one?
  6. Q6Difference between UNION and UNION ALL - and why ALL is faster.
  7. Q7What is INTERSECT - when is it more readable than INNER JOIN?
  8. Q8What is EXCEPT - and how is it different from anti-join?
  9. Q9What rules must all queries in a UNION follow (column count, types)?
  10. Q10Why does UNION sort + dedupe but UNION ALL does not?
  11. Q11What is a LATERAL JOIN - give a one-line description.
  12. Q12Compare LATERAL vs correlated subquery - same idea, different syntax.
  13. Q13Can you SELF JOIN through a foreign key (manager -> employee)?
  14. Q14Why is FULL OUTER JOIN often used in data reconciliation reports?
  15. Q15What does CROSS JOIN with a numbers table give you?
  16. Q16Does ORDER BY apply to each branch of UNION, or the combined result?
  17. Q17Where do you put LIMIT in a UNION query - per branch or overall?
  18. Q18What is "set semantics" vs "bag semantics" in SQL?
  19. Q19Can NULLs match in INTERSECT?
  20. Q20Why is FULL OUTER JOIN rarely used in OLTP queries?
  21. Q21How is a SELF JOIN typically used to find pairs (e.g., "employees in the same department")?
  22. Q22Compare CROSS JOIN vs FROM a, b - are they the same?
  23. Q23Why is UNION often slower than a single SELECT with OR conditions?
  24. Q24What is "generate_series" - and why does it pair so well with CROSS JOIN?
  25. Q25When does EXCEPT ALL behave differently from EXCEPT?

SELF JOIN BASICS

  1. Q26Pairs of employees in the same dim_department (e1.dept_id = e2.dept_id, e1.id < e2.id).
  2. Q27Pairs of customers in the same city.
  3. Q28Pairs of orders placed by the same customer on the same day.
  4. Q29Pairs of products with the same brand_id.
  5. Q30Pairs of stores in the same region_id.
  6. Q31List employees with the same role at different stores.
  7. Q32Find customers who share the same email domain.
  8. Q33List products in the same category with the same supplier_id.
  9. Q34Find tickets with the same priority + same status - show paired ticket_ids.
  10. Q35Pairs of campaigns running on the same platform.
  11. Q36Pairs of pay_slips for the same employee but different salary_month.
  12. Q37Pairs of orders by the same customer but different store_id.
  13. Q38Pairs of reviews by the same customer on different products.
  14. Q39Pairs of products with the same price_band (CASE-based).
  15. Q40Pairs of warehouses in the same region (no region_id on warehouses? join via stores).
  16. Q41SELF JOIN on customers: pairs of customers in same tier + same city.
  17. Q42SELF JOIN: pairs of brands with same category_id.
  18. Q43SELF JOIN: orders placed within 1 hour of each other by the same customer.
  19. Q44SELF JOIN: shipments using the same courier_name on the same date.
  20. Q45SELF JOIN: web_events.page_views in the same session_id.
  21. Q46SELF JOIN: employees with same hire_date.
  22. Q47SELF JOIN: pay_slips with same gross_salary but different employee_id.
  23. Q48SELF JOIN: tickets created by same customer for different products.
  24. Q49SELF JOIN: products with same supplier_id but different brand_id.
  25. Q50SELF JOIN: campaigns with same budget but different platform.

FULL OUTER + CROSS JOIN

  1. Q51FULL OUTER JOIN customers and loyalty.members - show customers without loyalty AND members orphaned.
  2. Q52FULL OUTER JOIN orders and shipments - orders without shipments + shipments without orders.
  3. Q53FULL OUTER JOIN employees and pay_slips - employees never paid + pay_slips with no employee.
  4. Q54FULL OUTER JOIN dim_region and stores - regions without stores + stores in no region.
  5. Q55FULL OUTER JOIN dim_category and dim_brand - empty categories + brands with no category.
  6. Q56FULL OUTER JOIN products and order_items - never-sold products + items pointing to deleted product.
  7. Q57FULL OUTER JOIN warehouses and inventory_snapshots - empty warehouses + orphan snapshots.
  8. Q58FULL OUTER JOIN tickets and support.agents - unassigned tickets + agents with no tickets.
  9. Q59FULL OUTER JOIN ad_campaigns and ads_spend - campaigns with no spend + spend with no campaign.
  10. Q60FULL OUTER JOIN reviews and orders - reviews not linked to order + orders never reviewed.
  11. Q61CROSS JOIN dim_region x tiers - all (region, tier) combinations (potential customer segments).
  12. Q62CROSS JOIN months x dim_category - all (month, category) buckets for revenue grid.
  13. Q63CROSS JOIN device_type x os_list - all browser/device combinations.
  14. Q64CROSS JOIN platforms x months - all reporting cells for ads_spend.
  15. Q65CROSS JOIN dim_region x dim_brand - all marketxbrand combinations.
  16. Q66CROSS JOIN generate_series(1, 12) x stores - month-by-store grid.
  17. Q67CROSS JOIN order statuses x stores - every (status, store) bucket.
  18. Q68CROSS JOIN ticket_priority x ticket_status - all priority-status combos.
  19. Q69CROSS JOIN regions x campaigns - all targeting combinations.
  20. Q70CROSS JOIN years x dim_department - year-by-dept HR grid.
  21. Q71Use generate_series to build a date dimension.
  22. Q72Build a calendar table (one row per day from 2024-01-01 to today) using generate_series.
  23. Q73CROSS JOIN dim_category x tiers - used for cohort analysis grids.
  24. Q74CROSS JOIN courier list x months - to see which courier-month combos had zero shipments.
  25. Q75Single row x CROSS JOIN customers - turn a single row into N copies (uncommon but valid).

UNION / INTERSECT / EXCEPT

  1. Q76UNION ALL: combine customer emails and employee emails into one list (with source label).
  2. Q77UNION ALL: all customer interactions - orders + reviews + tickets - into a single timeline.
  3. Q78UNION: distinct cities from customers + stores combined.
  4. Q79UNION ALL: combine domestic and international orders (if there were two tables). Use status='International' as proxy split.
  5. Q80UNION ALL: shipment events + delivery events into one log.
  6. Q81UNION ALL: agent activity from tickets + calls (preview for Topic 11).
  7. Q82INTERSECT: customers who BOTH placed an order AND wrote a review.
  8. Q83INTERSECT: customers in BOTH loyalty.members AND placed >0 orders.
  9. Q84INTERSECT: products that appear in BOTH order_items AND reviews.
  10. Q85INTERSECT: stores with BOTH employees AND orders.
  11. Q86EXCEPT: customers who placed orders but are NOT in loyalty.members.
  12. Q87EXCEPT: products with order_items but NO reviews.
  13. Q88EXCEPT: employees with no pay_slip records.
  14. Q89EXCEPT: stores with employees but no orders.
  15. Q90EXCEPT: brands with products but no order_items linked.
  16. Q91UNION ALL: top 5 highest-revenue + top 5 lowest-revenue products into one report.
  17. Q92UNION ALL: count of orders this month + count last month + count YTD as 3 labelled rows.
  18. Q93UNION ALL: per-region revenue + a grand-total row.
  19. Q94UNION ALL: combine three priority lists (Critical, High, Medium) with a priority_label column.
  20. Q95UNION ALL: registered customers + guest checkouts (where cust_id IS NULL) into one list.
  21. Q96INTERSECT ALL vs INTERSECT - show difference using a duplicate-heavy column.
  22. Q97UNION with ORDER BY at end - sort the combined result.
  23. Q98UNION ALL + LIMIT - get top 3 from query A + top 3 from query B as 6 rows total.
  24. Q99UNION ALL across three time buckets - last_week, last_month, last_quarter counts.
  25. Q100UNION ALL: tickets + reviews + calls, all reduced to (customer_id, source, created_at).

Combined ideas, multi-step thinking

SET OPS + SELF JOIN - CONCEPTUAL DEEPER

  1. Q1Why is "self-join + GROUP BY" usually slower than a window function (preview)?
  2. Q2How does a self-join scale with N - and when do you avoid it?
  3. Q3When does FULL OUTER JOIN produce duplicate rows - and how do you collapse them with COALESCE?
  4. Q4Why is CROSS JOIN with generate_series the standard way to "fill in missing dates"?
  5. Q5Compare UNION ALL vs JOIN - when can the same answer be expressed either way?
  6. Q6Why does UNION (without ALL) require a SORT step - and how does that affect plans?
  7. Q7Compare EXCEPT vs NOT IN vs NOT EXISTS - three flavors of anti-join.
  8. Q8What's the catch with INTERSECT and NULL columns?
  9. Q9Why is FULL OUTER JOIN often the "reconciliation report" tool of choice?
  10. Q10Explain what LATERAL JOIN does - and how it differs from a normal subquery.
  11. Q11Why does LATERAL let you reference outer-row columns inside the subquery?
  12. Q12When does the planner choose Hash Join vs Merge Join vs Nested Loop for self-joins?
  13. Q13Compare LEFT JOIN LATERAL vs INNER JOIN LATERAL - when does each row get dropped?
  14. Q14Why is SELF JOIN over a tiny table OK but a Cartesian on huge tables disastrous?
  15. Q15Explain why ORDER BY in a UNION must appear ONLY at the end.
  16. Q16What does GROUP BY 1 do in a UNION - does it apply to all branches?
  17. Q17Why does generate_series often appear inside CROSS JOIN LATERAL?
  18. Q18Compare CROSS JOIN unnest(array) vs LATERAL unnest(array).
  19. Q19Why does UNION ALL preserve duplicates and how is that useful for "stacked timelines"?
  20. Q20Walk through how SELF JOIN can compute "next event per row" before window functions.
  21. Q21Why does FULL OUTER JOIN often require COALESCE on JOIN keys?
  22. Q22Compare INTERSECT vs INNER JOIN ON all-columns - when are they equivalent?
  23. Q23Why is EXCEPT sometimes preferred over LEFT JOIN ... IS NULL for "find missing" reports?
  24. Q24What is "set semantics drift" - when a UNION quietly drops rows you wanted to keep?
  25. Q25Why is FULL OUTER JOIN the standard "audit table mismatch" tool?

SELF JOIN - DEEPER PATTERNS

  1. Q26For each order, find the customer's PREVIOUS order (self-join on cust_id + ORDER BY).
  2. Q27For each shipment, find the next shipment by the same courier_name.
  3. Q28For each pay_slip, find the same employee's prior month pay_slip.
  4. Q29For each ticket, find prior ticket by same customer (same cust_id).
  5. Q30For each call, find prior call by same agent_id.
  6. Q31For each review, find prior review by same customer for same product_id.
  7. Q32For each page_view, find prior page_view in same session.
  8. Q33Pairs of customers in same city, same tier - show how many such pairs exist.
  9. Q34Find pairs of orders by same customer where time-difference < 1 hour.
  10. Q35Self-join orders with itself: COUNT of repeat-day customers.
  11. Q36For each campaign, find another campaign of same platform with higher budget.
  12. Q37For each product, find another product of same brand with higher price.
  13. Q38For each employee, find another employee of same role with higher salary.
  14. Q39For each store, find another store in same region with higher order count.
  15. Q40For each warehouse, find another warehouse in same region with higher quantity_on_hand.
  16. Q41SELF JOIN employees: find employees whose hire_date is exactly N days after another employee.
  17. Q42SELF JOIN orders: customers with 2+ orders within 7 days.
  18. Q43SELF JOIN tickets: customers with 2+ tickets in same week.
  19. Q44SELF JOIN reviews: customers who wrote 2 reviews on different products with same rating.
  20. Q45SELF JOIN inventory: products whose quantity_on_hand at one warehouse = at another.
  21. Q46SELF JOIN customers: pairs from same city with different tiers (cross-tier in same city).
  22. Q47SELF JOIN orders: chains of 3+ same-day orders by same customer.
  23. Q48SELF JOIN page_views: sessions where two page_views had same url.
  24. Q49SELF JOIN tickets: ticket pairs from same customer where one is Critical and another Low.
  25. Q50SELF JOIN products: products with same supplier_id and same category.

FULL OUTER + CROSS JOIN GRIDS

  1. Q51Full reconciliation: customers vs orders - show 4 buckets (both / customer only / order only / never).
  2. Q52Full reconciliation: employees vs pay_slips - find pay_slips orphaned to deleted employees.
  3. Q53Full reconciliation: orders vs payments - orders without payments + payments without orders.
  4. Q54Full reconciliation: orders vs shipments - count of each side mismatch.
  5. Q55Full reconciliation: loyalty.members vs customers - orphan members?
  6. Q56Per region x month grid: SUM revenue (CROSS JOIN regions x month_series LEFT JOIN orders).
  7. Q57Per platform x month grid: SUM ad spend (no zeros missing).
  8. Q58Per ticket priority x month grid: COUNT tickets (zero-cell rows preserved).
  9. Q59Per device_type x week grid: COUNT page_views.
  10. Q60Per category x tier grid: COUNT distinct customers.
  11. Q61Per courier x month grid: COUNT shipments.
  12. Q62Per warehouse x day grid (last 7 days): quantity_on_hand.
  13. Q63Per dim_department x month grid: total payroll.
  14. Q64Per dim_region x dim_category grid: SUM revenue.
  15. Q65Per status x month grid: order counts (all status x month combos shown).
  16. Q66Use generate_series(2024-01-01, 2026-02-26, '1 month') + CROSS JOIN regions to build monthxregion target frame.
  17. Q67CROSS JOIN tiers x dim_department to get "tier presence" matrix.
  18. Q68CROSS JOIN dim_brand x dim_region: revenue matrix.
  19. Q69CROSS JOIN courier list x order_status: shipment frequencies.
  20. Q70CROSS JOIN log_level x service_name: counts.
  21. Q71Build a calendar dimension (generate_series for dates) + week_of_year + is_weekend column.
  22. Q72CROSS JOIN customers x campaigns to simulate "all campaigns reachable per customer" cell.
  23. Q73CROSS JOIN dim_region x tiers x months - 3-dim grid for executive dashboard.
  24. Q74CROSS JOIN every supplier x product to find supplier-product gaps.
  25. Q75CROSS JOIN orders x generate_series - explode 1 order row into N (e.g., per installment).

UNION / INTERSECT / EXCEPT / LATERAL

  1. Q76UNION ALL: all touchpoints per customer (orders, reviews, tickets, calls) into one event stream.
  2. Q77UNION ALL + GROUP BY: count of distinct customers across orders + reviews + tickets (deduped).
  3. Q78UNION ALL: combined revenue stream from orders + ads_spend (sign-flip for spend).
  4. Q79UNION ALL: combine top-5 best categories + bottom-5 worst categories with a label column.
  5. Q80INTERSECT: customers who placed orders AND wrote reviews AND opened tickets.
  6. Q81EXCEPT: products in inventory_snapshots but never in order_items.
  7. Q82EXCEPT: customers ordered but never reviewed.
  8. Q83EXCEPT: employees in stores.employees but not in pay_slips.
  9. Q84INTERSECT: stores having BOTH employees AND orders AND inventory_snapshots.
  10. Q85EXCEPT ALL vs EXCEPT: show difference on a duplicated cust_id list.
  11. Q86UNION ALL: monthly revenue + monthly cost + monthly profit as separate rows with metric label.
  12. Q87UNION ALL: registered + guest checkouts into uniform customer_id list.
  13. Q88UNION ALL + ROLLUP-like: per-region totals + grand total via UNION.
  14. Q89UNION ALL: rolling 12-week window built from 12 SELECTs (preview window functions).
  15. Q90UNION ALL: ticket history + call history into a "support contact timeline" per customer.
  16. Q91LATERAL: per customer, latest 3 orders (LATERAL with LIMIT 3 + ORDER BY).
  17. Q92LATERAL: per product, latest review (LATERAL with LIMIT 1).
  18. Q93LATERAL: per region, top 5 stores by revenue.
  19. Q94LATERAL: per category, top 3 products by units sold.
  20. Q95LATERAL with generate_series: explode orders into N installment rows.
  21. Q96LATERAL: per customer, count distinct products purchased.
  22. Q97LATERAL: per store, employee with highest salary.
  23. Q98LATERAL: per courier_name, last 5 shipments.
  24. Q99UNION ALL across 4 schemas (orders, reviews, tickets, calls) producing a unified "customer activity" denormalized feed.
  25. Q100EXCEPT + LATERAL combined: find customers with orders but no reviews of those exact products.

Interview grade, edge cases

ADVANCED JOIN PART 2 - CONCEPTUAL

  1. Q1Walk through how LATERAL is planned: re-evaluated per outer row.
  2. Q2Compare LATERAL LIMIT 1 vs DISTINCT ON for "latest per group".
  3. Q3When is FULL OUTER JOIN's COALESCE(left.k, right.k) required vs optional?
  4. Q4How does CROSS JOIN LATERAL generate_series produce "N rows per outer row" - explosion pattern.
  5. Q5Compare self-join with window function performance (preview Topic 16).
  6. Q6Explain how UNION ALL is a "stack vertically" while JOIN is "merge horizontally".
  7. Q7When does INTERSECT use a Hash Aggregate vs Sort + Merge?
  8. Q8Why is EXCEPT sometimes a better choice than NOT EXISTS for whole-row diffs?
  9. Q9Explain "relational division" - every X who has ALL Ys - give a SQL recipe.
  10. Q10Why are FULL OUTER JOINs the backbone of reconciliation (find rows missing on either side)?
  11. Q11Walk through a 4-bucket reconciliation: both / only-left / only-right / neither.
  12. Q12Compare a multi-condition self-join (a.x=b.x AND a.y<b.y) with a correlated subquery.
  13. Q13Why does UNION ALL preserve duplicates while UNION removes them - and the cost difference.
  14. Q14How do you build a "next event per row" with a self-join + NOT EXISTS gap check.
  15. Q15Why do CROSS JOIN reports use ARRAY_AGG to build grids?
  16. Q16Compare CROSS JOIN small x small x small vs CROSS JOIN huge x huge - performance cliff.
  17. Q17Walk through "dense reporting" pattern: CROSS JOIN time x dim LEFT JOIN facts COALESCE 0.
  18. Q18Why is LATERAL essential for "for each parent, get a subset of children with ORDER BY/LIMIT"?
  19. Q19When does a SELF JOIN with composite key match patterns (a.x=b.x AND a.y<b.y)?
  20. Q20Why does UNION drop information vs UNION ALL - and when is that desirable?
  21. Q21Explain how INTERSECT can replace INNER JOIN + DISTINCT on all columns.
  22. Q22Compare CROSS JOIN unnest(array) vs unnest in SELECT.
  23. Q23What is "PIVOT" - how do you simulate it with FILTER + GROUP BY?
  24. Q24What is "UNPIVOT" - how do you simulate it with UNION ALL?
  25. Q25Walk through a "session attribution" query that requires LATERAL + set ops.

LATERAL DEEP

  1. Q26LATERAL: per customer, latest 5 orders.
  2. Q27LATERAL: per product, latest 3 reviews (with rating).
  3. Q28LATERAL: per region, top 5 stores by revenue.
  4. Q29LATERAL: per category, top 3 products by units sold.
  5. Q30LATERAL: per agent, latest 5 tickets resolved.
  6. Q31LATERAL: per platform, top 3 campaigns by spend.
  7. Q32LATERAL: per warehouse, oldest snapshot (product + date).
  8. Q33LATERAL: per supplier, 3 most-recent shipments.
  9. Q34LATERAL: per call_reason, longest call.
  10. Q35LATERAL: per dept, highest-paid employee.
  11. Q36LATERAL: explode order into installments (generate_series 1..3).
  12. Q37LATERAL: explode shipment into "shipped -> in-transit -> delivered" steps.
  13. Q38LATERAL: per customer, derive 12 monthly buckets (generate_series + LATERAL).
  14. Q39LATERAL: per product, count of distinct buyers.
  15. Q40LATERAL: per ticket, the same customer's PREVIOUS ticket.
  16. Q41LATERAL + aggregation: per customer, JSON of all orders.
  17. Q42LATERAL: per session, the page clicked just before checkout.
  18. Q43LATERAL chain: per customer, last order -> that order's first item -> that item's product.
  19. Q44LATERAL with WHERE that references outer row.
  20. Q45LEFT JOIN LATERAL - keep outer row when subquery is empty.
  21. Q46LATERAL + LIMIT 0 (no rows) - INNER excludes; LEFT keeps with NULLs.
  22. Q47LATERAL with EXISTS - short-circuit detect.
  23. Q48LATERAL with generate_series + interval - date-bucket per parent.
  24. Q49LATERAL on a materialized view: top 3 products per category.
  25. Q50LATERAL: per order, its top 3 line items by net_amount.

SELF-JOIN ADVANCED

  1. Q51Self-join: prev/next order per customer (and gap days).
  2. Q52Self-join: detect 2+ same-day orders per customer.
  3. Q53Self-join: "repeat-buyer pattern" - same product, same customer, > 30 days apart.
  4. Q54Self-join: each ticket to the same customer's NEXT ticket.
  5. Q55Self-join: inventory "shortage pair" - same product low-stock at two warehouses within 7 days.
  6. Q56Self-join: customer signup -> first purchase delay (anchor + first event).
  7. Q57Self-join: same-brand product pairs by price (cheaper vs pricier).
  8. Q58Self-join: campaign overlap - two campaigns running same dates.
  9. Q59Self-join: returns following purchases within 7 days.
  10. Q60Self-join: customers with 2+ open tickets.
  11. Q61Self-join: employee pairs working at the same store.
  12. Q62Multi-join: product pairs in the same category.
  13. Q63Self-join: employees at one store who joined within 30 days of each other.
  14. Q64Self-join: multiple returns against the same order.
  15. Q65Set-based: new customers per registration month (cumulative idea).
  16. Q66Set-based: page-view path length per session.
  17. Q67Aggregation: warehouses and how many products they track.
  18. Q68Aggregation: suppliers and their shipment counts.
  19. Q69Aggregation: headcount per role per store.
  20. Q70Co-purchase self-join: customers who bought the same products as customer 1.
  21. Q71Self-join: rank orders by value WITHIN each customer (before window functions).
  22. Q72Use SELF JOIN to detect duplicate emails.
  23. Q73Use SELF JOIN to detect ticket subject duplicates.
  24. Q74Use SELF JOIN to detect inventory mismatches across warehouses.
  25. Q75Use SELF JOIN to find "twin orders" - same cust, same amount, same day.

SET OPS + RECONCILIATION

  1. Q76FULL OUTER reconciliation: orders.cust_id vs customers.customer_id.
  2. Q77FULL OUTER: products in inventory vs products ever sold - find drift.
  3. Q78UNION ALL stacked: events from orders + tickets + reviews + calls.
  4. Q79INTERSECT: customers in BOTH high-spend + active-reviewer cohorts.
  5. Q80EXCEPT: customers who ordered but never reviewed.
  6. Q81UNION ALL: "customer churn analysis" - combine multiple definitions of churn.
  7. Q82UNION ALL + grand total (ROLLUP-like).
  8. Q83INTERSECT: customers with a Delivered order AND a 2025 order.
  9. Q84EXCEPT ALL: rows in old set but not new (with multiplicity).
  10. Q85Set-op + CTE: differences between two computed reports.
  11. Q86Full reconciliation report: 4-bucket layout (both / only-orders / only-reviews / neither).
  12. Q87Multi-source dashboard: monthly revenue + ad cost in one UNION ALL output.
  13. Q88UNION ALL + DENSE_RANK (preview Topic 16).
  14. Q89Detect drift: customers with 2025 orders but none in 2024 (EXCEPT).
  15. Q90Verify overlap: customers present in BOTH orders and payments (INTERSECT).
  16. Q91Catch unmatched rows: customers who never placed an order (EXCEPT).
  17. Q92PIVOT customers by tier using FILTER.
  18. Q93UNPIVOT order item amount columns into rows.
  19. Q94INTERSECT: customers who ordered from BOTH store 1 and store 2.
  20. Q95INTERSECT three lifecycle stages.
  21. Q96EXCEPT to find new-only campaigns vs last month.
  22. Q97Build a "delta": customers (id <= 100) with no orders.
  23. Q98Build a "diff": products in inventory but never sold.
  24. Q99Combine LATERAL + UNION ALL: per customer, top 1 from each of 4 sources.
  25. Q100Customer 360deg "all interactions" feed: UNION ALL across schemas, ordered by timestamp.

Production scenarios, optimisation

RECURSIVE DEEP

  1. Q1Employee hierarchy with level + path.
  2. Q2Category tree (parent -> children).
  3. Q3Reporting hierarchy: CEO + reports + sub-reports.
  4. Q4Find depth of deepest sub-tree.
  5. Q5Find longest path from any node to a leaf.
  6. Q6Cycle detection with CYCLE clause (PG14+).
  7. Q7BFS vs DFS in recursive CTE.
  8. Q8Friend-of-friend at depth 3.
  9. Q9Product dependency graph (bill of materials).
  10. Q10Shortest path between two nodes (mini Dijkstra).
  11. Q11Count of descendants per node.
  12. Q12Roll-up totals through hierarchy.
  13. Q13Sum of subtree per node.
  14. Q14Order chain: refund -> original -> previous refund.
  15. Q15Web session reconstruction.
  16. Q16Date series via RECURSIVE (alternative to generate_series).
  17. Q17Fibonacci sequence (toy demo).
  18. Q18Recursive split: explode comma-separated list.
  19. Q19Cycle in supplier graph.
  20. Q20Cycle in friend graph.
  21. Q21Detect orphaned nodes.
  22. Q22Compute "depth from root" for every node.
  23. Q23Mark all nodes within N hops of a starting node.
  24. Q24Tree balance check.
  25. Q25Build a "category breadcrumb" string for every product.

LATERAL PRODUCTION

  1. Q26Per customer, latest order + first order + total spend.
  2. Q27Per customer, list 3 most-recent reviews + their products.
  3. Q28Per ticket, top 3 comments by date.
  4. Q29Per agent, count tickets by priority (via LATERAL).
  5. Q30Per region, top 5 products by revenue.
  6. Q31Per warehouse, latest snapshot of every product.
  7. Q32Per courier, average delivery + last 5 shipments.
  8. Q33Per supplier, sum shipped + last shipment date.
  9. Q34Per campaign, attribution + spend + ROI in one row.
  10. Q35Per platform, top 3 campaigns.
  11. Q36Per category, top 3 products + their brand.
  12. Q37Per dept, top earner + total payroll.
  13. Q38Per store, employee with most tickets + avg salary.
  14. Q39Per customer, page_views in last session.
  15. Q40Per call_reason, longest call + its transcript.
  16. Q41LATERAL explode JSONB array.
  17. Q42LATERAL with generate_series for dates.
  18. Q43LATERAL with subquery returning multiple cols.
  19. Q44LATERAL with EXISTS short-circuit.
  20. Q45Per customer, "next purchase" prediction (preview ML).
  21. Q46Per product, "buyer pattern" (top 5 customers).
  22. Q47Per region, "growth rate" via LATERAL on month series.
  23. Q48Per store, "peak hour" detection.
  24. Q49Chain LATERAL -> LATERAL -> LATERAL (3-level).
  25. Q50LATERAL with aggregation per parent row.

SET OPERATIONS PRODUCTION

  1. Q51Customer interactions timeline (UNION ALL across 6 sources).
  2. Q52Differences between two snapshots (EXCEPT both ways).
  3. Q53INTERSECT high-value cohorts (3 definitions of "VIP").
  4. Q54UNION ALL with metric label for dashboards.
  5. Q55UNION ALL across schemas.
  6. Q56Cross-database UNION ALL via FDW.
  7. Q57UNION ALL with GROUP BY + aggregation.
  8. Q58UNION ALL with window function on result.
  9. Q59UNION ALL of historical and current.
  10. Q60UNION ALL of partitioned children explicitly.
  11. Q61INTERSECT ALL preserving duplicates.
  12. Q62EXCEPT ALL preserving duplicates.
  13. Q63Combine LEFT JOIN + UNION ALL for "everyone but matches".
  14. Q64Build "audit reconciliation" via FULL OUTER + COALESCE.
  15. Q65Build "data drift" via EXCEPT both ways.
  16. Q66Build "churn definition matrix" via INTERSECT.
  17. Q67Build "lifecycle stage" via UNION ALL of stage queries.
  18. Q68Build "attribution" via UNION ALL with weight.
  19. Q69Build "customer interaction graph" via UNION ALL of edges.
  20. Q70Build "product co-purchase" via SELF JOIN + UNION.
  21. Q71Build "promotion overlap" via FULL OUTER.
  22. Q72Build "tax audit" via UNION ALL with categorization.
  23. Q73Build "refund audit" via UNION ALL.
  24. Q74Combine set ops + window for ranking.
  25. Q75Combine set ops + recursive for graph.

MIXED MEGA-PATTERNS

  1. Q76Customer activity stream -> first/last/most events.
  2. Q77Product lifecycle: from launch to discontinue.
  3. Q78Marketing funnel with multi-touch attribution.
  4. Q79RFM + cohort + LATERAL "next purchase".
  5. Q80RetailMart pulse: 50-metric exec dashboard.
  6. Q81Inventory rebalancing recommendation.
  7. Q82Supplier scorecard.
  8. Q83Employee 360deg with ranking within dept.
  9. Q84Customer journey reconstruction.
  10. Q85Ad campaign ROI deep dive.
  11. Q86Geo expansion plan: per-city stats + scoring.
  12. Q87Loyalty program ROI.
  13. Q88Returns analysis with root-cause flags.
  14. Q89SLA breach root-cause analysis.
  15. Q90Fraud detection report.
  16. Q91Stockout risk forecast.
  17. Q92Top 100 "at-risk" customers (composite score).
  18. Q93Top 100 "growth" customers.
  19. Q94Tier upgrade simulation.
  20. Q95Pricing optimization preview.
  21. Q96NPS deep dive by 5 dimensions.
  22. Q97Churn deep dive (root causes).
  23. Q98Cross-sell recommendations.
  24. Q99Up-sell recommendations.
  25. Q100Single mega-query: every interesting analytics result in 1 row per customer.