Bulk Buyers
The packing floor at Whetcode Supply has two lines. Small orders go down the automated one; anything of three units or more has to be packed by hand, because the boxes stop fitting the conveyor. Operations wants the list of every order that has ever taken the manual route, biggest first, so they can see how much of the day it accounts for.
The customer's name is not wanted on this sheet — the packers work from numbers, and the sheet gets left on a bench.
orders — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning three columns, id, customer_id and quantity, one row per order of three units or more — three units exactly does qualify. Order the rows by quantity, largest first, and break ties by id ascending.
Example output — shape only, on invented orders. The last row shows the boundary: an order of exactly three units belongs in the result:
| id | customer_id | quantity |
|---|---|---|
| 41 | 12 | 9 |
| 37 | 15 | 6 |
| 44 | 12 | 3 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in