SQL practice: CTEs: Naming Your Steps

Use a CTE to find the top 3 customers by total spend.

Use a CTE for total quantity sold per product, then show only products above 150.

Use a CTE to calculate revenue per branch, then show only branches above the average branch revenue.

Use a CTE to count orders per customer, then show only customers with more than 2.

Chain two CTEs: one for revenue per product, one that adds a rank, then show the top 5.

Use a CTE to work out each employee's total handled revenue, then join it to employees to show names.

Use a CTE for total spend per customer, then show the overall average of those totals.

Rewrite this nested query as a CTE: SELECT * FROM (SELECT role, AVG(salary) AS avg_pay FROM employees GROUP BY role) t WHERE avg_pay > 45000;

Chain three CTEs: revenue per branch → add a rank → join to branch names, showing only the bottom 3.

Find customers whose total spend is above the average customer's spend. It fails with 'relation "customer_totals" does not exist'. Fix it.