The Basics · COUNT · SUM · GROUP BY
5. Revenue per customer
Concept: COUNT · SUM · GROUP BYshow
Aggregates squash many rows into one value: COUNT(*) counts rows, SUM(col) adds values up. GROUP BY col makes one result row per distinct value of col, computing the aggregates per group. Name result columns with AS.
Example (different data):
SELECT category, COUNT(*) AS products, SUM(price) AS value FROM products GROUP BY category;
Your task
Finance is reconciling July revenue. Refunded and pending orders don't count as revenue.
For completed orders only, return customer_id, order_count and total_amount per customer, highest total_amount first.
Solution unlocks after 3 more tries
Starting Postgres in your browser…
query.sql
Output
Write a query and press Run Query (Ctrl/⌘ + Enter).