Module 4 Lab: Complex Queries

You are officially writing advanced SQL. You understand how to nest queries within other queries, how to build temporary derived tables, and how to use Common Table Expressions (CTEs) to make your code modular and readable.

It’s time to put these skills to the test with a real-world business intelligence task.

The Scenario

The CEO of our E-Commerce platform has asked you to identify our “Whale” customers so we can send them a VIP gift basket.

They have defined a “Whale” as any customer whose total lifetime spending on the platform is greater than $150.

You have two tables:

users (contains id and name)
orders (contains id, user_id, and total_amount)

Building the Query

To solve this, you must construct a query using a CTE.

Here is your game plan:

Define a CTE using the WITH clause. Name the CTE user_spending.
Inside the CTE, query the orders table. Group the data by user_id and use SUM(total_amount) to calculate the total lifetime spend for each user. Alias that sum as lifetime_spend.
Now, write your main SELECT statement below the CTE.
Your main statement should SELECT the name from the users table, and the lifetime_spend from your CTE.
To do this, you must INNER JOIN the users table to your user_spending CTE using the user_id.
Finally, add a WHERE clause to your main query to filter only for rows where lifetime_spend > 150.

This is a very common pattern in data analytics: group and summarize data in a CTE, then join that summary back to a main table to get human-readable names.

Good luck! Once you’ve identified the Whales, you’ll be ready to dive into the powerful world of Module 5: Window Functions!