TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 15

Subqueries and CTEs Combined: 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

SYNTHESIS / CONCEPTUAL

  1. Q1When to use subquery vs CTE?
  2. Q2When to use subquery vs JOIN?
  3. Q3When to use CTE vs derived table?
  4. Q4When to use LATERAL vs correlated?
  5. Q5When to use RECURSIVE vs iterative app code?
  6. Q6Pre-aggregate via CTE before JOIN - why?
  7. Q7EXPLAIN ANALYZE-driven choice.
  8. Q8Readability vs performance.
  9. Q9CTE for modularity.
  10. Q10Subquery for inline computation.
  11. Q11Combine subquery + CTE + window.
  12. Q12Combine LATERAL + CTE.
  13. Q13Combine RECURSIVE + CTE chain.
  14. Q14Combine subquery in SELECT + JOIN.
  15. Q15Combine HAVING subquery + window.
  16. Q16When to materialize.
  17. Q17When to inline.
  18. Q18When to use view vs MV.
  19. Q19When to refactor subquery to CTE.
  20. Q20When to refactor CTE to MV.
  21. Q21When to denormalize.
  22. Q22When to partition.
  23. Q23When to index.
  24. Q24When to write a stored procedure.
  25. Q25When to push computation to app vs DB.

KPI REPORTS

  1. Q26MRR (monthly recurring revenue) approximation.
  2. Q27ARR (annual recurring revenue).
  3. Q28CAC (customer acquisition cost).
  4. Q29LTV (customer lifetime value).
  5. Q30LTV / CAC ratio.
  6. Q31AOV (average order value).
  7. Q32CR (conversion rate).
  8. Q33Repeat purchase rate.
  9. Q34Refund rate.
  10. Q35Cancellation rate.
  11. Q36SLA compliance rate.
  12. Q37CSAT (customer satisfaction).
  13. Q38NPS (net promoter score).
  14. Q39FCR (first-contact resolution).
  15. Q40AHT (average handle time).
  16. Q41CPL (cost per lead).
  17. Q42CPC (cost per click).
  18. Q43ROAS (return on ad spend).
  19. Q44ROI (campaigns).
  20. Q45Inventory turnover.
  21. Q46Days sales outstanding (DSO).
  22. Q47Days inventory outstanding (DIO).
  23. Q48Gross margin.
  24. Q49Net margin.
  25. Q50Operating margin.

COHORT & FUNNEL REPORTS

  1. Q51Cohort retention by signup month.
  2. Q52Cohort retention by first-purchase month.
  3. Q53Cohort LTV.
  4. Q54Cohort churn.
  5. Q55Win-back rate.
  6. Q56Re-activation cohort.
  7. Q57Funnel: visitor -> signup.
  8. Q58Funnel: signup -> first order.
  9. Q59Funnel: cart -> checkout -> paid.
  10. Q60Funnel: campaign -> conversion.
  11. Q61Marketing funnel.
  12. Q62Sales funnel.
  13. Q63Support funnel: ticket -> in-progress -> resolved.
  14. Q64Onboarding funnel.
  15. Q65Trial -> paid funnel.
  16. Q66Drop-off detection.
  17. Q67Step-by-step time.
  18. Q68Bottleneck identification.
  19. Q69Cohort size variance.
  20. Q70Retention curve.
  21. Q71Stickiness DAU/MAU.
  22. Q72Engagement frequency.
  23. Q73Activity decay.
  24. Q74Reactivation.
  25. Q75Revenue cohort.

DASHBOARDS

  1. Q76Executive 1-pager.
  2. Q77Sales dashboard.
  3. Q78Marketing dashboard.
  4. Q79Operations dashboard.
  5. Q80Customer service dashboard.
  6. Q81HR dashboard.
  7. Q82Finance dashboard.
  8. Q83Inventory dashboard.
  9. Q84Supply chain dashboard.
  10. Q85Channel mix dashboard.
  11. Q86Geo dashboard.
  12. Q87Loyalty dashboard.
  13. Q88Mobile vs desktop dashboard.
  14. Q89New vs returning.
  15. Q90Subscription dashboard.
  16. Q91Fraud dashboard.
  17. Q92Compliance dashboard.
  18. Q93SLA dashboard.
  19. Q94Engagement dashboard.
  20. Q95Cohort dashboard.
  21. Q96Anomaly dashboard.
  22. Q97Performance dashboard.
  23. Q98Health dashboard.
  24. Q99Quality dashboard.
  25. Q100Master 100-metric dashboard.

Combined ideas, multi-step thinking

COHORT DEEP

  1. Q1Signup cohort retention monthly.
  2. Q2First-purchase cohort retention monthly.
  3. Q3Cohort LTV by month.
  4. Q4Cohort revenue.
  5. Q5Cohort orders.
  6. Q6Cohort by channel.
  7. Q7Cohort by campaign.
  8. Q8Cohort by region.
  9. Q9Cohort by tier.
  10. Q10Cohort by device.
  11. Q11Cohort by acquisition source.
  12. Q12Cohort retention curve.
  13. Q13Cohort drop-off.
  14. Q14Cohort lifetime curve.
  15. Q15Cohort churn rate.
  16. Q16Cohort reactivation.
  17. Q17Cohort revenue per cohort customer.
  18. Q18Cohort AOV.
  19. Q19Cohort frequency.
  20. Q20Cohort recency.
  21. Q21Cohort engagement score.
  22. Q22Cohort comparison year-over-year.
  23. Q23Cohort comparison quarter-over-quarter.
  24. Q24Cohort visualization data.
  25. Q25Cohort heatmap data.

FUNNEL DEEP

  1. Q26Marketing funnel: impression -> click -> visit -> signup -> order.
  2. Q27Sales funnel: lead -> opportunity -> quote -> close.
  3. Q28Onboarding funnel: signup -> activation -> first action.
  4. Q29Checkout funnel: cart -> address -> payment -> confirm.
  5. Q30Subscription funnel: trial -> engaged -> paid.
  6. Q31Reactivation funnel: dormant -> click -> order.
  7. Q32Support funnel: ticket -> assigned -> in-progress -> resolved.
  8. Q33Refund funnel: request -> review -> approve -> issue.
  9. Q34Returns funnel: initiate -> ship-back -> received -> refund.
  10. Q35Funnel drop-off per step.
  11. Q36Funnel time per step.
  12. Q37Funnel conversion per step.
  13. Q38Funnel bottleneck identification.
  14. Q39Multi-touch attribution.
  15. Q40First-touch attribution.
  16. Q41Last-touch attribution.
  17. Q42Linear-touch attribution.
  18. Q43Time-decay attribution.
  19. Q44Position-based attribution.
  20. Q45Path analysis.
  21. Q46Common paths.
  22. Q47Rare paths.
  23. Q48Path with most revenue.
  24. Q49Path with shortest time.
  25. Q50Funnel by cohort.

RFM / NPS / Segments

  1. Q51RFM 5x5x5.
  2. Q52RFM cluster names.
  3. Q53RFM next-best-action.
  4. Q54NPS overall.
  5. Q55NPS per region.
  6. Q56NPS per product.
  7. Q57NPS per tier.
  8. Q58NPS trend month-over-month.
  9. Q59Promoter count.
  10. Q60Detractor count.
  11. Q61Passive count.
  12. Q62CSAT overall.
  13. Q63CSAT per agent.
  14. Q64CSAT per category.
  15. Q65Segment: VIP.
  16. Q66Segment: Loyal.
  17. Q67Segment: At-Risk.
  18. Q68Segment: Lost.
  19. Q69Segment: New.
  20. Q70Segment migration.
  21. Q71Segment LTV.
  22. Q72Segment frequency.
  23. Q73Segment retention.
  24. Q74Segment ROI of campaigns.
  25. Q75Segment next-best-product.

MULTI-SOURCE DASHBOARDS

  1. Q76Customer 360deg via 6 sources.
  2. Q77Product 360deg via 5 sources.
  3. Q78Store 360deg via 5 sources.
  4. Q79Region 360deg via 4 sources.
  5. Q80Campaign 360deg via 4 sources.
  6. Q81Supplier 360deg.
  7. Q82Brand 360deg.
  8. Q83Tier 360deg.
  9. Q84Agent 360deg.
  10. Q85Courier 360deg.
  11. Q86Warehouse 360deg.
  12. Q87Dept 360deg.
  13. Q88Employee 360deg.
  14. Q89Category 360deg.
  15. Q90Channel 360deg.
  16. Q91Executive scorecard 50 KPIs.
  17. Q92Board pack 20 KPIs.
  18. Q93Daily ops summary.
  19. Q94Weekly leadership review.
  20. Q95Monthly P&L sketch.
  21. Q96Quarterly review.
  22. Q97Annual report data.
  23. Q98Investor metrics.
  24. Q99Audit report.
  25. Q100Master 100-metric weekly dashboard.

Interview grade, edge cases

HIERARCHICAL REPORTS

  1. Q1Category breadcrumb with revenue rollup.
  2. Q2Region rollup.
  3. Q3Org hierarchy rollup.
  4. Q4Account hierarchy.
  5. Q5BOM with cost rollup.
  6. Q6Folder size rollup.
  7. Q7Comment thread rollup.
  8. Q8Approval chain rollup.
  9. Q9Tax bracket.
  10. Q10Discount stack.
  11. Q11Loyalty referral.
  12. Q12Affiliate chain.
  13. Q13Sales territory.
  14. Q14Distributor network.
  15. Q15Refer-a-friend.
  16. Q16Permission inheritance.
  17. Q17Geographic hierarchy.
  18. Q18Brand hierarchy.
  19. Q19Product family.
  20. Q20Supply chain.
  21. Q21Customer referral tree.
  22. Q22Campaign hierarchy.
  23. Q23Promotion stack.
  24. Q24Subscription tree.
  25. Q25Hierarchical KPI rollup.

FUNNEL + ATTRIBUTION

  1. Q26Multi-touch attribution per campaign.
  2. Q27First-touch attribution.
  3. Q28Last-touch attribution.
  4. Q29Linear-touch.
  5. Q30Time-decay.
  6. Q31Position-based.
  7. Q32Attribution comparison.
  8. Q33Funnel by source.
  9. Q34Funnel by medium.
  10. Q35Funnel by campaign.
  11. Q36Funnel by region.
  12. Q37Funnel by device.
  13. Q38Funnel by tier.
  14. Q39Funnel time-to-conversion.
  15. Q40Funnel drop-off rate.
  16. Q41Funnel bottleneck.
  17. Q42Funnel ROI per campaign.
  18. Q43Funnel CAC per campaign.
  19. Q44Funnel LTV per campaign.
  20. Q45Funnel cohort split.
  21. Q46Funnel anomaly detect.
  22. Q47Funnel A/B test.
  23. Q48Funnel optimization.
  24. Q49Funnel sequence sampling.
  25. Q50Funnel path analysis.

MULTI-STAGE ETL

  1. Q51Customer master ETL.
  2. Q52Order fact ETL.
  3. Q53Product master ETL.
  4. Q54Inventory snapshot ETL.
  5. Q55Campaign attribution ETL.
  6. Q56Loyalty event ETL.
  7. Q57Web event ETL.
  8. Q58Support ticket ETL.
  9. Q59Call center ETL.
  10. Q60Audit trail ETL.
  11. Q61ETL: SCD type 1.
  12. Q62ETL: SCD type 2.
  13. Q63ETL: SCD type 3.
  14. Q64ETL: change data capture.
  15. Q65ETL: incremental load.
  16. Q66ETL: full load.
  17. Q67ETL: backfill.
  18. Q68ETL: late-arriving data.
  19. Q69ETL: dedupe.
  20. Q70ETL: cleanse.
  21. Q71ETL: enrich.
  22. Q72ETL: aggregate.
  23. Q73ETL: publish.
  24. Q74ETL: archive.
  25. Q75ETL: error handling.

REAL BI REPORTS

  1. Q76Executive 1-pager (50 metrics).
  2. Q77Board pack.
  3. Q78Daily ops summary.
  4. Q79Weekly leadership review.
  5. Q80Monthly P&L.
  6. Q81Quarterly review.
  7. Q82Annual report.
  8. Q83Investor metrics.
  9. Q84Cohort lifetime value.
  10. Q85RFM segment dashboard.
  11. Q86NPS dashboard.
  12. Q87CSAT dashboard.
  13. Q88Churn risk dashboard.
  14. Q89Engagement dashboard.
  15. Q90Loyalty dashboard.
  16. Q91Marketing ROI.
  17. Q92Sales velocity.
  18. Q93Inventory turnover.
  19. Q94Supplier scorecard.
  20. Q95Courier scorecard.
  21. Q96Agent leaderboard.
  22. Q97Store leaderboard.
  23. Q98Region scorecard.
  24. Q99Brand scorecard.
  25. Q100Master report: combining RECURSIVE + LATERAL + CTE + window + set ops.

Production scenarios, optimisation

MULTI-DOMAIN MEGA

  1. Q1Customer 360deg with 50 metrics.
  2. Q2Product 360deg with 40 metrics.
  3. Q3Store 360deg with 30 metrics.
  4. Q4Region 360deg with 25 metrics.
  5. Q5Campaign 360deg with 20 metrics.
  6. Q6Supplier 360deg.
  7. Q7Brand 360deg.
  8. Q8Tier 360deg.
  9. Q9Agent 360deg.
  10. Q10Courier 360deg.
  11. Q11Warehouse 360deg.
  12. Q12Dept 360deg.
  13. Q13Employee 360deg.
  14. Q14Category 360deg.
  15. Q15Channel 360deg.
  16. Q16Geographic 360deg.
  17. Q17Loyalty 360deg.
  18. Q18Subscription 360deg.
  19. Q19Web session 360deg.
  20. Q20Ticket 360deg.
  21. Q21Call 360deg.
  22. Q22Returns 360deg.
  23. Q23Refunds 360deg.
  24. Q24Audit 360deg.
  25. Q25Compliance 360deg.

COHORT / RFM / NPS MEGA

  1. Q26Cohort retention matrix (24 months x all cohorts).
  2. Q27Cohort LTV curves.
  3. Q28RFM 5x5x5 with segment names.
  4. Q29RFM migration.
  5. Q30RFM revenue per segment.
  6. Q31NPS by 10 dimensions.
  7. Q32NPS trend.
  8. Q33NPS comparison year-over-year.
  9. Q34CSAT by 10 dimensions.
  10. Q35CSAT root-cause analysis.
  11. Q36Funnel by cohort.
  12. Q37Funnel by segment.
  13. Q38Funnel by channel.
  14. Q39Funnel by campaign.
  15. Q40Funnel by device.
  16. Q41Multi-touch attribution full.
  17. Q42Attribution by channel.
  18. Q43Attribution by source.
  19. Q44Attribution by medium.
  20. Q45ROAS per campaign.
  21. Q46ROAS per platform.
  22. Q47Cost per acquisition by channel.
  23. Q48Lifetime ROI per channel.
  24. Q49Win-back ROI.
  25. Q50Reactivation ROI.

ETL PIPELINES

  1. Q51Customer master full ETL.
  2. Q52Order fact full ETL.
  3. Q53Inventory snapshot full ETL.
  4. Q54Campaign attribution full ETL.
  5. Q55Web event full ETL.
  6. Q56Loyalty event full ETL.
  7. Q57Ticket full ETL.
  8. Q58Call full ETL.
  9. Q59Audit full ETL.
  10. Q60SCD type 2 customer.
  11. Q61SCD type 2 product.
  12. Q62SCD type 2 store.
  13. Q63SCD type 2 employee.
  14. Q64SCD type 2 supplier.
  15. Q65CDC pipeline customer.
  16. Q66CDC pipeline order.
  17. Q67CDC pipeline inventory.
  18. Q68CDC pipeline campaign.
  19. Q69Incremental load customer.
  20. Q70Incremental load order.
  21. Q71Backfill customer.
  22. Q72Backfill order.
  23. Q73Late-arriving fact handling.
  24. Q74Deduplication pipeline.
  25. Q75Cleansing pipeline.

CAPSTONE

  1. Q76Master report: every techniques combined.
  2. Q77Executive board pack data.
  3. Q78Investor metrics.
  4. Q79Quarterly review data.
  5. Q80Annual report data.
  6. Q81KPI North Star Metric.
  7. Q82Health score per customer.
  8. Q83Churn predictor input.
  9. Q84Fraud detector input.
  10. Q85Personalization input.
  11. Q86Recommendation engine input.
  12. Q87Demand forecast input.
  13. Q88Inventory replenishment input.
  14. Q89Marketing budget allocation.
  15. Q90Sales territory rebalance.
  16. Q91HR rebalance.
  17. Q92Loyalty tier rebalance.
  18. Q93Pricing optimization.
  19. Q94Promotion optimization.
  20. Q95Channel mix optimization.
  21. Q96Customer segment migration.
  22. Q97Product portfolio review.
  23. Q98Supply chain optimization.
  24. Q99End-of-week-3 review query.
  25. Q100Mega query: RECURSIVE + LATERAL + CTE + window + set ops + DML in 1 statement.