Capstone Lab: Real-World Scenario

Congratulations on making it to the final lab of the SQL Curriculum!

You’ve learned how to query data, filter it, group it, join tables, write subqueries, use window functions, modify records, design schemas, and analyze query performance. You now possess the core database skills of a professional backend software engineer.

The Final Challenge

The Chief Financial Officer (CFO) of our E-Commerce company urgently needs a report for the quarterly board meeting.

They need a summary of total realized revenue, broken down by individual customer. Realized revenue means we only care about orders that have actually shipped; cancelled or pending orders do not count.

They have provided you with four fully populated tables: users, orders, order_items, and products.

The Requirements

Construct a single query that returns exactly three columns:

user_full_name: The user’s first and last name combined with a space in the middle. The CFO insists this column must be entirely in UPPERCASE text.
customer_tier: A calculated text column. If the user’s is_premium flag is true, output 'VIP'. Otherwise, output 'Standard'. (Hint: Use CASE WHEN).
total_shipped_spend: The total monetary value of all the items they have purchased in orders where the order status is 'shipped'.

Final constraints:

You will need to join users, orders, and order_items. (You don’t technically need the products table for this specific math, but you can join it if you want).
You must filter the orders before aggregating.
You must group the results by the user so they only appear on one row.
You must sort the final report so the highest-spending user is at the top.

This query will combine Joins, Aggregation, String Functions, and Conditional Logic. Take a deep breath, write it step by step, and show the CFO what you can do!

Once you complete this, you are officially finished with the curriculum. Happy querying!