Seasonal Sales Analysis - Problem

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

sales
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
Primary Key: sale_id
products
Column Name Type Description
product_id PK int Unique identifier for each product
product_name varchar Name of the product
category varchar Product category
Primary Key: product_id

Input & Output

Example 1 — Basic Seasonal Analysis
Input Tables:
sales
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
products
product_id product_name category
101 Laptop Electronics
102 T-shirt Clothing
103 Soccer Ball Sports
104 Running Shoes Sports
Output:
season category
Spring Electronics
Summer Sports
Fall Sports
💡 Note:

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.
Example 2 — Tiebreaker by Revenue
Input Tables:
sales
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
products
product_id product_name category
101 Phone Electronics
102 Jacket Clothing
103 Book Books
Output:
season category
Spring Books
Winter Electronics
💡 Note:

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_date is a valid date
  • 1 ≤ quantity ≤ 1000
  • 0.01 ≤ price ≤ 10000.00
  • product_name and category are non-empty strings

Visualization

Tap to expand
Seasonal Sales Analysis OverviewSales Tableproduct_idsale_datequantity1012023-03-15501032023-06-2080Products Tableproduct_idcategory101Electronics103SportsJOIN + CASESeason ClassificationGrouped by SeasonseasoncategoryquantitySpringElectronics50SummerSports80ROW_NUMBER()RANK = 1Final ResultseasoncategorySpringElectronicsSummerSports
Understanding the Visualization
1
Join Tables
Combine sales and products data
2
Classify Seasons
Map dates to seasons using CASE
3
Rank Categories
Use ROW_NUMBER() with tiebreakers
Key Takeaway
🎯 Key Insight: Use window functions with PARTITION BY to rank items within groups, handling complex tiebreaking scenarios

Related Problems

Asked in
Amazon 28 Microsoft 15 Google 22
23.4K Views
Medium Frequency
~18 min Avg. Time
856 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen