TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 13

CTEs: 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

CTE - CONCEPTUAL

  1. Q1What is a CTE?
  2. Q2Compare CTE vs derived table.
  3. Q3Why are CTEs called "named subqueries"?
  4. Q4Can a CTE be referenced multiple times?
  5. Q5CTE scope - only the current SQL statement.
  6. Q6When does Postgres inline CTE vs materialize?
  7. Q7MATERIALIZED keyword (PG12+).
  8. Q8NOT MATERIALIZED keyword.
  9. Q9Multiple CTEs separated by comma.
  10. Q10CTE referencing earlier CTE.
  11. Q11Recursive CTE syntax (preview).
  12. Q12CTE vs view - when each.
  13. Q13CTE vs materialized view.
  14. Q14CTE inside view.
  15. Q15CTE inside function.
  16. Q16CTE in DML (INSERT/UPDATE/DELETE).
  17. Q17CTE with column list aliasing.
  18. Q18CTE with derived computation.
  19. Q19CTE with GROUP BY.
  20. Q20CTE with HAVING.
  21. Q21CTE with ORDER BY + LIMIT.
  22. Q22CTE with window function.
  23. Q23CTE with JOIN inside.
  24. Q24CTE with UNION.
  25. Q25CTE with INTERSECT/EXCEPT.

SINGLE-CTE QUERIES

  1. Q26Top 10 customers by spend (CTE).
  2. Q27Top 10 products by units sold (CTE).
  3. Q28Top 10 stores by revenue (CTE).
  4. Q29Top 5 brands (CTE).
  5. Q30Top 5 categories (CTE).
  6. Q31Per region orders count (CTE).
  7. Q32Per dept employee count (CTE).
  8. Q33Per tier member count (CTE).
  9. Q34Per warehouse SUM(qty) (CTE).
  10. Q35Per courier shipment count (CTE).
  11. Q36Customers with > 10 orders (CTE + filter).
  12. Q37Products with > 5 reviews (CTE + filter).
  13. Q38Above-average orders (CTE + comparison).
  14. Q39Below-median orders (CTE + percentile).
  15. Q40Outliers (CTE + z-score).
  16. Q41Latest order per customer (CTE + DISTINCT ON).
  17. Q42Latest review per product (CTE + ROW_NUMBER).
  18. Q43Latest pay_slip per employee (CTE).
  19. Q44Latest snapshot per warehouse-product (CTE).
  20. Q45Latest shipment per courier (CTE).
  21. Q46First-order date per customer (CTE).
  22. Q47First-review date per product (CTE).
  23. Q48Active customers in last 30 days (CTE).
  24. Q49New customers this month (CTE).
  25. Q50Churned customers (CTE).

MULTI-CTE QUERIES

  1. Q51orders_agg + revs_agg -> JOIN customer.
  2. Q52orders_agg + tickets_agg -> customer 360deg.
  3. Q53spend_per_cust + tier_lookup -> segment.
  4. Q54revenue_per_region + region_dim -> label.
  5. Q55revenue_per_brand + brand_dim -> label.
  6. Q56inventory_levels + product_dim -> label.
  7. Q57agent_workload + employee_dim -> label.
  8. Q58courier_perf + courier_dim.
  9. Q59cohort_signup + cohort_first_order.
  10. Q60churned_customers + win_back_targets.
  11. Q61monthly_rev + monthly_cost -> profit.
  12. Q62monthly_orders + monthly_churn -> growth.
  13. Q63top_products + their_brands -> cross.
  14. Q64top_customers + their_orders -> cross.
  15. Q65top_brands + their_categories -> cross.
  16. Q66agent_stats + customer_stats -> match.
  17. Q67supplier_stats + product_stats -> cross.
  18. Q68warehouse_stats + inventory_stats -> join.
  19. Q69region_stats + city_stats -> roll up.
  20. Q70tier_stats + lifecycle_stats -> cross.
  21. Q713-CTE chain: A -> B -> C.
  22. Q724-CTE chain: A -> B -> C -> D.
  23. Q735-CTE chain.
  24. Q74Chain referencing each previous.
  25. Q75Chain with UNION across CTEs.

WRITEABLE CTEs (DML)

  1. Q76WITH d AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM d.
  2. Q77WITH ins AS (INSERT ...) SELECT * FROM ins.
  3. Q78WITH upd AS (UPDATE ...) SELECT * FROM upd.
  4. Q79Multi-step: WITH a (INSERT) WITH b (DELETE) UNION ALL.
  5. Q80CTE + RETURNING captures id.
  6. Q81CTE-DML chain: customer -> loyalty member.
  7. Q82CTE-DML chain: order -> audit_log.
  8. Q83CTE-DML chain: refund -> archive.
  9. Q84CTE-DML chain: signup -> welcome email.
  10. Q85CTE-DML chain: cancel -> audit.
  11. Q86CTE for batched DELETE.
  12. Q87CTE for batched UPDATE.
  13. Q88CTE for batched INSERT.
  14. Q89CTE for dedup INSERT.
  15. Q90CTE for merge-style UPSERT.
  16. Q91CTE for soft-delete + audit.
  17. Q92CTE for state transition + audit.
  18. Q93CTE for tier upgrade.
  19. Q94CTE for loyalty redemption.
  20. Q95CTE for fraud flag + ticket creation.
  21. Q96CTE for stock decrement + audit.
  22. Q97CTE for shipment + delivery log.
  23. Q98CTE for return + refund + audit.
  24. Q99CTE for campaign close + final attribution.
  25. Q100Complete order pipeline: order -> payment -> inventory -> shipment via CTEs.

Combined ideas, multi-step thinking

DEEPER CONCEPTUAL

  1. Q1When does inlining help vs hurt?
  2. Q2MATERIALIZED for re-use cost.
  3. Q3When does the planner re-execute a CTE?
  4. Q4CTE + index - when index applies.
  5. Q5CTE forcing serialization (preventing optimizer pushdown).
  6. Q6Chain of CTEs vs nested subqueries - readability.
  7. Q7CTE + EXPLAIN ANALYZE.
  8. Q8CTE + auto_explain.
  9. Q9CTE in stored procedure.
  10. Q10CTE with parameter binding.
  11. Q11CTE inside a view.
  12. Q12CTE inside a window.
  13. Q13CTE inside RECURSIVE.
  14. Q14CTE producing array.
  15. Q15CTE producing JSON.
  16. Q16CTE for set operations.
  17. Q17CTE for pivot.
  18. Q18CTE for unpivot.
  19. Q19CTE for cleansing pipeline.
  20. Q20CTE for cohort analysis.
  21. Q21CTE for RFM.
  22. Q22CTE for NPS.
  23. Q23CTE for funnel.
  24. Q24CTE for anomaly detection.
  25. Q25CTE for time-series rollup.

CHAIN PATTERNS

  1. Q263-CTE chain: facts -> daily -> weekly.
  2. Q274-CTE chain: facts -> daily -> weekly -> monthly.
  3. Q285-CTE chain.
  4. Q29Branching: 2 CTEs feeding 1 final.
  5. Q30Diamond: 1 source -> 2 branches -> 1 final.
  6. Q31Chain with conditional CTE.
  7. Q32Chain with rollup.
  8. Q33Chain with cube.
  9. Q34Chain with window inside one CTE.
  10. Q35Chain with LATERAL inside one CTE.
  11. Q36Chain with set ops.
  12. Q37Chain with PERCENTILE.
  13. Q38Chain with FILTER.
  14. Q39Chain with TYPE casts.
  15. Q40Chain with NULL handling.
  16. Q41Chain with idempotent steps.
  17. Q42Chain with reusable lookup CTE.
  18. Q43Chain with reusable dim CTE.
  19. Q44Chain producing dashboard rows.
  20. Q45Chain producing JSON output.
  21. Q46Chain producing CSV-like output.
  22. Q47Chain for "anomaly + alert".
  23. Q48Chain for "ranked + filtered".
  24. Q49Chain for "cohort + retention".
  25. Q50Chain for "RFM + segment".

CTE + WINDOW

  1. Q51CTE with ROW_NUMBER.
  2. Q52CTE with RANK.
  3. Q53CTE with DENSE_RANK.
  4. Q54CTE with NTILE.
  5. Q55CTE with LAG/LEAD.
  6. Q56CTE with running SUM.
  7. Q57CTE with running AVG.
  8. Q58CTE with PERCENTILE_CONT.
  9. Q59CTE with FIRST_VALUE.
  10. Q60CTE with LAST_VALUE.
  11. Q61CTE filtering window result.
  12. Q62CTE with multiple windows.
  13. Q63CTE with PARTITION + ORDER.
  14. Q64CTE with named window.
  15. Q65CTE with frame specification.
  16. Q66CTE + window for gaps-and-islands.
  17. Q67CTE + window for runs.
  18. Q68CTE + window for sessionization.
  19. Q69CTE + window for cohort.
  20. Q70CTE + window for cumulative.
  21. Q71CTE + window for moving avg.
  22. Q72CTE + window for YoY.
  23. Q73CTE + window for top-N.
  24. Q74CTE + window for percentile rank.
  25. Q75CTE + window for "delta vs first".

CTE FOR ETL

  1. Q76ETL: staging -> cleaned -> loaded.
  2. Q77ETL: dedupe via DISTINCT ON CTE.
  3. Q78ETL: type-cast errors via CTE.
  4. Q79ETL: null-fix via CTE.
  5. Q80ETL: outlier removal via CTE.
  6. Q81ETL: enrichment via JOIN CTE.
  7. Q82ETL: aggregation via CTE.
  8. Q83ETL: split rows via CTE.
  9. Q84ETL: merge rows via CTE.
  10. Q85ETL: cross-source UNION via CTE.
  11. Q86ETL: anti-join via CTE.
  12. Q87ETL: insert + log via CTE.
  13. Q88ETL: update + log via CTE.
  14. Q89ETL: delete + archive via CTE.
  15. Q90ETL: upsert via CTE.
  16. Q91ETL: incremental load via CTE.
  17. Q92ETL: change capture via CTE.
  18. Q93ETL: snapshot via CTE.
  19. Q94ETL: dimension load via CTE.
  20. Q95ETL: fact load via CTE.
  21. Q96ETL: SCD type 2 via CTE.
  22. Q97ETL: CDC stream simulation via CTE.
  23. Q98ETL: data quality flagging via CTE.
  24. Q99ETL: audit table population via CTE.
  25. Q100ETL: 10-step pipeline via CTE chain.

Interview grade, edge cases

INLINING vs MATERIALIZED

  1. Q1Default inlining behavior (PG12+).
  2. Q2Force inline with NOT MATERIALIZED.
  3. Q3Force materialize with MATERIALIZED.
  4. Q4When inlining wins.
  5. Q5When materialization wins.
  6. Q6CTE used twice - auto materialize?
  7. Q7CTE used 5 times - definitely materialize.
  8. Q8CTE with side effects - always materialized.
  9. Q9CTE with VOLATILE function - always materialized.
  10. Q10CTE with random - caveat.
  11. Q11CTE with now() - caveat.
  12. Q12CTE with LIMIT - may inline if used once.
  13. Q13CTE with DML - always materialized.
  14. Q14CTE in recursive - always materialized.
  15. Q15CTE inside view - materialization.
  16. Q16CTE inside function - materialization.
  17. Q17CTE in prepared stmt - materialization.
  18. Q18CTE with parameter - materialization.
  19. Q19EXPLAIN ANALYZE CTE - read "CTE Scan" node.
  20. Q20Compare plans inline vs materialized.
  21. Q21CTE with filter - pushdown opportunity.
  22. Q22CTE with predicate - pushdown vs not.
  23. Q23CTE with ORDER BY - preserved.
  24. Q24CTE with LIMIT - preserved (LIMIT pushdown).
  25. Q25CTE with index hints? Not in Postgres.

MATERIALIZED MV vs CTE

  1. Q26CTE vs MV - when each.
  2. Q27MV for repeated dashboard query.
  3. Q28CTE for one-off.
  4. Q29MV refresh schedule.
  5. Q30CTE in production view.
  6. Q31MV in production view.
  7. Q32CTE vs subquery - refactor decision.
  8. Q33CTE for testability.
  9. Q34CTE for readability.
  10. Q35MV for performance.
  11. Q36CTE in EXPLAIN with MATERIALIZED.
  12. Q37CTE with hash table size.
  13. Q38CTE with sort spill.
  14. Q39Spilled CTE warning - work_mem?
  15. Q40Audit CTE size via pg_stat_statements.
  16. Q41Compare CTE+window vs MV+index.
  17. Q42Mixed: CTE on top of MV.
  18. Q43MV containing CTE-style query.
  19. Q44MV refresh with CONCURRENTLY.
  20. Q45MV index.
  21. Q46MV partitioning.
  22. Q47MV stale check.
  23. Q48MV vs continuous aggregate.
  24. Q49MV in BI dashboard.
  25. Q50MV in reporting layer.

ETL PRODUCTION

  1. Q51Multi-stage ETL with audit at each step.
  2. Q52Incremental load with checkpoint.
  3. Q53CDC consumer with state.
  4. Q54Stream-to-batch ETL.
  5. Q55Slowly Changing Dimension type 2.
  6. Q56Snapshot generation.
  7. Q57Dimension load.
  8. Q58Fact load.
  9. Q59Data quality stage.
  10. Q60Deduplication stage.
  11. Q61Cleanse stage.
  12. Q62Enrich stage.
  13. Q63Aggregate stage.
  14. Q64Publish stage.
  15. Q65Archive stage.
  16. Q66ETL retry-safe.
  17. Q67ETL idempotent.
  18. Q68ETL parallel-safe.
  19. Q69ETL with FOR UPDATE SKIP LOCKED.
  20. Q70ETL with advisory lock.
  21. Q71ETL with batched DELETE.
  22. Q72ETL with batched INSERT.
  23. Q73ETL with rolling window.
  24. Q74ETL with late-arriving data.
  25. Q75ETL with backfill.

REAL REPORTS

  1. Q76Cohort retention via CTE chain.
  2. Q77RFM segmentation via CTE chain.
  3. Q78Funnel analysis via CTE.
  4. Q79NPS via CTE.
  5. Q80Churn analysis via CTE.
  6. Q81Top-N per group via CTE.
  7. Q82Multi-metric leaderboard.
  8. Q83Pivot via CTE.
  9. Q84Unpivot via CTE.
  10. Q85Cross-tab via CTE.
  11. Q86Time-series rollup via CTE.
  12. Q87Year-over-year via CTE.
  13. Q88Quarter-over-quarter via CTE.
  14. Q89Month-over-month via CTE.
  15. Q90Day-over-day via CTE.
  16. Q91Cohort lifetime value via CTE.
  17. Q92Customer 360deg via CTE.
  18. Q93Product 360deg via CTE.
  19. Q94Store 360deg via CTE.
  20. Q95Region 360deg via CTE.
  21. Q96Campaign 360deg via CTE.
  22. Q97Supplier 360deg via CTE.
  23. Q98Brand 360deg via CTE.
  24. Q99Executive 1-pager via CTE.
  25. Q100Master CTE-chain report - 20-step pipeline.

Production scenarios, optimisation

MEGA ETL PIPELINES

  1. Q120-step customer ETL: raw -> cleanse -> enrich -> segment -> load.
  2. Q220-step order ETL.
  3. Q320-step product ETL.
  4. Q420-step inventory ETL.
  5. Q520-step campaign attribution ETL.
  6. Q6ETL: dedup + cleanse + enrich + load + audit.
  7. Q7ETL with SCD type 1.
  8. Q8ETL with SCD type 2.
  9. Q9ETL with SCD type 3.
  10. Q10ETL with hybrid SCD.
  11. Q11ETL with late-arriving fact.
  12. Q12ETL with conformed dimension.
  13. Q13ETL with degenerate dimension.
  14. Q14ETL with junk dimension.
  15. Q15ETL with role-playing dimension.
  16. Q16ETL with snowflake schema.
  17. Q17ETL with star schema.
  18. Q18ETL with hash-partitioned target.
  19. Q19ETL with range-partitioned target.
  20. Q20ETL with list-partitioned target.
  21. Q21ETL with parallel inserts via TEMP.
  22. Q22ETL with deferred constraints.
  23. Q23ETL with bulk merge.
  24. Q24ETL with retry-safe upsert.
  25. Q25ETL with checkpoint + resume.

MULTI-SOURCE CTE

  1. Q26CTE combining sales + reviews + tickets + calls.
  2. Q27CTE combining marketing + sales + customer.
  3. Q28CTE combining inventory + supply + sales.
  4. Q29CTE combining HR + sales + customer.
  5. Q30CTE combining loyalty + sales + customer.
  6. Q31CTE combining web_events + orders + customer.
  7. Q32CTE combining audit + activity.
  8. Q33CTE combining all 16 schemas (RetailMart V3 wide).
  9. Q34CTE cross-DB via postgres_fdw.
  10. Q35CTE cross-cluster.
  11. Q36CTE for "customer interaction timeline".
  12. Q37CTE for "product event log".
  13. Q38CTE for "store activity feed".
  14. Q39CTE for "campaign attribution funnel".
  15. Q40CTE for "supplier scorecard".
  16. Q41CTE for "courier perf".
  17. Q42CTE for "warehouse health".
  18. Q43CTE for "agent productivity".
  19. Q44CTE for "tier migration".
  20. Q45CTE for "fraud watchlist".
  21. Q46CTE for "churn signals".
  22. Q47CTE for "win-back targets".
  23. Q48CTE for "upsell candidates".
  24. Q49CTE for "cross-sell candidates".
  25. Q50CTE for "VIP retention".

MEGA REPORTS

  1. Q51Executive 1-pager (50 metrics) via CTE chain.
  2. Q52Board summary (10 KPIs).
  3. Q53Daily ops dashboard.
  4. Q54Weekly leadership scorecard.
  5. Q55Monthly board pack.
  6. Q56Quarterly review.
  7. Q57Annual report.
  8. Q58Investor deck data via CTE.
  9. Q59Cohort retention pyramid.
  10. Q60RFM 5x5x5 matrix.
  11. Q61Funnel with drop-off rates.
  12. Q62NPS by 5 dimensions.
  13. Q63Customer 360deg dashboard.
  14. Q64Product 360deg.
  15. Q65Store 360deg.
  16. Q66Region 360deg.
  17. Q67Brand 360deg.
  18. Q68Supplier 360deg.
  19. Q69Campaign 360deg.
  20. Q70Channel 360deg.
  21. Q71Anomaly digest.
  22. Q72Sales velocity report.
  23. Q73Inventory turnover report.
  24. Q74Support SLA report.
  25. Q75Marketing ROI report.

PIPELINE ARCHITECTURE

  1. Q76Composable CTE library.
  2. Q77Reusable dimension CTE.
  3. Q78Reusable fact CTE.
  4. Q79Versioned CTE pipeline.
  5. Q80Materialized view + CTE composition.
  6. Q81Incremental MV + CTE refresh logic.
  7. Q82Cron-driven CTE pipeline.
  8. Q83Trigger-driven CTE pipeline.
  9. Q84App-driven CTE pipeline.
  10. Q85NOTIFY/LISTEN with CTE.
  11. Q86Outbox pattern + CTE.
  12. Q87CDC consumer + CTE.
  13. Q88SCD type 6 (hybrid) via CTE.
  14. Q89Time-travel queries via CTE + valid_from/valid_to.
  15. Q90Soft-delete with audit via CTE.
  16. Q91Multi-tenant CTE.
  17. Q92Sharded CTE.
  18. Q93Read-replica-friendly CTE.
  19. Q94CTE in stored procedure.
  20. Q95CTE in function with parameters.
  21. Q96CTE with composite type return.
  22. Q97CTE with TABLE return.
  23. Q98CTE with SETOF return.
  24. Q99CTE composition + pg_cron.
  25. Q100Master: 50-step CTE-MV-cron-NOTIFY pipeline.