Project 30: Top Customers Report

SQL Architect

Query Task

Objective

The capstone: every concept from this track — WHERE, GROUP BY, HAVING, aggregate functions, ORDER BY, and LIMIT — combined into one real admin report identifying your best customers.

You're given an orders table with columns id, customer, status, total, and order_date.

Task: return customer, a count of orders (as order_count), and the sum of total (as lifetime_value) — for completed orders only, grouped by customer, keeping only customers with a lifetime value over 500, sorted by lifetime_value descending, limited to the top 10.

query.sql

SELECT customer, COUNT(*) AS order_count, SUM(total) AS lifetime_value FROM orders WHERE status = 'completed' GROUP BY customer HAVING SUM(total) > 500 ORDER BY lifetime_value DESC LIMIT 10;

* Hint: Correct characters turn green, incorrect ones turn red.

Query Results

🗄️

Execute your query to see results

← Previous