TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 11

Subqueries Part 1: 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

SUBQUERIES - CONCEPTUAL

  1. Q1What is a subquery - and how is it different from a CTE?
  2. Q2What does "scalar subquery" mean - single row + single column.
  3. Q3Compare IN (subquery) vs EXISTS (subquery).
  4. Q4Why does NOT IN break when the subquery returns NULL?
  5. Q5What is a "correlated subquery"?
  6. Q6Compare = ANY(subquery) vs IN (subquery).
  7. Q7Compare > ALL(subquery) vs > (SELECT MAX...).
  8. Q8Can a subquery in SELECT return multiple rows?
  9. Q9Can a subquery in WHERE return multiple rows?
  10. Q10Compare WHERE col = (subquery) vs WHERE col IN (subquery).
  11. Q11What does (SELECT MAX(net_total) FROM ...) return - type?
  12. Q12When is a subquery in FROM (derived table) required?
  13. Q13Why must a subquery in FROM have an alias?
  14. Q14Compare scalar subquery in SELECT vs JOIN - same result, different style.
  15. Q15What is "semi-join" - and how does EXISTS implement it?
  16. Q16What is "anti-join" - and how does NOT EXISTS implement it?
  17. Q17Why does NOT EXISTS NOT have the NULL problem of NOT IN?
  18. Q18Compare HAVING subquery vs WHERE subquery.
  19. Q19Can a subquery reference its outer query's columns? (Yes if correlated.)
  20. Q20Compare correlated subquery vs LEFT JOIN.
  21. Q21Performance: when does the planner rewrite a subquery to a join?
  22. Q22What is the "subquery cache" in some engines (not in Postgres).
  23. Q23Compare uncorrelated subquery (executes once) vs correlated (executes per row).
  24. Q24Why is "subquery returns more than one row" a common error?
  25. Q25Compare subquery in SELECT vs subquery in WHERE for performance.

IN / NOT IN / EXISTS / NOT EXISTS

  1. Q26Find orders with cust_id IN (top 10 customers by spend).
  2. Q27Find customers WHERE customer_id IN (SELECT cust_id FROM sales.orders).
  3. Q28Find customers WHERE customer_id NOT IN (loyalty.members).
  4. Q29Find products WHERE product_id IN (sales.order_items).
  5. Q30Find tickets WHERE cust_id IN (high-spend customers).
  6. Q31Find employees WHERE store_id IN (stores in 'Mumbai').
  7. Q32Find orders WHERE order_status IN ('Delivered','Cancelled','Returned').
  8. Q33Find products WHERE brand_id IN (brands of category 'Electronics').
  9. Q34Find shipments WHERE order_id IN (orders by Gold-tier customers).
  10. Q35Find pay_slips WHERE employee_id IN (employees in 'Sales' dept).
  11. Q36EXISTS: customers who placed orders.
  12. Q37EXISTS: products with reviews.
  13. Q38EXISTS: stores with employees.
  14. Q39EXISTS: warehouses with snapshots.
  15. Q40EXISTS: campaigns with spend.
  16. Q41NOT EXISTS: customers without orders.
  17. Q42NOT EXISTS: products without reviews.
  18. Q43NOT EXISTS: brands without products.
  19. Q44NOT EXISTS: employees without pay_slips.
  20. Q45NOT EXISTS: orders without shipments.
  21. Q46Combine EXISTS + filter: customers who placed > 5 orders.
  22. Q47Combine NOT EXISTS + filter: products with no 5-star reviews.
  23. Q48Compare LEFT JOIN IS NULL vs NOT EXISTS for anti-join.
  24. Q49Use EXISTS inside SELECT (as boolean column).
  25. Q50Use EXISTS in CASE expression.

ANY / SOME / ALL

  1. Q51Find products WHERE price > ANY(SELECT price FROM 'electronics').
  2. Q52Find orders WHERE net_total > ALL(SELECT net_total FROM orders WHERE store_id = 1).
  3. Q53Find customers WHERE tier_id = ANY(SELECT tier_id FROM loyalty.tiers WHERE points > 1000).
  4. Q54Find employees WHERE salary > ALL(SELECT salary FROM stores.employees WHERE role = 'Sales').
  5. Q55Find reviews WHERE rating < ANY(SELECT rating FROM customers.reviews WHERE product_id = 1).
  6. Q56Find tickets WHERE priority = ANY(ARRAY['Critical','High']).
  7. Q57Find products WHERE brand_id <> ALL(SELECT brand_id FROM dim_brand WHERE category_id = 1).
  8. Q58Find orders WHERE net_total = (SELECT MAX(net_total) FROM sales.orders).
  9. Q59Find products WHERE price = (SELECT MIN(price) FROM products.products).
  10. Q60Find employees WHERE salary = (SELECT MAX(salary) FROM stores.employees WHERE dept_id = 1).
  11. Q61Find orders WHERE net_total > (SELECT AVG(net_total) FROM sales.orders).
  12. Q62Find loyalty members WHERE tier_id > (SELECT MIN(tier_id) FROM loyalty.members).
  13. Q63Find pay_slips WHERE gross_salary > (SELECT AVG(gross_salary) FROM payroll.pay_slips).
  14. Q64Find shipments WHERE delivered_date IS NULL AND shipped_date < (SELECT MIN(shipped_date) + INTERVAL '7 days' FROM ...).
  15. Q65Find products WHERE supplier_id = ANY(SELECT supplier_id FROM products.suppliers WHERE city = 'Mumbai').
  16. Q66Compare = ANY vs IN - same result.
  17. Q67Compare <> ALL vs NOT IN - same result.
  18. Q68> ANY = greater than at least one.
  19. Q69> ALL = greater than every.
  20. Q70< ANY = less than at least one.
  21. Q71< ALL = less than every.
  22. Q72= SOME = synonym for = ANY.
  23. Q73Combine ANY with array literal: x = ANY(ARRAY[1,2,3]).
  24. Q74ANY with subquery returning 0 rows - what happens.
  25. Q75ALL with subquery returning 0 rows - what happens (TRUE!).

SCALAR SUBQUERIES IN SELECT

  1. Q76For each order, show net_total + (SELECT AVG(net_total) FROM sales.orders) AS overall_avg.
  2. Q77For each customer, show (SELECT COUNT(*) FROM sales.orders WHERE cust_id = c.customer_id) AS orders.
  3. Q78For each product, show (SELECT AVG(rating) FROM customers.reviews WHERE product_id = p.product_id) AS avg_rating.
  4. Q79For each store, show (SELECT COUNT(*) FROM stores.employees WHERE store_id = s.store_id) AS emp_count.
  5. Q80For each campaign, show (SELECT SUM(amount) FROM marketing.ads_spend WHERE campaign_id = c.campaign_id) AS total_spend.
  6. Q81For each region, show count of stores via scalar subquery.
  7. Q82For each customer, show last order date.
  8. Q83For each product, show last review date.
  9. Q84For each employee, show last pay_slip month.
  10. Q85For each ticket, show count of comments via scalar subquery.
  11. Q86For each customer, show their tier_name via scalar subquery JOIN.
  12. Q87For each order, show the customer's full_name.
  13. Q88For each order, show the store's region_name.
  14. Q89For each ticket, show the agent's full_name.
  15. Q90For each shipment, show the order's customer email.
  16. Q91Use scalar subquery in WHERE: WHERE net_total > (subq).
  17. Q92Use scalar subquery in HAVING: HAVING SUM(amt) > (subq).
  18. Q93Use scalar subquery in ORDER BY: ORDER BY (SELECT ...).
  19. Q94Use scalar subquery in CASE: CASE WHEN x > (subq) THEN ... END.
  20. Q95Multiple scalar subqueries in one SELECT (5 columns).
  21. Q96Show "% of total revenue" per region using scalar denominator.
  22. Q97Show "rank among peers" via correlated scalar subquery.
  23. Q98Show "is_above_average" boolean column.
  24. Q99Show "how far from max" - (max - this).
  25. Q100Show "10 customer KPIs" all as scalar subqueries in one SELECT.

Combined ideas, multi-step thinking

SUBQUERIES DEEPER - CONCEPTUAL

  1. Q1Why is a correlated subquery slower than an uncorrelated one?
  2. Q2When does the planner rewrite a subquery to a JOIN?
  3. Q3Compare derived table (FROM subquery) vs CTE.
  4. Q4Why is "subquery returns more than one row" common with =?
  5. Q5Explain LIMIT 1 inside a subquery - when needed.
  6. Q6Why use ORDER BY inside subquery with LIMIT 1?
  7. Q7Compare uncorrelated EXISTS (constant) vs correlated EXISTS.
  8. Q8Compare WHERE x IN (...) vs WHERE x = ANY(VALUES (...)).
  9. Q9Compare WHERE x IN (subq) vs JOIN ... - same result, different plan.
  10. Q10When does NOT IN return 0 rows surprisingly?
  11. Q11Why is NOT EXISTS NULL-safe?
  12. Q12Compare SELECT scalar vs JOIN aggregate - fan-out implications.
  13. Q13Why is "subquery in FROM" called a "derived table" or "inline view"?
  14. Q14Explain "lateral subquery" vs "subquery in FROM".
  15. Q15Compare HAVING WHERE filter vs subquery.
  16. Q16Why must derived tables have aliases?
  17. Q17Compare HAVING SUM(x) > (subq) vs WHERE (with pre-aggregate).
  18. Q18What is "subquery flattening"?
  19. Q19Why does putting an aggregate in WHERE error?
  20. Q20Compare scalar subquery in SELECT vs adding to GROUP BY.
  21. Q21What is "subquery factoring" - same as CTE.
  22. Q22Explain why subqueries in SELECT can hide N+1 query patterns.
  23. Q23Compare subquery in WHERE = (single value) vs IN (set).
  24. Q24Why does ORDER BY with scalar subquery hurt performance.
  25. Q25Walk through how planner decides Hash Semi-Join vs Nested Loop.

IN / EXISTS DEEPER

  1. Q26Find customers with orders AND reviews - combine 2 EXISTS.
  2. Q27Find customers with orders BUT no reviews.
  3. Q28Find customers with orders in 2024 AND 2025.
  4. Q29Find products with reviews in last 30 days AND price > 1000.
  5. Q30Find stores with employees AND orders AND inventory.
  6. Q31Find brands present in BOTH 'Electronics' AND 'Apparel' categories.
  7. Q32Find customers in cities WITH at least one store.
  8. Q33Find products of suppliers in 'Mumbai' AND shipped in last 60 days.
  9. Q34Find tickets created by customers with > 5 orders.
  10. Q35Find pay_slips for employees in stores with > 10 employees.
  11. Q36Find orders by customers who are loyalty members.
  12. Q37Find orders by customers WHO are NOT loyalty members.
  13. Q38Find products in categories with > 100 products.
  14. Q39Find products in brands with avg_price > 5000.
  15. Q40Find customers in tiers with > 1000 members.
  16. Q41Find orders shipped by couriers with avg_delivery_time < 3 days.
  17. Q42Find ad spend rows for campaigns active in March 2025.
  18. Q43Find reviews on products with > 5 sales.
  19. Q44Find tickets on products that have ever been returned.
  20. Q45Find calls regarding products with low ratings.
  21. Q46Find page_views in sessions that ended with a purchase.
  22. Q47EXISTS with multiple correlated columns.
  23. Q48NOT EXISTS for a complex predicate (subquery JOIN).
  24. Q49Nested EXISTS: customers who reviewed products they returned.
  25. Q50EXISTS + DISTINCT - when redundant.

SUBQUERY IN FROM (DERIVED TABLE)

  1. Q51Derived table: (SELECT cust_id, SUM(net_total) AS total FROM orders GROUP BY cust_id) AS s.
  2. Q52Filter derived: WHERE total > 50000.
  3. Q53Join derived with customers.
  4. Q54Join 3 derived tables.
  5. Q55Top-N from a derived.
  6. Q56Derived with GROUP BY + HAVING.
  7. Q57Derived with window function (preview).
  8. Q58Derived with UNION ALL.
  9. Q59Derived with EXCEPT.
  10. Q60Derived returning multiple columns.
  11. Q61Derived using DISTINCT ON.
  12. Q62Derived used in CASE expression.
  13. Q63Derived used in scalar context.
  14. Q64Derived used twice - performance implications.
  15. Q65Derived inside another derived (nested).
  16. Q66Subquery with ORDER BY + LIMIT for top-N.
  17. Q67Subquery in LEFT JOIN.
  18. Q68Subquery in RIGHT JOIN.
  19. Q69Subquery in FULL OUTER JOIN.
  20. Q70Subquery aliasing columns explicitly.
  21. Q71Derived used in window function PARTITION BY.
  22. Q72Derived with no GROUP BY but aggregate.
  23. Q73Replace nested derived with CTE - same result, more readable.
  24. Q74Derived returning JSON.
  25. Q75Derived built from a UNION across schemas.

HAVING WITH SUBQUERY

  1. Q76HAVING SUM(net_total) > (SELECT AVG(SUM(net_total)) FROM ...).
  2. Q77HAVING COUNT(*) > (SELECT AVG count) - find above-average.
  3. Q78HAVING SUM > 10 x AVG.
  4. Q79HAVING SUM > 2 x prior period.
  5. Q80HAVING with EXISTS.
  6. Q81HAVING with NOT EXISTS.
  7. Q82HAVING with correlated subquery.
  8. Q83HAVING based on another table's stats.
  9. Q84HAVING with multiple conditions (AND/OR).
  10. Q85HAVING comparing two aggregates from same query.
  11. Q86HAVING per-region: revenue > region's avg.
  12. Q87HAVING per-category: AVG > overall AVG.
  13. Q88HAVING with CASE-based COUNT.
  14. Q89HAVING for outlier detection.
  15. Q90HAVING SUM(qty FILTER WHERE) > N.
  16. Q91HAVING with date-based subquery.
  17. Q92HAVING + GROUP BY ROLLUP.
  18. Q93HAVING + GROUP BY CUBE.
  19. Q94HAVING + complex CASE in COUNT.
  20. Q95HAVING + percentile threshold.
  21. Q96HAVING + median threshold.
  22. Q97HAVING + max threshold.
  23. Q98HAVING + min threshold.
  24. Q99HAVING + COUNT distinct > N.
  25. Q100Combine HAVING + subquery + window + ROLLUP in one mega query.

Interview grade, edge cases

SUBQUERY REWRITES

  1. Q1Rewrite IN-subquery as JOIN.
  2. Q2Rewrite NOT IN as NOT EXISTS (NULL-safe).
  3. Q3Rewrite correlated subquery as LEFT JOIN.
  4. Q4Rewrite scalar subquery in SELECT as LEFT JOIN.
  5. Q5Rewrite EXISTS as INNER JOIN + DISTINCT.
  6. Q6Rewrite multiple scalar subqueries as a single CTE.
  7. Q7Rewrite "(SELECT MAX...)" as window function.
  8. Q8Rewrite "WHERE x = (subquery)" with DISTINCT ON.
  9. Q9Rewrite "WHERE x IN (top-N)" as LATERAL.
  10. Q10Rewrite HAVING subquery to a WHERE on aggregated CTE.
  11. Q11Rewrite multi-level nested subquery as flat CTE chain.
  12. Q12Rewrite "for each row, count related" via window.
  13. Q13Rewrite correlated MAX subquery as window.
  14. Q14Rewrite "find rows with same key" via window.
  15. Q15Rewrite "find latest per group" via DISTINCT ON or ROW_NUMBER.
  16. Q16Rewrite EXISTS subquery as LEFT JOIN ... IS NOT NULL.
  17. Q17Rewrite NOT EXISTS as LEFT JOIN ... IS NULL.
  18. Q18Rewrite OR-subquery as UNION ALL.
  19. Q19Rewrite AND-of-subqueries as INNER JOIN chain.
  20. Q20Rewrite "Top-N per group" subquery as LATERAL.
  21. Q21Rewrite "find duplicates" subquery as window count.
  22. Q22Rewrite "find gaps" subquery as window LAG.
  23. Q23Rewrite "find islands" subquery as window.
  24. Q24Rewrite "find runs" subquery as gaps-and-islands.
  25. Q25Rewrite percent-of-total subquery using window.

PERFORMANCE FORENSICS

  1. Q26EXPLAIN ANALYZE a correlated subquery.
  2. Q27EXPLAIN ANALYZE the same query rewritten as JOIN - compare.
  3. Q28EXPLAIN IN-subquery vs JOIN - same plan?
  4. Q29EXPLAIN scalar subquery in SELECT - see the per-row subplan.
  5. Q30Identify "SubPlan" node in EXPLAIN.
  6. Q31Identify "InitPlan" node (uncorrelated subquery).
  7. Q32Identify "Hash Semi Join" for IN-subquery.
  8. Q33Identify "Hash Anti Join" for NOT EXISTS.
  9. Q34Add index to speed up correlated subquery.
  10. Q35Add composite index for two-column correlated subquery.
  11. Q36Add expression index for subquery filter.
  12. Q37Pre-aggregate via CTE to reduce subquery cost.
  13. Q38Materialize CTE explicitly to control plan.
  14. Q39Use LATERAL to replace expensive correlated subquery.
  15. Q40Use WINDOW to replace expensive subquery in SELECT.
  16. Q41Use SET enable_nestloop = off to see hash-based plan.
  17. Q42Compare plan with and without random_page_cost tuning.
  18. Q43Tune work_mem to keep hash in memory.
  19. Q44Diagnose "subquery returns more than one row" error.
  20. Q45Add LIMIT 1 + ORDER BY for safe scalar subquery.
  21. Q46Diagnose "subquery used in expression must return single column" error.
  22. Q47Diagnose "subquery in FROM must have an alias" error.
  23. Q48Write an IN-subquery query pattern (in prod, pg_stat_statements finds the slow ones).
  24. Q49Track "N+1" via repeated subqueries from app logs.
  25. Q50Audit slow queries with subqueries - top 10.

ANTI-PATTERN CATALOG

  1. Q51ANTIPATTERN: NOT IN with nullable subquery.
  2. Q52ANTIPATTERN: scalar subquery in SELECT for every row of huge table.
  3. Q53ANTIPATTERN: correlated subquery instead of JOIN.
  4. Q54ANTIPATTERN: SELECT (subquery) in app loop (N+1).
  5. Q55ANTIPATTERN: subquery in WHERE without index.
  6. Q56ANTIPATTERN: ORDER BY (subquery) - re-evaluated per row.
  7. Q57ANTIPATTERN: DISTINCT after EXISTS - redundant.
  8. Q58ANTIPATTERN: EXISTS (SELECT col FROM ...) - col evaluation wasted.
  9. Q59ANTIPATTERN: IN-subquery with 100k values - slow.
  10. Q60ANTIPATTERN: WHERE col IN (SELECT col FROM same_table) - likely just need DISTINCT.
  11. Q61ANTIPATTERN: triple-nested correlated subquery.
  12. Q62ANTIPATTERN: subquery + ORDER BY without LIMIT - wasted sort.
  13. Q63ANTIPATTERN: subquery materialized when it should be inlined.
  14. Q64ANTIPATTERN: NOT EXISTS with FROM cross product.
  15. Q65ANTIPATTERN: HAVING with full table scan inside.
  16. Q66ANTIPATTERN: subquery returning entire JSON instead of needed field.
  17. Q67ANTIPATTERN: hardcoded IN-list when JOIN to table is cleaner.
  18. Q68ANTIPATTERN: subquery in CASE that fires N times.
  19. Q69ANTIPATTERN: scalar subquery returning whole row (use composite type).
  20. Q70ANTIPATTERN: GROUP BY + subquery + window - overcomplicated.
  21. Q71ANTIPATTERN: subquery using SELECT * (wasted columns).
  22. Q72ANTIPATTERN: nested derived tables with redundant aliases.
  23. Q73ANTIPATTERN: WHERE x = ANY(subq) used as anti-join.
  24. Q74ANTIPATTERN: subquery in DELETE / UPDATE without WHERE.
  25. Q75ANTIPATTERN: subquery without ORDER BY in LIMIT 1.

REAL-WORLD PRODUCTION PATTERNS

  1. Q76Build "find duplicate customers by email" - subquery + GROUP BY.
  2. Q77Build "find orphan order_items" - anti-join.
  3. Q78Build "find late shipments" - correlated date subquery.
  4. Q79Build "above-average customers" report.
  5. Q80Build "below-median orders" report.
  6. Q81Build "high-LTV customer" segment.
  7. Q82Build "low-velocity products" report.
  8. Q83Build "high-priority unresolved tickets" report.
  9. Q84Build "premium tier upgrade candidates" - multi-criteria subquery.
  10. Q85Build "stale inventory" report - subquery on snapshot dates.
  11. Q86Build "churn-risk" report - subquery on last_order.
  12. Q87Build "high-spending guests" - anti-join on loyalty.
  13. Q88Build "fraud watchlist" - subquery on velocity + amount.
  14. Q89Build "agent leaderboard" - subquery on tickets resolved.
  15. Q90Build "supplier compliance" - anti-join on shipments missed.
  16. Q91Build "campaign effectiveness" - subquery on attributions.
  17. Q92Build "warehouse health" - subquery on snapshot age.
  18. Q93Build "courier reliability" - subquery on late deliveries.
  19. Q94Build "category trend" - month-over-month subquery.
  20. Q95Build "brand growth" - subquery comparing periods.
  21. Q96Build "tier migration" - subquery on prior tier.
  22. Q97Build "loyalty churn" - anti-join on member activity.
  23. Q98Build "executive scorecard" - 20 metric subqueries in 1 row.
  24. Q99Build "data quality" - subquery on NULL counts per table.
  25. Q100Build "anomaly detection" - subquery on z-score.

Production scenarios, optimisation

SUBQUERY + WINDOW

  1. Q1Top-N per group via DISTINCT ON + subquery.
  2. Q2Top-N per group via ROW_NUMBER.
  3. Q3Top-3 customers per region.
  4. Q4Top-5 products per category.
  5. Q5Top-10 stores per region.
  6. Q6Top-N + tiebreaker via composite ORDER BY.
  7. Q7Per row, percent of group total via window.
  8. Q8Per row, percent of grand total via subquery + window.
  9. Q9Running total per group.
  10. Q10Year-over-year growth via window.
  11. Q11Quarter-over-quarter growth.
  12. Q12Month-over-month.
  13. Q13Customer lifetime: window of all orders.
  14. Q14RFM bucket via NTILE + subquery.
  15. Q15NTILE quartile of order_total.
  16. Q16Median per group via percentile_cont.
  17. Q17Top-3 cheapest per category.
  18. Q18Top-3 most expensive per category.
  19. Q19Per region, customer with biggest spend.
  20. Q20Per category, brand with most products.
  21. Q21Per store, top employee by tickets resolved.
  22. Q22Per courier, longest shipment.
  23. Q23Per warehouse, oldest snapshot.
  24. Q24Per supplier, latest shipment.
  25. Q25Per agent, top-rated call.

MULTI-SOURCE SUBQUERIES

  1. Q26Customer 360deg: orders + reviews + tickets + calls + returns + page_views.
  2. Q27Product 360deg: sold + reviewed + returned + in inventory.
  3. Q28Store 360deg: employees + orders + revenue + complaints.
  4. Q29Region 360deg.
  5. Q30Campaign 360deg.
  6. Q31Employee 360deg.
  7. Q32Brand 360deg.
  8. Q33Supplier 360deg.
  9. Q34Tier 360deg.
  10. Q35Agent 360deg.
  11. Q36Per customer, union of all touchpoints.
  12. Q37Per product, union of all events.
  13. Q38Per region, union of all sales channels.
  14. Q39Per campaign, union of attribution sources.
  15. Q40Per warehouse, union of inventory changes.
  16. Q41Per supplier, union of orders shipped.
  17. Q42Per courier, union of deliveries + failures.
  18. Q43Per tier, union of upgrades + downgrades.
  19. Q44Per agent, union of tickets + calls.
  20. Q45Per page_view, enrich with customer + product.
  21. Q46Cross-schema EXISTS chain.
  22. Q47Cross-schema NOT EXISTS for orphan detection.
  23. Q48Cross-domain JOIN via subqueries.
  24. Q49Cross-domain aggregation via UNION ALL of subqueries.
  25. Q50Cross-domain anti-join via EXCEPT.

SUBQUERY IN DML (25) NOTE: RetailMart is READ-ONLY - run these DML statements in your OWN practice database, or preview the affected rows with a SELECT first (the answer key shows the SELECT preview against RetailMart).

  1. Q51UPDATE orders SET status = 'reviewed' WHERE order_id IN (subquery).
  2. Q52UPDATE customers SET tier = (the customer's loyalty tier, via subquery).
  3. Q53DELETE old_orders WHERE order_id IN (subquery).
  4. Q54INSERT INTO archive SELECT * FROM ... WHERE ... (subquery filter).
  5. Q55UPDATE products SET stock = (subquery from inventory).
  6. Q56UPDATE employees SET salary = salary * 1.1 WHERE dept_id IN (subquery).
  7. Q57DELETE customers.customers WHERE customer_id NOT IN (subquery).
  8. Q58INSERT INTO audit_log SELECT order_id FROM sales.orders WHERE ... (subquery filter).
  9. Q59UPDATE tickets SET status = 'closed' WHERE created_date < (subquery date).
  10. Q60UPDATE ad_campaigns SET active = false WHERE id IN (subquery low spend).
  11. Q61CREATE TABLE high_value AS SELECT * FROM sales.orders WHERE net_total > (subq).
  12. Q62ALTER TABLE ADD COLUMN tier_id DEFAULT (subquery)? Show how (immutable required).
  13. Q63UPSERT customers using a subquery for the new tier.
  14. Q64MERGE INTO ... USING (subquery) ...
  15. Q65Bulk INSERT via subquery (INSERT SELECT).
  16. Q66Subquery-driven UPDATE setting multiple columns.
  17. Q67Subquery in WITH for CTE-based DML.
  18. Q68Subquery in RETURNING.
  19. Q69UPDATE ... FROM (subquery).
  20. Q70DELETE ... USING (subquery).
  21. Q71CTE-DML chain: WITH d AS (DELETE ...) INSERT INTO archive SELECT * FROM d.
  22. Q72UPDATE with subquery to compute new value per row.
  23. Q73Conditional UPDATE: WHERE (subquery) IS DISTINCT FROM new_value.
  24. Q74DELETE with subquery and LIMIT (batched).
  25. Q75INSERT INTO ... SELECT from JOIN of multiple subqueries.

REAL REPORTS

  1. Q76"Above average" report per region.
  2. Q77"Below median" report per category.
  3. Q78"Top quartile" customers.
  4. Q79"Bottom quartile" products.
  5. Q80"Outlier orders" via z-score.
  6. Q81"Cohort retention" via subquery on signup month.
  7. Q82"Churn analysis" with subquery on last activity.
  8. Q83"Win-back targets" - lapsed customers.
  9. Q84"Tier movers" - upgraded recently.
  10. Q85"Loyalty new members".
  11. Q86"Inventory at risk" - subquery on velocity vs stock.
  12. Q87"Stockout candidates" - qty < forecast.
  13. Q88"Supplier scorecard" - multi-metric subqueries.
  14. Q89"Courier scorecard".
  15. Q90"Campaign ROI".
  16. Q91"Brand performance vs peers".
  17. Q92"Top 10% products by revenue".
  18. Q93"Bottom 10% products by sales velocity".
  19. Q94"VIP customers" - multi-criteria.
  20. Q95"Fraud watchlist" - subquery on velocity + amount.
  21. Q96"SLA breach" - subquery on response time.
  22. Q97"Stuck tickets" - open for > p95 time.
  23. Q98"Reactivation candidates" - last_order in 60-180 days.
  24. Q99"Refund risk" - subquery on customer return rate.
  25. Q100"Executive 1-pager" - 50 metrics from subqueries in one row.