Problem
Customer lifetime value starts with understanding how frequently people buy. A sales team routinely queries order counts per customer to identify top buyers for loyalty programs, spot one-time purchasers who might need re-engagement campaigns, and understand overall purchasing patterns. Using the orders table, return customer_id and order_count ordered from most to fewest orders.
Schema
orders
| column | type |
|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
Sample Data
| id | customer_id | amount | created_at |
|---|
| 1 | 1 | 99.99 | 2024-01-10 |
| 2 | 1 | 49.50 | 2024-02-15 |
| 3 | 2 | 200.00 | 2024-03-01 |
| 4 | 1 | 75.00 | 2024-03-20 |
| 5 | 3 | 30.00 | 2024-04-05 |
Expected Output
| customer_id | order_count |
|---|
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |