Max Quantity
Start Timer
0:00:00
Given the transactions table, write a query to get the max quantity purchased for each distinct product_id, every year.
The output should include the year, product_id, and max_quantity for that product sorted by year and product_id ascending.
Schema:
Input:
transactions table
| Column | Type |
|---|---|
id |
INTEGER |
user_id |
INTEGER |
created_at |
DATETIME |
product_id |
INTEGER |
quantity |
INTEGER |
Output:
| Column | Type |
|---|---|
year |
INTEGER |
product_id |
INTEGER |
max_quantity |
INTEGER |
Example
Input:
| id | user_id | created_at | product_id | quantity |
|---|---|---|---|---|
| 1 | 59 | 2019-10-05 00:00:00 | 500 | 1 |
| 2 | 84 | 2019-08-11 00:00:00 | 500 | 2 |
| 3 | 21 | 2020-01-25 00:00:00 | 500 | 3 |
| 4 | 47 | 2019-12-25 00:00:00 | 500 | 2 |
| 5 | 100 | 2020-03-05 00:00:00 | 500 | 2 |
| 6 | 45 | 2019-06-30 00:00:00 | 500 | 1 |
| 7 | 44 | 2020-07-07 00:00:00 | 500 | 1 |
| 8 | 38 | 2020-09-23 00:00:00 | 500 | 2 |
| 9 | 45 | 2020-03-08 00:00:00 | 500 | 2 |
| 10 | 16 | 2020-04-22 00:00:00 | 999 | 1 |
| 11 | 17 | 2020-06-06 00:00:00 | 999 | 2 |
| 12 | 41 | 2020-09-17 00:00:00 | 999 | 1 |
| 13 | 34 | 2019-07-05 00:00:00 | 999 | 1 |
| 14 | 78 | 2020-02-16 00:00:00 | 999 | 3 |
| 15 | 16 | 2020-05-22 00:00:00 | 999 | 4 |
| 16 | 4 | 2020-05-10 00:00:00 | 999 | 2 |
| 17 | 25 | 2019-12-15 00:00:00 | 999 | 1 |
| 18 | 71 | 2020-05-03 00:00:00 | 999 | 1 |
| 19 | 3 | 2020-08-03 00:00:00 | 999 | 2 |
| 20 | 19 | 2019-03-13 00:00:00 | 999 | 5 |
Output:
| year | product_id | max_quantity |
|---|---|---|
| 2019 | 500 | 2 |
| 2019 | 999 | 5 |
| 2020 | 500 | 3 |
| 2020 | 999 | 4 |
.
.
.
.
.
.
.
.
.
Comments