Your query is ready
SELECT u.id, u.name, COUNT(*) AS order_count, SUM(o.amount) AS total_amountFROM orders AS oJOIN users AS u ON u.id = o.user_idWHERE o.created_at >= NOW() - INTERVAL '30 days' AND o.status = 'completed'GROUP BY u.id, u.nameORDER BY order_count DESC, total_amount DESCLIMIT 10;What it does
JOIN users AS u ON u.id = o.user_idTies each order to its owner. The customer name is not kept on the orders table, so this join is not optional.
WHERE o.created_at >= NOW() - INTERVAL '30 days'Takes the last 30 days relative to when it runs, rather than a hard-coded date — the query is still correct tomorrow.
AND o.status = 'completed'Cancelled and refunded orders stay out of the count — “top customer” means a customer with completed orders.
GROUP BY u.id, u.nameRows are collapsed per customer. PostgreSQL does not require `u.name` because `u.id` is the primary key, but writing it keeps the query portable.
ORDER BY order_count DESC, total_amount DESCWhen two customers tie on count, the higher spender comes first — that makes the ordering stable.
Example result
| id | name | order_count | total_amount |
|---|---|---|---|
| 1 | Ayşe Yıldız | 3 | 815.00 |
| 2 | Mehmet Kaya | 2 | 1380.00 |
| 5 | Zeynep Ak | 1 | 540.00 |
These rows come from actually running the query on the example schema in a SQLite file.
Performance note
- CREATE INDEX ON orders (created_at DESC) WHERE status = 'completed'; — a partial index, so the scan only ever touches completed orders.
- Without an index on orders (user_id), the join scans the users table once per row.
- At millions of rows, consider a pre-aggregated summary table (a daily materialized view) instead of `COUNT(*)`.
Dialect difference
MySQL writes `NOW() - INTERVAL 30 DAY`, SQL Server `DATEADD(day, -30, GETDATE())`, SQLite `date('now', '-30 days')`. Using an output column name inside ORDER BY is allowed in PostgreSQL and MySQL, but not in SQL Server.