SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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).