Seasonal Sales Analysis solution and explanation
3 more videos on this problem
Leetcode MEDIUM 3564 - WINDOW FUNCTIONS in SQL - Seasonal Sales Analysis | Everyday Data ScienceEveryday Data Science · 10:58 · 1K views
LeetCode 3564 – Seasonal Sales Analysis: Step-by-Step SQL Solution (Medium)InsightVanta · 13:41 · 41 views
Leetcode 3564 Seasonal Sales AnalysisAlgorithmicIQ · 15:07 · 19 views
LeetCode. Sql, 3564. Seasonal Sales Analysis, уровень MEDIUMДиванная аналитика · 23:08 · 4 views
The problem
Table: sales
+---------------+---------+ | Column Name | Type | +---------------+---------+ | sale_id | int | | product_id | int | | sale_date | date | | quantity | int | | price | decimal | +---------------+---------+ sale_id is the unique identifier for this table. Each row contains information about a product sale including the product_id, date of sale, quantity sold, and price per unit.
Table: products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | | category | varchar | +---------------+---------+ product_id is the unique identifier for this table. Each row contains information about a product including its 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.
The result format is in the following example.
Example:
Input:
sales table:
+---------+------------+------------+----------+-------+ | sale_id | product_id | sale_date | quantity | price | +---------+------------+------------+----------+-------+ | 1 | 1 | 2023-01-15 | 5 | 10.00 | | 2 | 2 | 2023-01-20 | 4 | 15.00 | | 3 | 3 | 2023-03-10 | 3 | 18.00 | | 4 | 4 | 2023-04-05 | 1 | 20.00 | | 5 | 1 | 2023-05-20 | 2 | 10.00 | | 6 | 2 | 2023-06-12 | 4 | 15.00 | | 7 | 5 | 2023-06-15 | 5 | 12.00 | | 8 | 3 | 2023-07-24 | 2 | 18.00 | | 9 | 4 | 2023-08-01 | 5 | 20.00 | | 10 | 5 | 2023-09-03 | 3 | 12.00 | | 11 | 1 | 2023-09-25 | 6 | 10.00 | | 12 | 2 | 2023-11-10 | 4 | 15.00 | | 13 | 3 | 2023-12-05 | 6 | 18.00 | | 14 | 4 | 2023-12-22 | 3 | 20.00 | | 15 | 5 | 2024-02-14 | 2 | 12.00 | +---------+------------+------------+----------+-------+
products table:
+------------+-----------------+----------+ | product_id | product_name | category | +------------+-----------------+----------+ | 1 | Warm Jacket | Apparel | | 2 | Designer Jeans | Apparel | | 3 | Cutting Board | Kitchen | | 4 | Smart Speaker | Tech | | 5 | Yoga Mat | Fitness | +------------+-----------------+----------+
Output:
+---------+----------+----------------+---------------+ | season | category | total_quantity | total_revenue | +---------+----------+----------------+---------------+ | Fall | Apparel | 10 | 120.00 | | Spring | Kitchen | 3 | 54.00 | | Summer | Tech | 5 | 100.00 | | Winter | Apparel | 9 | 110.00 | +---------+----------+----------------+---------------+
Explanation:
- Fall (Sep, Oct, Nov):
- Apparel: 10 items sold (6 Jackets in Sep, 4 Jeans in Nov), revenue $120.00 (6×$10.00 + 4×$15.00)
- Fitness: 3 Yoga Mats sold in Sep, revenue $36.00
- Most popular: Apparel with highest total quantity (10)
- Spring (Mar, Apr, May):
- Kitchen: 3 Cutting Boards sold in Mar, revenue $54.00
- Tech: 1 Smart Speaker sold in Apr, revenue $20.00
- Apparel: 2 Warm Jackets sold in May, revenue $20.00
- Most popular: Kitchen with highest total quantity (3) and highest revenue ($54.00)
- Summer (Jun, Jul, Aug):
- Apparel: 4 Designer Jeans sold in Jun, revenue $60.00
- Fitness: 5 Yoga Mats sold in Jun, revenue $60.00
- Kitchen: 2 Cutting Boards sold in Jul, revenue $36.00
- Tech: 5 Smart Speakers sold in Aug, revenue $100.00
- Most popular: Tech and Fitness both have 5 items, but Tech has higher revenue ($100.00 vs $60.00)
- Winter (Dec, Jan, Feb):
- Apparel: 9 items sold (5 Jackets in Jan, 4 Jeans in Jan), revenue $110.00
- Kitchen: 6 Cutting Boards sold in Dec, revenue $108.00
- Tech: 3 Smart Speakers sold in Dec, revenue $60.00
- Fitness: 2 Yoga Mats sold in Feb, revenue $24.00
- Most popular: Apparel with highest total quantity (9) and highest revenue ($110.00)
The result table is ordered by season in ascending order.
Approach overview
Problem Overview: You get a sales table and a products table. Assign each sale to a season based on its month, then aggregate quantity and revenue per season and category. The result shows the leading category in each season.
Approach 1: Brute Force with Correlated Subqueries (O(S * C * N) Time, O(1) Extra Space)
For each season and category pair, run a separate subquery that scans the sales table and joins products to compute totals. The logic is easy to write because each subquery answers one question. The cost is that the sales table is scanned once per (season, category) pair. Use this only to confirm your filtering logic on small datasets.
Approach 2: Equi Join + Group Aggregation (O(N + M) Time, O(G) Space)
Join sales to products on product_id with an equality condition. Use a CASE expression on MONTH(sale_date) to map months to Winter, Spring, Summer, or Fall. Then GROUP BY season, category and apply SUM(quantity) and SUM(quantity * price). The database builds a hash table on the smaller side of the join, so each row is touched once. Here N is the number of sales rows, M is the number of products, and G is the number of season-category groups.
Approach 3: Equi Join + Group Aggregation + Window Function (O(N log N) Time, O(G) Space)
Start from Approach 2 and wrap the grouped result in a CTE. Add RANK() OVER (PARTITION BY season ORDER BY total_quantity DESC) to rank categories inside each season. Then filter to rank 1 in the outer query. The window function sorts each partition, which is where the log factor comes from. This is the cleanest way to express 'top category per season' without self-joins or repeated scans.
Recommended for interviews: Interviewers expect the join plus grouped aggregation with a window function. The brute force version shows you understand the requirement, but the window function version shows you can write set-based SQL that scales. Explain the season mapping and tie handling out loud. Mention that RANK() keeps ties while ROW_NUMBER() does not.
Related: Database, Window Functions, Joins.
Solution
Thinking
Join sales to products, map months to the four seasons, and aggregate quantity and revenue by
(season,category).Rank categories inside each season by quantity then revenue, keep rank
1, and order the rows by season. A window rank replaces a correlated subquery.
We can perform an equi join between the sales table and the products table to obtain the product category for each sales record. Next, we determine the season based on the month of the sales date, and then group by season and category to calculate the total quantity sold and total revenue. Finally, we use a window function to rank the categories within each season and select the top-ranked category.
# Write your MySQL query statement below
WITH
SeasonalSales AS (
SELECT
CASE
WHEN MONTH(sale_date) IN (12, 1, 2) THEN 'Winter'
WHEN MONTH(sale_date) IN (3, 4, 5) THEN 'Spring'
WHEN MONTH(sale_date) IN (6, 7, 8) THEN 'Summer'
WHEN MONTH(sale_date) IN (9, 10, 11) THEN 'Fall'
END AS season,
category,
SUM(quantity) AS total_quantity,
SUM(quantity * price) AS total_revenue
FROM
sales
JOIN products USING (product_id)
GROUP BY 1, 2
),
TopCategoryPerSeason AS (
SELECT
*,
RANK() OVER (
PARTITION BY season
ORDER BY total_quantity DESC, total_revenue DESC, category ASC
) AS rk
FROM SeasonalSales
)
SELECT season, category, total_quantity, total_revenue
FROM TopCategoryPerSeason
WHERE rk = 1
ORDER BY 1;import pandas as pd
def seasonal_sales_analysis(
products: pd.DataFrame, sales: pd.DataFrame
) -> pd.DataFrame:
df = sales.merge(products, on="product_id")
month_to_season = {
12: "Winter",
1: "Winter",
2: "Winter",
3: "Spring",
4: "Spring",
5: "Spring",
6: "Summer",
7: "Summer",
8: "Summer",
9: "Fall",
10: "Fall",
11: "Fall",
}
df["season"] = df["sale_date"].dt.month.map(month_to_season)
seasonal_sales = df.groupby(["season", "category"], as_index=False).agg(
total_quantity=("quantity", "sum"),
total_revenue=("quantity", lambda x: (x * df.loc[x.index, "price"]).sum()),
)
seasonal_sales["rk"] = (
seasonal_sales.sort_values(
["season", "total_quantity", "total_revenue", "category"],
ascending=[True, False, False, True],
)
.groupby("season")
.cumcount()
+ 1
)
result = seasonal_sales[seasonal_sales["rk"] == 1].copy()
return result[
["season", "category", "total_quantity", "total_revenue"]
].sort_values("season")Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subqueries (Brute Force) | O(S * C * N) | O(1) extra | Small data, verifying logic |
| Equi Join + Group Aggregation | O(N + M) | O(G) | Full season and category totals |
| Equi Join + Group Aggregation + Window Function | O(N log N) | O(G) | Top category per season, ranking and ties |
Frequently asked questions
Seasonal Sales Analysis Python solution
A Pandas solution merges the sales and products DataFrames on product_id, maps the month to a season using a dictionary or pd.cut-style logic, then groups by season and category with sum aggregations. For the top category per season, use groupby with idxmax or rank with method='min' inside each group. This mirrors the SQL join, group, and window approach.
Is Seasonal Sales Analysis easy or hard?
Seasonal Sales Analysis is a medium-difficulty problem. The hard part is not the join or the aggregation but correctly mapping months to seasons, handling ties in the ranking, and choosing the right window function. Practice writing the CTE and the RANK() filter until it feels routine.
How to solve Seasonal Sales Analysis in O(n)?
Join sales to products with a single equality condition on product_id, derive the season with a CASE expression on the month, and aggregate with GROUP BY season, category. This pass is linear in the number of rows and uses a hash-based join. The window function adds a sort step, so the full ranking output is O(N log N).
What is the best approach for Seasonal Sales Analysis?
The best approach is an equi join between sales and products, followed by grouping on season and category, and a RANK() window function to pick the top category in each season. This runs in O(N log N) time because of the window sort. It is clean, set-based SQL that works well in both MySQL and Pandas.
What data structure is used in Seasonal Sales Analysis?
The problem uses relational tables rather than a classic data structure. The database engine uses a hash table internally for the equi join and grouping step, and a sort for the window function partition. Thinking in terms of hash-based grouping and sorted partitions helps you reason about performance.
What is the time complexity of Seasonal Sales Analysis?
The optimal solution runs in O(N log N) time, where N is the number of sales rows, due to sorting inside the window function. The join and aggregation steps alone are O(N + M), where M is the number of products. The brute force version with correlated subqueries takes O(S * C * N), where S is the number of seasons and C is the number of categories.
Is Seasonal Sales Analysis asked at Google, Amazon, or Meta?
Seasonal Sales Analysis is tagged as a Database problem on FleetCode, and it is a SQL-focused question rather than a standard algorithms round. Check the company-specific tags on LeetCode for current interview reports before you plan around it. Strong SQL skills with joins, CASE, and window functions transfer to any data-heavy interview loop.