|
#2
秋末茗塘2022-12-15 16:20
|
Please write a query to get the top 3 item_id (has most distinct order_ids) for each category and their order count together with its rank for each category.
Table: item_sales_information
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| item_id | int |
| order_id | int |
| category | varchar |
| sold_date | timestamp|
+-------------+----------+
Your query should return the following output for the above table:
+----------+---------------+------------+------+
| item_id | order_count | category | rank |
+----------+---------------+------------+------+
| t3902 | 2599 | Clothes | 1 |
| t7892 | 2320 | Clothes | 2 |
| t1829 | 1878 | Clothes | 3 |
| k1290 | 1299 | Shoes | 1 |
| k4729 | 872 | Shoes | 2 |
| k0173 | 729 | Shoes | 3 |
... |
+----------+---------------+------------+------+