DB Exercises
0
Track Guide & Problems

SQL CTEs & Recursive Hierarchies Practice

Break complex database queries into clean, maintainable modular pipelines. Calculate quarterly logo churn rates, net expansion bridges, and recursive organizational tree hierarchies with PostgreSQL CTEs.

Track Size
12 Practice Problems
Engine
PostgreSQL 16 WASM (<5ms)
Grading
Automated Row-by-Row Checks
Start Problem 1: Quarterly Logo Churn Rate with CTEs

Core Syntax & Mechanics

Key patterns and rules practiced in this track.

Modular Query Decomposition with CTEs

Creates named temporary result sets that exist only within the execution of the query. Improves readability over nested subqueries.

Syntax Pattern
WITH step_one AS (SELECT ...), step_two AS (SELECT ...) SELECT * FROM step_two
Example
WITH active_cohort AS (SELECT user_id, signup_date FROM users WHERE status = 'active'), order_summary AS (SELECT user_id, COUNT(*) AS orders FROM purchases GROUP BY user_id) SELECT a.user_id, COALESCE(o.orders, 0) FROM active_cohort a LEFT JOIN order_summary o ON a.user_id = o.user_id;

Recursive CTEs for Tree and Graph Traversals

Executes an initial anchor query and repeatedly joins against itself until no new rows are generated. Ideal for organizational trees and bill-of-materials.

Syntax Pattern
WITH RECURSIVE hierarchy AS (anchor_query UNION ALL recursive_query) SELECT * FROM hierarchy
Example
WITH RECURSIVE org_tree AS (SELECT emp_id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, e.manager_id, o.depth + 1 FROM employees e JOIN org_tree o ON e.manager_id = o.emp_id) SELECT * FROM org_tree;

Multi-Step Financial Bridges (Expansion vs Contraction)

Isolates starting and ending state snapshots to compute net retention, upgrades, downgrades, and logo churn.

Syntax Pattern
WITH mrr_start AS (...), mrr_end AS (...) SELECT ...
Example
WITH account_changes AS (SELECT account_id, old_mrr, new_mrr, (new_mrr - old_mrr) AS delta FROM subscription_events) SELECT SUM(delta) FILTER (WHERE delta > 0) AS expansion_mrr, SUM(ABS(delta)) FILTER (WHERE delta < 0) AS contraction_mrr FROM account_changes;

CTEs Exercises

Click any problem to open the interactive editor.

12 Problems
#1
Quarterly Logo Churn Rate with CTEsFREE

Single row summary with total_accounts, churned_accounts, and churn_rate_pct.

Practice
#2
Net New MRR Bridge: New, Expansion, and ContractionFREE

Single row summary showing new_mrr, expansion_mrr, contraction_mrr, and net_new_mrr.

Practice
#3
Customer 30-Day Repeat Repurchase RateFREE

Summary with total_customers, repeat_customers, and repeat_rate_pct within 30 days.

Practice
#4
30-Day Hospital Readmission Rate BenchmarkPRO

Single row summary showing total discharges, readmissions within 30 days, and readmission rate percentage.

Practice
#5
High Net Worth Liquidity SegmentationPRO

Summary of client count and total assets grouped by wealth tier.

Practice
#6
Recursive Employee Reporting Chain & Hierarchy DepthPRO

Employee hierarchy showing reporting depth distance from the executive level.

Practice
#7
Cumulative Route Leg Transit & Total DurationPRO

Summary of stops, total driving time, and shift violation status per route.

Practice
#8
Power User Identification via Cross-Module AdoptionPRO

Power users utilizing at least 3 distinct modules and logging >= 10 actions.

Practice
#9
Customer Lifetime Value Decile SegmentationPRO

Customer lifetime spend with quartile segmentation.

Practice
#10
Surgical Suite Block Time Utilization RatePRO

Surgical rooms with total surgery minutes and utilization percentage.

Practice
#11
Recursive Loan Balance Paydown SchedulePRO

Monthly paydown schedule showing remaining loan balance after 4 periods.

Practice
#12
Warehouse Inventory Stockout Risk & Reorder PriorityPRO

At-risk inventory items with 10 or fewer days of remaining stock.

Practice

Common Interview Mistakes

  • •Omitting the termination condition in a `WITH RECURSIVE` query, leading to an infinite loop.
  • •Using `UNION` instead of `UNION ALL` in recursive CTEs without realizing the deduplication overhead.
  • •Assuming CTEs are always materialized: In modern PostgreSQL (12+), non-recursive CTEs are inlined by default unless `MATERIALIZED` is explicitly stated.

Frequently asked questions about CTEs

What is the performance difference between a Subquery and a CTE?▸
In modern PostgreSQL (version 12+), CTEs are inlined into the query execution plan just like subqueries by default, meaning they have identical performance while offering vastly superior readability. If you want PostgreSQL to cache the CTE result, use `WITH cte_name AS MATERIALIZED (...)`.
How do you prevent infinite loops in a recursive CTE?▸
Ensure the recursive query joins on the parent-child relationship and include a depth limiter or cycle guard: `WHERE depth < 20`.
Can you reference an earlier CTE inside a later CTE in the same statement?▸
Yes. CTEs are evaluated sequentially, so any CTE defined later in the `WITH` clause can reference any previously defined CTE.

Practice CTEs now

Run queries directly in your browser against realistic tables.

Open Problem 1