Capstone: revenue analysis

Study the pattern

Combine joins, aggregation, CTEs, and ranking in a business report. Predict the output before running the example.

WITH revenue AS (SELECT c.customer_id,c.name,COUNT(o.order_id) AS orders,SUM(o.amount) AS total_revenue FROM customers c JOIN orders o ON o.customer_id=c.customer_id GROUP BY c.customer_id,c.name) SELECT name,orders,total_revenue,RANK() OVER(ORDER BY total_revenue DESC) AS revenue_rank FROM revenue ORDER BY revenue_rank;

Explain it from memory

What does today's SQL feature do, and when would you use it?

Aggregate per customer in a CTE, then apply RANK().

Run SQL in your browser

Run the example, then modify one clause and predict how the result changes.

Aggregate per customer in a CTE, then apply RANK().

Consolidate the skill

• Run and inspect the output.
• Change one part and predict the difference.
• Return later and rewrite the query from memory.