Project: The First Audit—Phase 1 Final

The Graduation Challenge

Congratulations! You've made it through **Phase 1**. You've gone from "What is SQL?" to being able to fetch, filter, sort, and clean data.

Today, there is no "Lesson." There is only the **Mission**.

The Board of Directors is meeting in one hour. They need a "High Priority Customer Action List."

1. **Selection**: We need the `First Name`, `Last Name`, and `Email` of the customers.

2. **Logic**: They must be in the 'Gold' status tier **OR** have spent more than $5,000 in the last year.

3. **Geography**: We are ONLY interested in countries that are 'UK', 'USA', or 'Germany'.

4. **Cleaning**: The names must be in `UPPER` case and stripped of spaces. If the email is missing, it should say `'NO EMAIL PROVIDED'`.

5. **Presentation**: Sort the list by `spend` (highest first) and show only the Top 50 results.

Try to write this yourself before you look at the solution below!

SELECT

UPPER(TRIM(first_name)) AS first_name,

UPPER(TRIM(last_name)) AS last_name,

COALESCE(email, 'NO EMAIL PROVIDED') AS contact_info

FROM

customers

WHERE

(status = 'Gold' OR total_spend > 5000)

AND country IN ('UK', 'USA', 'Germany')

ORDER BY

total_spend DESC

LIMIT 50;

This single query uses almost every concept we’ve covered:

**SELECT/FROM**: The foundation.

**WHERE/AND/OR/IN**: The filtering logic.

**UPPER/TRIM/COALESCE**: The diagnostic cleaning.

**AS**: Professional labeling.

**ORDER BY/LIMIT**: The presentation touch.

You now know how to look at individual rows. But businesses care about "Totals."

*What is our total revenue?*

*Which country has the most users?*

*What is the average price of our products?*

**Phase 2: Aggregations & Reporting** starts tomorrow. We move from looking at the trees to seeing the entire forest.

Great work. See you in Phase 2!