TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 09

Joins 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

JOIN CONCEPTUAL

  1. Q1Difference between INNER JOIN and LEFT JOIN in one sentence.
  2. Q2When does LEFT JOIN produce NULLs in the result?
  3. Q3Why is RIGHT JOIN considered redundant - what's the equivalent LEFT JOIN trick?
  4. Q4What is the anti-join pattern? Write it conceptually.
  5. Q5Why must JOIN have an ON clause - what happens without it?
  6. Q6What's the difference between JOIN ... ON and JOIN ... USING?
  7. Q7In a LEFT JOIN, when WHERE filters the right-side column, what silently happens?
  8. Q8Why does COUNT(*) over-count on a LEFT JOIN when measuring left-side rows?
  9. Q9What's a foreign-key path - give the path from sales.order_items to core.dim_category.
  10. Q10Why is qualifying every column (alias.column) considered best practice in JOIN queries?

INNER JOIN - 2 TABLES

  1. Q11Show every order with the customer's first name. JOIN sales.orders + customers.customers.
  2. Q12Show every order with the store's name. JOIN sales.orders + stores.stores.
  3. Q13Show every order_item with its product's name. JOIN sales.order_items + products.products.
  4. Q14Show every employee with their department name. JOIN stores.employees + core.dim_department.
  5. Q15Show every store with its region name. JOIN stores.stores + core.dim_region.
  6. Q16Show every product with its brand name. JOIN products.products + core.dim_brand.
  7. Q17Show every brand with its category name. JOIN core.dim_brand + core.dim_category.
  8. Q18Show every review with the customer's full name. JOIN customers.reviews + customers.customers.
  9. Q19Show every ticket with the customer's email. JOIN support.tickets + customers.customers.
  10. Q20Show every ticket with the agent's name (employee). JOIN support.tickets + stores.employees.
  11. Q21Show every loyalty member with their tier name. JOIN loyalty.members + loyalty.tiers.
  12. Q22Show every loyalty redemption with the customer's name.
  13. Q23Show every payment with the order_status. JOIN sales.payments + sales.orders.
  14. Q24Show every shipment with the order's net_total. JOIN sales.shipments + sales.orders.
  15. Q25Show every return with the order_date. JOIN sales.returns + sales.orders.
  16. Q26Show every call with the customer name. JOIN call_center.calls + customers.customers.
  17. Q27Show every call with the agent name. JOIN call_center.calls + stores.employees.
  18. Q28Show every supply-chain shipment with the warehouse name. JOIN supply_chain.shipments + supply_chain.warehouses.
  19. Q29Show every supply-chain shipment with the supplier name. JOIN supply_chain.shipments + products.suppliers.
  20. Q30Show every inventory snapshot with the warehouse name. JOIN supply_chain.inventory_snapshots + supply_chain.warehouses.
  21. Q31Show every work_order with the production line name. JOIN manufacture.work_orders + manufacture.production_lines.
  22. Q32Show every transcript with the call_reason. JOIN call_center.transcripts + call_center.calls.
  23. Q33Show every pay_slip with the employee's first name. JOIN payroll.pay_slips + stores.employees.
  24. Q34Show every attendance entry with the employee's role.
  25. Q35Show every expense with its category_name. JOIN finance.expenses + core.dim_expense_category.
  26. Q36Show every order with the customer tier (Bronze/Silver/Gold/Platinum).
  27. Q37Show every page_view with the customer's first_name (skip anonymous ones - INNER JOIN drops them automatically).
  28. Q38Show every ad_spend record with the campaign name. JOIN marketing.ads_spend + marketing.campaigns.
  29. Q39Show every order with the store's CITY (orders + stores).
  30. Q40Show every customer review with the product name (reviews + products).

LEFT JOIN (30) Topics: preserve all left rows; NULL for unmatched right; anti-joins

  1. Q41ALL customers + a left join to sales.orders - show order_id (will be NULL for never-ordered customers).
  2. Q42ALL products + LEFT JOIN to sales.order_items - flag products NEVER ordered (oi.order_item_id IS NULL).
  3. Q43ALL employees + LEFT JOIN to support.tickets (as agent).
  4. Q44ALL stores + LEFT JOIN to sales.orders - find stores with NO orders.
  5. Q45ALL customers + LEFT JOIN to customers.reviews - find customers who NEVER reviewed.
  6. Q46ALL customers + LEFT JOIN to support.tickets - find customers who NEVER raised a ticket.
  7. Q47ALL customers + LEFT JOIN to loyalty.members - non-members appear with NULL tier_id.
  8. Q48ALL orders + LEFT JOIN to sales.shipments - find orders without shipments.
  9. Q49ALL orders + LEFT JOIN to sales.payments - find orders not yet paid.
  10. Q50ALL orders + LEFT JOIN to sales.returns - find orders NEVER returned.
  11. Q51ALL products + LEFT JOIN to customers.reviews - find products with NO reviews.
  12. Q52ALL employees + LEFT JOIN to hr.attendance - find employees with NO attendance entries.
  13. Q53ALL warehouses + LEFT JOIN to supply_chain.inventory_snapshots - find warehouses with NO inventory data.
  14. Q54ALL warehouses + LEFT JOIN to supply_chain.shipments.
  15. Q55ALL campaigns + LEFT JOIN to marketing.ads_spend - campaigns with no spend?
  16. Q56ALL production_lines + LEFT JOIN to manufacture.work_orders.
  17. Q57ALL departments + LEFT JOIN to stores.employees - departments with NO employees.
  18. Q58ALL regions + LEFT JOIN to stores.stores - regions with NO stores.
  19. Q59ALL brands + LEFT JOIN to products.products - brands with NO products.
  20. Q60ALL categories + LEFT JOIN to core.dim_brand - categories with NO brands.
  21. Q61ALL tiers + LEFT JOIN to loyalty.members - tiers with no members.
  22. Q62ALL expense_categories + LEFT JOIN to finance.expenses.
  23. Q63ALL suppliers + LEFT JOIN to products.products - suppliers with no products.
  24. Q64Anti-join: products NEVER ordered (LEFT JOIN + WHERE order_item_id IS NULL).
  25. Q65Anti-join: customers NEVER ordered.
  26. Q66Anti-join: customers NEVER reviewed any product.
  27. Q67Anti-join: orders NEVER shipped.
  28. Q68Anti-join: customers NOT in loyalty program.
  29. Q69Anti-join: tickets NEVER resolved (use ticket.resolved_date IS NULL - single table; but show LEFT JOIN style for practice).
  30. Q70Anti-join: employees with NO ticket assignments.

RIGHT JOIN + MULTI-TABLE (30) Topics: RIGHT JOIN basics, then 3-table INNER chains

  1. Q71RIGHT JOIN customers.customers RIGHT JOIN sales.orders - all orders, with customer info.
  2. Q72RIGHT JOIN stores.stores RIGHT JOIN sales.orders - same idea.
  3. Q73RIGHT JOIN products.products RIGHT JOIN sales.order_items - every item with product info.
  4. Q74RIGHT JOIN: same as Q71 but rewritten as a LEFT JOIN.
  5. Q75RIGHT JOIN: same as Q72 but rewritten as a LEFT JOIN.
  6. Q76JOIN sales.orders + customers.customers + stores.stores: order_id, customer name, store name.
  7. Q77JOIN sales.order_items + products.products + sales.orders: item_id, product_name, order_date.
  8. Q78JOIN products.products + core.dim_brand + core.dim_category: product, brand, category.
  9. Q79JOIN stores.employees + stores.stores + core.dim_region: employee, store, region.
  10. Q80JOIN stores.employees + core.dim_department + stores.stores: employee, dept_name, store_name.
  11. Q81JOIN loyalty.members + customers.customers + loyalty.tiers: member name, tier name, points.
  12. Q82JOIN sales.orders + customers.customers + customers.addresses: order_id, customer, city.
  13. Q83JOIN call_center.calls + customers.customers + stores.employees: customer, agent, duration.
  14. Q84JOIN support.tickets + customers.customers + stores.employees: customer, agent, subject.
  15. Q85JOIN customers.reviews + customers.customers + products.products: customer, product, rating.
  16. Q86JOIN sales.returns + sales.orders + products.products: return, order_date, product.
  17. Q87JOIN supply_chain.shipments + supply_chain.warehouses + products.suppliers: shipment, warehouse, supplier.
  18. Q88JOIN manufacture.work_orders + manufacture.production_lines + products.products: work_order_id, line, product.
  19. Q89JOIN payroll.pay_slips + stores.employees + stores.stores: pay_slip, employee, store.
  20. Q90JOIN hr.attendance + stores.employees + core.dim_department: attendance, employee, department.
  21. Q91JOIN marketing.ads_spend + marketing.campaigns + ... bonus: just 2 tables. spend, campaign name.
  22. Q92JOIN finance.expenses + core.dim_expense_category + (where amount > 50000).
  23. Q93JOIN audit.application_logs + (no FK needed, single table - but show with a JOIN to a synthetic literal - skip). Instead: JOIN audit.api_requests + ... single-table query (acceptable).
  24. Q94JOIN call_center.transcripts + call_center.calls + customers.customers: transcript_id, customer, sentiment_score.
  25. Q95JOIN web_events.events + web_events.page_views + customers.customers: event, customer, page_url.
  26. Q96JOIN loyalty.redemptions + customers.customers + loyalty.members + loyalty.tiers: customer, tier, redemption reward.
  27. Q97JOIN sales.orders + customers.customers + stores.stores + core.dim_region: order, customer, store, region.
  28. Q98JOIN sales.order_items + products.products + core.dim_brand + core.dim_category + sales.orders: item, product, brand, category, order_date.
  29. Q99JOIN sales.orders + customers.customers + sales.payments: order_id, customer, payment_mode.
  30. Q100JOIN sales.orders + customers.customers + sales.shipments + stores.stores: full order trace (customer + courier + store).

Combined ideas, multi-step thinking

JOIN DEEPER - CONCEPTUAL

  1. Q1Why does putting a right-table filter in WHERE silently turn a LEFT JOIN into an INNER JOIN?
  2. Q2What is "fan-out" in JOINs - and how do you detect it?
  3. Q3Why does COUNT(*) on a LEFT JOIN over-count when measuring left rows?
  4. Q4Compare COUNT(left.id) vs COUNT(DISTINCT left.id) vs COUNT(*) in a LEFT JOIN.
  5. Q5Does JOIN order (A JOIN B JOIN C vs A JOIN C JOIN B) affect the RESULT? Performance?
  6. Q6Compare JOIN ... ON vs JOIN ... USING - which collapses the duplicate column in the result?
  7. Q7Why is JOIN orders ON 1 = 1 not the same as CROSS JOIN - what does it produce?
  8. Q8When does an INNER JOIN drop rows you didn't expect - name three causes.
  9. Q9Explain how a LEFT JOIN handles three rows in the left and one in the right matching one of them.
  10. Q10Why does ANSI SQL recommend explicit JOIN syntax over comma-separated FROM clauses?
  11. Q11What is a "Cartesian explosion" and how do you accidentally cause one?
  12. Q12Compare LEFT JOIN + WHERE right.key IS NULL vs NOT EXISTS - which is more readable, which is faster?
  13. Q13Why does the PostgreSQL planner choose Hash Join vs Nested Loop based on row counts?
  14. Q14When does INNER JOIN's row count equal the smaller table's row count - and when does it not?
  15. Q15Why is JOIN customers c1 JOIN customers c2 ON c1.id != c2.id typically an interview red flag?
  16. Q16Explain why aliases (e1/e2/c1/c2) are MANDATORY in self-joins.
  17. Q17Compare INNER JOIN vs SEMI JOIN (which PostgreSQL implements via EXISTS internally).
  18. Q18What is an "anti semi join"? When does PostgreSQL use it?
  19. Q19Why is the result of a JOIN with NULL keys deterministic - they never match?
  20. Q20Explain why JOIN order matters less when JOINs are INNER but more for OUTER JOINs.
  21. Q21What does "EQUI JOIN" mean vs "non-equi JOIN"? Give a real RetailMart non-equi example.
  22. Q22Why is LEFT JOIN ... ON a.x = b.x AND b.y = 5 different from LEFT JOIN ... ON a.x = b.x WHERE b.y = 5?
  23. Q23Compare USING (col) vs NATURAL JOIN - and why NATURAL JOIN is dangerous.
  24. Q24How does the GROUP BY interact with multi-table JOINs - what counts as a "row" being grouped?
  25. Q25Why must INNER JOIN with aggregates often be wrapped in a CTE for readability?

INNER JOIN + FILTER + GROUP BY

  1. Q26Per region (via stores), COUNT orders + SUM net_total.
  2. Q27Per category, COUNT products.
  3. Q28Per dim_brand, COUNT products + AVG price.
  4. Q29Per dim_department, COUNT employees + AVG salary.
  5. Q30Per tier (from loyalty.tiers JOIN loyalty.members), COUNT members + AVG points_balance.
  6. Q31Per customer (JOIN orders), COUNT lifetime orders + SUM total spend.
  7. Q32Per support agent (JOIN tickets), COUNT tickets resolved + AVG resolution time.
  8. Q33Per product, SUM quantity sold (JOIN order_items).
  9. Q34Per region (3-table JOIN: orders -> stores -> regions), SUM revenue.
  10. Q35Per warehouse, SUM quantity_on_hand (JOIN inventory_snapshots latest).
  11. Q36Per supplier (JOIN supply_chain.shipments), SUM quantity shipped.
  12. Q37Per category (JOIN products -> dim_brand -> dim_category), SUM revenue from order_items.
  13. Q38Per month + region (JOIN orders + stores + region + DATE_TRUNC), SUM net_total.
  14. Q39Per courier_name (JOIN shipments -> orders), AVG delivery duration.
  15. Q40Per agent_id (JOIN call_center.calls -> stores.employees), total call_duration.
  16. Q41Per warehouse (3-table JOIN), TOP 3 products by quantity_on_hand. (Window function preview.)
  17. Q42Per dim_department + store_id, COUNT employees + AVG salary.
  18. Q43Per page_view device_type + customer's tier (JOIN page_views -> customers), COUNT page_views.
  19. Q44Per platform + month (JOIN ads_spend -> campaigns), SUM amount.
  20. Q45Per category + ticket_status (JOIN tickets), COUNT.
  21. Q46Per ticket category + day_of_week (JOIN nothing - single table CASE), COUNT.
  22. Q47Per region (JOIN dim_region -> stores -> orders), TOP 10 by revenue.
  23. Q48Per brand_name (JOIN brand -> products -> order_items), SUM net_amount.
  24. Q49Per category_name (JOIN to dim_category), SUM revenue.
  25. Q50Per region_name + tier (JOIN orders, stores, regions, customers), COUNT distinct customers.

LEFT JOIN EDGE CASES + ANTI-JOIN

  1. Q51Show ALL departments with COUNT of employees (departments with 0 should appear as 0).
  2. Q52Show ALL brands with COUNT of products.
  3. Q53Show ALL stores with COUNT of orders (zero-order stores should appear as 0).
  4. Q54Show ALL customers with COUNT of orders (use LEFT JOIN + COUNT right table column).
  5. Q55Show ALL products with SUM of net_amount from order_items (zero-sale products show 0).
  6. Q56Show ALL tiers with COUNT of members.
  7. Q57Show ALL employees with COUNT of resolved tickets.
  8. Q58Show ALL customers with COUNT of reviews (zero-review customers show 0).
  9. Q59Show ALL regions with SUM revenue (zero-revenue regions appear with 0).
  10. Q60Show ALL warehouses with SUM(quantity_on_hand).
  11. Q61Show ALL campaigns with SUM ad_spend amount.
  12. Q62Find customers who NEVER placed an order (anti-join).
  13. Q63Find products NEVER ordered.
  14. Q64Find stores with NO orders ever.
  15. Q65Find brands with NO products.
  16. Q66Find suppliers whose products were NEVER ordered (3-table anti-join).
  17. Q67Find employees who NEVER appeared in support.tickets as agent.
  18. Q68Find customers who placed orders but NEVER wrote a review.
  19. Q69Find customers who NEVER had any interaction (orders, reviews, tickets, calls).
  20. Q70Find orders with NO shipment record.
  21. Q71Find orders with NO payment record.
  22. Q72Find tickets that were NEVER assigned to an agent (agent_id IS NULL).
  23. Q73Find warehouses that NEVER received a supply_chain.shipment.
  24. Q74Find products that have inventory_snapshots but NO recent shipments (90 days).
  25. Q75Find customers in loyalty.members but with NO redemptions.

MULTI-TABLE CHAINS (4-5 TABLES)

  1. Q76Order receipt: order_id, customer name, store name, product name, qty, amount (5 tables).
  2. Q77Per customer, total spend + count of distinct stores ordered from (3 tables).
  3. Q78Per region, count distinct customers, count distinct stores, sum revenue (4 tables).
  4. Q79Per category, top product by units sold (5 tables + window preview).
  5. Q80Per ticket, customer name + agent name + store of agent (4 tables).
  6. Q81Per call, customer name + agent name + agent's store (4 tables).
  7. Q82Per work_order, product name + production line name + days to complete (3 tables).
  8. Q83Per page_view, customer first_name + os + device_type (2 tables - single JOIN).
  9. Q84Per ad_spend, campaign_name + spend month + platform (2 tables).
  10. Q85Per refund (return), order_date + customer name + product name (4 tables).
  11. Q86Per shipment, courier_name + order's customer + store city (4 tables).
  12. Q87Per pay_slip, employee name + department name + store_name (3 tables).
  13. Q88Per loyalty member, customer name + tier name + lifetime orders (4 tables).
  14. Q89Per expense, expense category name + amount + month (2 tables).
  15. Q90Per inventory snapshot, warehouse name + product name + brand name + category name (5 tables).
  16. Q91Per supplier, product count + supplier city + total quantity_shipped (3 tables).
  17. Q92Customer 360deg: customer name, lifetime orders, total spend, count of reviews, count of tickets (4 LEFT JOINs + aggregates).
  18. Q93Store 360deg: store name, region name, employee count, order count, revenue (5 tables - handle fan-out with subqueries).
  19. Q94Per agent_id, count tickets handled + count calls handled (UNION of two sources) - preview Topic 11.
  20. Q95Top 10 categories by revenue (5 tables: order_items -> products -> dim_brand -> dim_category + orders).
  21. Q96Top 10 cities by total customer spend (4 tables).
  22. Q97Top 10 suppliers by total quantity shipped to warehouses (3 tables).
  23. Q98Per audit.api_requests endpoint, COUNT + AVG response_time (single table - but include AVG of response_time_ms grouped).
  24. Q99Per call_reason, AVG sentiment_score from transcripts (3 tables: calls, transcripts, sentiment scope).
  25. Q100Full order trace: order_id + customer + store + payment + shipment + first item product (6 tables, mix INNER + LEFT).

Interview grade, edge cases

JOINs - CONCEPTUAL DEEP

  1. Q1When does the planner pick Hash Join vs Merge Join vs Nested Loop?
  2. Q2Why does adding ORDER BY join_key encourage Merge Join?
  3. Q3Compare hash join build/probe phases - which side is hashed.
  4. Q4What is a "broadcast join" - and does Postgres do it?
  5. Q5Why is INNER JOIN ON a.x = b.y faster than CROSS JOIN + WHERE? (Hint: same plan in modern engines.)
  6. Q6Why is JOIN ON a.x = b.y AND a.z = b.z faster with a composite index (x, z)?
  7. Q7Non-equi join: WHERE a.range_start <= b.event_date <= a.range_end - give a RetailMart case.
  8. Q8Range join in Postgres - what indexes help (GIST on tsrange)?
  9. Q9Compare LATERAL JOIN vs correlated subquery - same idea, different syntax.
  10. Q10Anti-join three ways: LEFT JOIN IS NULL vs NOT EXISTS vs EXCEPT.
  11. Q11SEMI-JOIN: when does Postgres convert EXISTS to a semi-join?
  12. Q12Fan-out: how does it create row multiplication in aggregations?
  13. Q13How do you detect fan-out (compare COUNT(*) vs expected)?
  14. Q14Why does GROUP BY after a fan-out join over-count SUM?
  15. Q15Why is JOIN order irrelevant for INNER but critical for OUTER?
  16. Q16How does the planner decide JOIN order (join_collapse_limit)?
  17. Q17What is "estimated rows mismatch" - and why bad row estimates kill performance?
  18. Q18Walk through a triangle inequality JOIN: a.x + b.y > c.z.
  19. Q19Why is JOIN through a many-to-many bridge table called a "fan-out fan-in"?
  20. Q20Compare JOIN ON (a.x, a.y) = (b.x, b.y) vs separate AND.
  21. Q21Explain how OUTER JOIN's qualifying-side filter pushed into ON differs from WHERE.
  22. Q22What is "join reordering" - and how does the planner explore options?
  23. Q23Why does adding indexes BOTH sides of a JOIN help?
  24. Q24Why is WHERE a.x = b.x (comma syntax) equivalent to INNER JOIN but missing the OUTER semantics?
  25. Q25Walk through Postgres's "implicit JOIN" rewriting.

NON-EQUI / MULTI-COL JOINS

  1. Q26Range join: orders to campaigns running on the order_date (order_date BETWEEN start/end).
  2. Q27Range join: orders to promotions active on the order_date.
  3. Q28Range join: page_views to campaigns running during the view.
  4. Q29Range join: pay_slip to its tax bracket (gross_salary BETWEEN min_salary AND max_salary).
  5. Q30Range join: employee salary to its tax bracket.
  6. Q31Multi-col join: order_items (via their order's store) to products.inventory on (store_id, product_id).
  7. Q32Multi-col join: pay_slip to attendance on (employee_id, year).
  8. Q33Multi-col join: inventory_snapshot to supply_chain.shipment on (warehouse_id, product_id, date).
  9. Q34Multi-col join: order_items to returns on (order_id, prod_id).
  10. Q35Join + filter: orders to the customer's DEFAULT address (customer_id AND is_default).
  11. Q36INNER JOIN with a > predicate: line items priced above the product's list price.
  12. Q37Triangle JOIN: three products whose two cheaper prices exceed the third (bundle pricing).
  13. Q38JOIN where order_date is within 7 days of a campaign's start_date.
  14. Q39JOIN where the customer's default-address city = store's city (proxy for "local order").
  15. Q40JOIN with a composite key derived in a CTE (clean city from addresses).
  16. Q41Range JOIN: gaps-and-islands warmup (each order to the customer's NEXT order).
  17. Q42JOIN orders to their shipment, keeping only Delivered shipments.
  18. Q43JOIN orders to the customer's loyalty tier ("tier at order time" proxy).
  19. Q44JOIN with date_trunc to align granularity (month).
  20. Q45JOIN ON expression: orders bucketed into seasons (CASE-based).
  21. Q46Self-equality on derived key: orders sharing the first 4 digits of order_id.
  22. Q47Prefix self-join: products sharing the first 3 letters of product_name.
  23. Q48Same-brand product pairs (self-join on brand_id).
  24. Q49Chain join: product -> brand -> category.
  25. Q50Join reviews to the product and the reviewing customer.

ANTI-JOIN & SEMI-JOIN PATTERNS

  1. Q51Anti-join 3 ways: customers never ordered (LEFT IS NULL, NOT EXISTS, EXCEPT).
  2. Q52Anti-join: products with no reviews.
  3. Q53Anti-join: employees never assigned a ticket as agent.
  4. Q54Anti-join: customers with orders but no loyalty membership.
  5. Q55Anti-join: orders with no shipment.
  6. Q56Anti-join: ad spend rows with no matching campaign.
  7. Q57Anti-join: inventory_snapshot rows where product no longer exists.
  8. Q58Anti-join: pay_slips for employees no longer in stores.employees.
  9. Q59Anti-join: tickets created by deleted customers (orphans).
  10. Q60Anti-join: warehouses with no shipments in last 90 days.
  11. Q61Anti-join: customers who never wrote a review for products they bought.
  12. Q62Semi-join: customers who placed AT LEAST one order (EXISTS).
  13. Q63Semi-join: products with ANY review.
  14. Q64Semi-join: stores with employees AND orders AND inventory.
  15. Q65Semi-join: agents who handled BOTH tickets AND calls.
  16. Q66Find products sold in MULTIPLE regions (semi-join with HAVING COUNT > 1).
  17. Q67Find customers with returns AND no follow-up order.
  18. Q68Find suppliers who ship to ALL warehouses (relational division).
  19. Q69Find brands present in EVERY region (relational division).
  20. Q70Find customers who placed orders in BOTH 2024 AND 2025.
  21. Q71Find products in inventory with no order_items linkage.
  22. Q72Find pay_slips with no matching attendance record (same employee + year).
  23. Q73Find campaigns with no spend rows.
  24. Q74Find employees (agents) with NO calls handled in the last 30 days.
  25. Q75Find shipments referencing deleted orders (FK enforce check).

MULTI-TABLE CHAINS (3+ TABLES)

  1. Q766-table chain: order -> order_item -> product -> brand -> category -> supplier.
  2. Q775-table customer 360deg: customer -> order -> order_item -> product (+ review).
  3. Q78Workforce chain: employee -> pay_slip + department + store.
  4. Q79Marketing chain: campaign + ad spend + email engagement (clicks).
  5. Q80Inventory chain: snapshot -> warehouse + product + supplier (+ shipment).
  6. Q81Support chain: ticket -> customer + handling agent.
  7. Q82Detect fan-out: COUNT(*) of orders JOIN order_items vs distinct orders.
  8. Q83Subquery aggregation to avoid fan-out: pre-aggregate order_items into a CTE, then JOIN.
  9. Q84Fan-in: DISTINCT cust_id COUNT among multi-unit order lines.
  10. Q855-table count: customer -> order -> item -> product -> brand (line items per customer).
  11. Q86Items per order (orders x order_items, grouped).
  12. Q87Revenue by region: region -> store -> order.
  13. Q88Lifetime revenue per customer (customer -> order).
  14. Q89Order value from its items (order -> order_items).
  15. Q90Products per category: category -> brand -> product.
  16. Q91Re-write a JOIN as EXISTS (semi-join): customers with at least one order.
  17. Q92Convert a correlated subquery to a LEFT JOIN against a derived table.
  18. Q93Products per supplier (supplier -> product).
  19. Q94Use a MATERIALIZED CTE to pre-compute item totals, then join.
  20. Q95Returns with their order and customer.
  21. Q96Calls with their customer and handling agent.
  22. Q97Payments with their order and customer.
  23. Q98Orders since 2025 by store (store -> order, grouped).
  24. Q99Build a "BI report" query: region -> store -> order, aggregated.
  25. Q100Audit query: a customer table with metrics across multiple schemas (correlated subqueries).

Production scenarios, optimisation

MEGA TABLE CHAINS

  1. Q18 tables: order -> items -> product -> brand -> category -> supplier -> warehouse -> region.
  2. Q210 tables: customer full lifecycle joining all activity.
  3. Q3Fan-out controlled with pre-aggregation CTEs.
  4. Q46-table sales pipeline.
  5. Q56-table marketing attribution.
  6. Q66-table workforce / payroll.
  7. Q76-table support pipeline.
  8. Q86-table inventory & supply chain.
  9. Q9Cross-domain JOIN: sales + marketing + support per customer.
  10. Q10Cross-domain: HR + sales + customer per region.
  11. Q11Order trace: order -> payment -> shipment -> delivery -> review.
  12. Q12Customer trace: signup -> first order -> tier upgrade -> first review.
  13. Q13Refund trace: order -> item -> return -> refund -> audit.
  14. Q14Campaign trace: spend -> attribution -> order -> customer -> review.
  15. Q15Ticket trace: ticket -> customer -> product -> order -> resolution.
  16. Q16Call trace: call -> customer -> past_orders -> tickets.
  17. Q17Web -> order trace: page_view -> session -> cart -> checkout -> order.
  18. Q18Supplier trace: supplier -> product -> inventory -> shipment -> order_item.
  19. Q19Pay_slip trace: employee -> attendance -> pay_slip -> bank.
  20. Q20Region rollup: region -> store -> employee -> orders -> customers.
  21. Q21Brand revenue chain: brand -> product -> order_item -> order -> store -> region.
  22. Q22Loyalty chain: customer -> tier -> points -> redemption.
  23. Q23AB-test chain: page_view -> AB -> cart -> order.
  24. Q24Audit chain: order -> audit_log -> api_request -> app_log.
  25. Q25Full 10-table receipt: every dimension visible.

BI DASHBOARDS

  1. Q26Executive: revenue, orders, customers, AOV per month.
  2. Q27Regional: per region, top 5 stores by revenue.
  3. Q28Tier dashboard: members, points, redemptions, revenue.
  4. Q29Marketing: campaigns, spend, attributions, ROI.
  5. Q30Customer service: tickets, calls, resolution time, CSAT.
  6. Q31Inventory: stock by warehouse, low-stock, in-transit.
  7. Q32Supplier: top suppliers, deliveries, late count.
  8. Q33Brand performance: revenue, returns, avg rating.
  9. Q34Category trends: month-over-month.
  10. Q35Channel mix: organic vs paid vs referral.
  11. Q36Cohort retention.
  12. Q37Churn analysis.
  13. Q38Refund leaderboard.
  14. Q39Top customer LTV.
  15. Q40Top product velocity.
  16. Q41Employee productivity scoreboard.
  17. Q42Warehouse throughput.
  18. Q43Courier performance.
  19. Q44Geographic heatmap.
  20. Q45Device/platform mix.
  21. Q46Hourly traffic curve.
  22. Q47Weekly cadence.
  23. Q48Holiday vs non-holiday compare.
  24. Q49New vs returning customers.
  25. Q50Full executive 1-page (30 metrics).

PERFORMANCE TUNING

  1. Q51EXPLAIN ANALYZE a 6-table JOIN.
  2. Q52Pre-aggregate one branch into a CTE.
  3. Q53Use MATERIALIZED CTE for fan-out control.
  4. Q54Force partition-wise JOIN.
  5. Q55Add INDEX on FK column.
  6. Q56Add COVERING index.
  7. Q57Add INCLUDE columns.
  8. Q58Use partial index for hot subset.
  9. Q59Use expression index for derived column.
  10. Q60Use BRIN for time-series.
  11. Q61Replace LEFT JOIN with NOT EXISTS for anti-join.
  12. Q62Replace IN-subquery with JOIN.
  13. Q63Replace EXISTS with semi-join INNER.
  14. Q64Use LATERAL instead of correlated subquery.
  15. Q65Add ORDER BY for Merge Join.
  16. Q66Increase work_mem for big sort.
  17. Q67Pre-sort via index for Merge Join.
  18. Q68ANALYZE before heavy report.
  19. Q69Increase stats target for skewed columns.
  20. Q70Use extended stats for correlated columns.
  21. Q71Set enable_nestloop = off to force hash.
  22. Q72Set enable_hashjoin = off to force merge.
  23. Q73Tune random_page_cost for SSD.
  24. Q74Use parallel scan (max_parallel_workers).
  25. Q75Benchmark before/after with pg_stat_statements.

MATERIALIZED VIEWS

  1. Q76MV: per region per month revenue.
  2. Q77MV: per category top 10 products.
  3. Q78MV: customer 360deg.
  4. Q79MV: product 360deg.
  5. Q80MV: store 360deg.
  6. Q81REFRESH MATERIALIZED VIEW CONCURRENTLY.
  7. Q82Schedule MV refresh via cron.
  8. Q83Index a MV.
  9. Q84Combine multiple MVs.
  10. Q85Drop-and-rebuild MV when underlying changes.
  11. Q86MV vs continuous aggregate (TimescaleDB).
  12. Q87MV vs UI-side caching.
  13. Q88MV with WITH NO DATA.
  14. Q89MV depending on another MV.
  15. Q90Incremental MV refresh (PG18 hints).
  16. Q91Trigger-based recompute.
  17. Q92Partition the MV.
  18. Q93MV cost analysis.
  19. Q94MV staleness monitoring.
  20. Q95MV with row-level security.
  21. Q96MV for cross-schema reports.
  22. Q97MV for cross-database (via FDW).
  23. Q98MV for replication-friendly reports.
  24. Q99MV for "ad hoc" exec dashboard.
  25. Q100Build a 5-MV pipeline: facts -> daily -> weekly -> monthly -> exec.