Project: The Master Report

The Ultimate Challenge

Congratulations! You've mastered **Joins**, **Subqueries**, and **CTEs**. Today, you build the Master Report.

The Requirements

Build a single query (or CTE chain) that:

1. Shows every customer's name and email.

2. Their total number of orders.

3. Their total lifetime spend.

4. Their most recent order date.

5. The name of the product in their largest order.

The Solution Skeleton

WITH customer_stats AS (

SELECT

customer_id,

COUNT(*) AS order_count,

SUM(amount) AS total_spend,

MAX(order_date) AS last_order

FROM orders

GROUP BY customer_id

),

largest_order AS (

SELECT DISTINCT ON (customer_id) customer_id, product_id

FROM orders

ORDER BY customer_id, amount DESC

)

SELECT

c.name, c.email,

cs.order_count, cs.total_spend, cs.last_order,

p.name AS top_product

FROM customers c

JOIN customer_stats cs ON c.id = cs.customer_id

JOIN largest_order lo ON c.id = lo.customer_id

JOIN products p ON lo.product_id = p.id;

What's Next?

**Phase 5: Window Functions**. You'll learn `ROW_NUMBER`, `RANK`, `LAG`, `LEAD`, and running totals—the advanced analytics that separate a good analyst from a great one.

See you in Phase 5!