Project: The Executive Summary Report

The Final Challenge

Congratulations! You've made it through **Phase 2**. You've gone from counting rows to building sophisticated business metrics.

Today, there is no lesson. There is only the **Mission**.

The Scenario

The board of directors is meeting. They need a single-page summary of your entire business health for the last quarter.

The Requirements

1. **Total Metrics**: Total Orders, Total Revenue, and Total Unique Customers.

2. **Averages**: Average Order Value (AOV).

3. **Segmentation**: Revenue broken down by the Top 5 countries.

4. **Trend**: Monthly revenue for the last 3 months.

The Solution Shell

-- Part 1: Total Metrics

SELECT

COUNT(*) AS total_orders,

SUM(order_amount) AS total_revenue,

COUNT(DISTINCT customer_id) AS unique_customers,

AVG(order_amount) AS avg_order_value

FROM orders

WHERE order_date BETWEEN '2025-10-01' AND '2025-12-31';

-- Part 2: Top 5 Countries

SELECT

country,

SUM(order_amount) AS country_revenue

FROM orders

WHERE order_date BETWEEN '2025-10-01' AND '2025-12-31'

GROUP BY country

ORDER BY country_revenue DESC

LIMIT 5;

-- Part 3: Monthly Trend

SELECT

DATE_TRUNC('month', order_date) AS month,

SUM(order_amount) AS monthly_revenue

FROM orders

WHERE order_date BETWEEN '2025-10-01' AND '2025-12-31'

GROUP BY DATE_TRUNC('month', order_date)

ORDER BY month;

What This Proves

These three queries use every concept we've covered in Phase 2: `COUNT`, `SUM`, `AVG`, `DISTINCT`, `WHERE`, `GROUP BY`, `HAVING` (implicit in the LIMIT), `ORDER BY`, and `DATE_TRUNC`.

Phase 3 Sneak Peek

You've mastered looking at one table at a time. But real businesses have hundreds of tables! Customers, Orders, Products, Payments...

**Phase 3: Joins**. We're going to link the entire database together.

Great work. See you in Phase 3!