Practice & Assessment
Test your understanding of Subqueries and CTEs
Multiple Choice Questions
5SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders) returns 0 rows even though 5 customers have no orders. What is the most likely cause?
A correlated subquery references the outer query's row. What is the performance implication?
What must every subquery in a FROM clause have?
In a recursive CTE, what does the UNION ALL connect?
When is a CTE preferred over an equivalent inline subquery?
Coding Challenges
1Customer Cohort Analysis with CTEs
Given orders (id, customer_id, amount, status, created_at) and customers (id, name, signup_date, country): Using CTEs only (no inline subqueries), write a query that (1) identifies each customer's first order date as their cohort month, (2) calculates total revenue per cohort month, (3) counts how many customers in each cohort placed a second order within 30 days of their first, and (4) shows cohort retention rate as a percentage. Expected output: cohort_month, total_customers, retained_customers, retention_rate, cohort_revenue. Time estimate: 30 minutes.
Mini Project
Reporting Engine with CTEs
Build a full reporting suite for an e-commerce database using only CTEs (no subqueries in FROM, no correlated subqueries). Report 1 — Revenue funnel: starting with all orders, break down by status (pending, completed, refunded) with row counts and amounts. Report 2 — Customer segmentation: classify customers as 'champion' (>5 orders, >500 total), 'loyal' (>3 orders), 'new' (1 order in last 30 days), 'at_risk' (ordered before but not in 90 days). Report 3 — Product performance: for each product, show orders count, total revenue, rank by revenue, and comparison to category average using a CTE for category averages. Report 4 — Org chart traversal using recursive CTE on an employees table showing each employee's full management chain as a path string. Each report must be a separate SQL statement with all CTEs named descriptively.