You are given two tables: sales and products.
The sales table contains information about product sales including sale_id, product_id, sale_date, quantity sold, and price per unit.
The products table contains product information including product_id, product_name, and category.
Write a solution to find the most popular product category for each season. The seasons are defined as:
- Winter: December, January, February
- Spring: March, April, May
- Summer: June, July, August
- Fall: September, October, November
The popularity of a category is determined by the total quantity sold in that season. If there is a tie, select the category with the highest total revenue (quantity × price). If there is still a tie, return the lexicographically smaller category.
Return the result table ordered by season in ascending order.
Table Schema
| Column Name | Type | Description |
|---|---|---|
sale_id
PK
|
int | Unique identifier for each sale |
product_id
|
int | Foreign key to products table |
sale_date
|
date | Date when the sale occurred |
quantity
|
int | Number of units sold |
price
|
decimal | Price per unit |
| Column Name | Type | Description |
|---|---|---|
product_id
PK
|
int | Unique identifier for each product |
product_name
|
varchar | Name of the product |
category
|
varchar | Product category |
Input & Output
| sale_id | product_id | sale_date | quantity | price |
|---|---|---|---|---|
| 1 | 101 | 2023-03-15 | 50 | 25 |
| 2 | 102 | 2023-04-10 | 30 | 40 |
| 3 | 103 | 2023-06-20 | 80 | 15 |
| 4 | 101 | 2023-07-05 | 40 | 25 |
| 5 | 104 | 2023-09-12 | 60 | 30 |
| product_id | product_name | category |
|---|---|---|
| 101 | Laptop | Electronics |
| 102 | T-shirt | Clothing |
| 103 | Soccer Ball | Sports |
| 104 | Running Shoes | Sports |
| season | category |
|---|---|
| Spring | Electronics |
| Summer | Sports |
| Fall | Sports |
In Spring (March-May), Electronics has 50 units vs Clothing's 30 units. In Summer (June-August), Sports has 80 units vs Electronics' 40 units. In Fall (September-November), Sports has the only sales with 60 units. Winter has no sales data.
| sale_id | product_id | sale_date | quantity | price |
|---|---|---|---|---|
| 1 | 101 | 2023-01-15 | 20 | 100 |
| 2 | 102 | 2023-02-10 | 20 | 50 |
| 3 | 103 | 2023-03-20 | 15 | 80 |
| product_id | product_name | category |
|---|---|---|
| 101 | Phone | Electronics |
| 102 | Jacket | Clothing |
| 103 | Book | Books |
| season | category |
|---|---|
| Spring | Books |
| Winter | Electronics |
In Winter, both Electronics and Clothing have 20 units sold, but Electronics has higher revenue ($2000 vs $1000). In Spring, Books is the only category. The tiebreaker logic prioritizes quantity first, then revenue.
Constraints
-
1 ≤ sale_id ≤ 10000 -
1 ≤ product_id ≤ 1000 -
sale_dateis a valid date -
1 ≤ quantity ≤ 1000 -
0.01 ≤ price ≤ 10000.00 -
product_nameandcategoryare non-empty strings