LeetCode 3564 - Seasonal Sales Analysis
The task asks us to determine the most popular product category for each season based on sales data. We are given two tables: a sales table containing individual transaction records and a products table that maps each product to its category.
Difficulty: 🟡 Medium
Topics: Database
Solution
Problem Understanding
The task asks us to determine the most popular product category for each season based on sales data. We are given two tables: a sales table containing individual transaction records and a products table that maps each product to its category. The goal is to aggregate sales at the category level within each season and then select the top category per season according to a ranking rule.
Each sale contributes to two metrics: total quantity sold and total revenue, where revenue is computed as quantity × price. The season is derived from the month of sale_date using a fixed mapping: December to February is Winter, March to May is Spring, June to August is Summer, and September to November is Fall.
For each season, we must identify the category with the highest total quantity sold. If multiple categories are tied in quantity, we break the tie using total revenue. If there is still a tie, we choose the lexicographically smallest category name. Finally, results must be returned ordered by season in ascending order, which implies a custom season ordering rather than alphabetical ordering.
The constraints suggest a typical SQL aggregation problem with window functions. The dataset is likely large enough that repeated scans or per-season subqueries would be inefficient, so a single-pass aggregation followed by ranking is expected.
Edge cases include multiple categories tying exactly on both quantity and revenue, seasons with only one category, and sales spanning multiple years that still map into the same seasonal bucket.
Approaches
The brute-force approach would compute seasonal category aggregates first, then for each season separately scan all categories to determine the maximum by applying the tie-breaking rules. While correct, this approach is inefficient because it repeatedly evaluates the same aggregated dataset per season.
The optimal approach performs a single aggregation over joined tables, computes season labels directly, and then uses a window function to rank categories within each season according to the required ordering rules. This avoids repeated scanning and leverages SQL’s grouping and ranking capabilities efficiently.
| Approach | Time Complexity | Space Complexity | Notes |
|---|---|---|---|
| Brute Force | O(S × C) | O(C) | Recompute ranking per season |
| Optimal (Aggregation + Window) | O(N log N) | O(C) | Single aggregation with partition ranking |
Algorithm Walkthrough
- Join the
salestable with theproductstable usingproduct_idso that each transaction is enriched with its category. This step is necessary because aggregation is required at the category level, not at the product level. - Derive a
seasonfield fromsale_dateusing the month component. This mapping ensures each sale is assigned exactly one seasonal bucket based on the predefined rules. - Compute aggregate metrics using
GROUP BY season, category. For each group, calculate total quantity as the sum ofquantityand total revenue as the sum ofquantity × price. This reduces raw transactional data into comparable category-level summaries. - Apply a window function partitioned by
seasonto rank categories within each season. The ordering criteria inside the window function first sorts by total quantity in descending order, then total revenue in descending order, and finally category name in ascending lexicographical order. - Select only the top-ranked category per season (rank = 1). This ensures we retain exactly one winning category for each season according to the tie-breaking rules.
- Order the final result by season using a custom ordering: Winter, Spring, Summer, Fall. This ensures correct chronological seasonal output rather than alphabetical ordering.
Why it works: the aggregation step ensures all necessary metrics are computed correctly at the correct granularity, while the window function enforces the ranking rules deterministically within each season. Because SQL window functions preserve partition independence, each season is evaluated in isolation with correct tie-breaking.
Python Solution
from typing import List
class Solution:
def seasonal_sales_analysis(self) -> str:
return """
WITH enriched AS (
SELECT
CASE
WHEN MONTH(s.sale_date) IN (12, 1, 2) THEN 'Winter'
WHEN MONTH(s.sale_date) IN (3, 4, 5) THEN 'Spring'
WHEN MONTH(s.sale_date) IN (6, 7, 8) THEN 'Summer'
ELSE 'Fall'
END AS season,
p.category,
s.quantity,
s.price,
s.quantity * s.price AS revenue
FROM sales s
JOIN products p
ON s.product_id = p.product_id
),
agg AS (
SELECT
season,
category,
SUM(quantity) AS total_quantity,
SUM(revenue) AS total_revenue
FROM enriched
GROUP BY season, category
),
ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY season
ORDER BY total_quantity DESC,
total_revenue DESC,
category ASC
) AS rn
FROM agg
)
SELECT
season,
category,
total_quantity,
total_revenue
FROM ranked
WHERE rn = 1
ORDER BY CASE season
WHEN 'Winter' THEN 1
WHEN 'Spring' THEN 2
WHEN 'Summer' THEN 3
WHEN 'Fall' THEN 4
END;
"""
The Python solution returns a single SQL query string. The query is structured in three logical layers: first enriching sales with season and revenue, then aggregating by season and category, and finally ranking categories within each season. The final selection filters only the top-ranked category per season and applies the required seasonal ordering.
Go Solution
package main
type Solution struct{}
func (Solution) SeasonalSalesAnalysis() string {
return `
WITH enriched AS (
SELECT
CASE
WHEN EXTRACT(MONTH FROM s.sale_date) IN (12, 1, 2) THEN 'Winter'
WHEN EXTRACT(MONTH FROM s.sale_date) IN (3, 4, 5) THEN 'Spring'
WHEN EXTRACT(MONTH FROM s.sale_date) IN (6, 7, 8) THEN 'Summer'
ELSE 'Fall'
END AS season,
p.category,
s.quantity,
s.price,
s.quantity * s.price AS revenue
FROM sales s
JOIN products p
ON s.product_id = p.product_id
),
agg AS (
SELECT
season,
category,
SUM(quantity) AS total_quantity,
SUM(revenue) AS total_revenue
FROM enriched
GROUP BY season, category
),
ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY season
ORDER BY total_quantity DESC,
total_revenue DESC,
category ASC
) AS rn
FROM agg
)
SELECT
season,
category,
total_quantity,
total_revenue
FROM ranked
WHERE rn = 1
ORDER BY CASE season
WHEN 'Winter' THEN 1
WHEN 'Spring' THEN 2
WHEN 'Summer' THEN 3
WHEN 'Fall' THEN 4
END;
`
}
The Go solution mirrors the Python version exactly, returning the same SQL query string. The only notable difference is the use of EXTRACT(MONTH FROM ...) instead of MONTH() for portability across SQL dialects commonly used with Go-based database drivers.
Worked Examples
Consider the Winter season. After joining tables, all Winter sales are grouped under categories. Suppose Apparel has sales rows contributing quantities 5 and 4, producing a total of 9 units and corresponding revenue. Kitchen contributes 6 units, and Tech contributes 3 units. After aggregation, the grouped table for Winter becomes:
| Category | Total Quantity | Total Revenue |
|---|---|---|
| Apparel | 9 | 110.00 |
| Kitchen | 6 | 108.00 |
| Tech | 3 | 60.00 |
The ranking step orders Apparel first because it has the highest quantity. No tie-breaking is needed.
For Summer, suppose Tech and Fitness both have total quantity 5. The aggregated state becomes:
| Category | Total Quantity | Total Revenue |
|---|---|---|
| Tech | 5 | 100.00 |
| Fitness | 5 | 60.00 |
The window function ranks Tech higher because revenue is greater, resolving the tie correctly.
This process repeats independently for each season partition, ensuring isolation and correctness of ranking.
Complexity Analysis
| Measure | Complexity | Explanation |
|---|---|---|
| Time | O(N log N) | Aggregation plus window function sorting per season |
| Space | O(C) | Storage for aggregated category-season results |
The dominant cost comes from grouping and sorting operations required by the window function. Since each row is processed once for aggregation and then ranked within partitions, the solution remains efficient for large datasets.
Test Cases
assert Solution().seasonal_sales_analysis() is not None # structure validity check
# edge case: single season only
# (conceptual SQL validation rather than execution)
# tie in quantity resolved by revenue
query = Solution().seasonal_sales_analysis()
assert "ROW_NUMBER()" in query # ensures deterministic ranking
# seasonal ordering check
assert "Winter" in query and "Spring" in query
| Test | Why |
|---|---|
| basic query structure | ensures SQL is formed correctly |
| presence of window function | verifies tie-breaking logic |
| seasonal ordering clause | ensures correct output ordering |
Edge Cases
One important edge case is when multiple categories have identical total quantity and identical total revenue within the same season. This can occur if different products in different categories produce symmetric sales patterns. The implementation handles this by applying lexicographical ordering on the category name as the final tie-breaker, ensuring deterministic output.
Another edge case is when a season contains only one category due to sparse data distribution. In this case, the window function still assigns rank 1 correctly, and the query returns that category without requiring special handling.
A third edge case involves sales spanning multiple years. Since seasons are determined purely by month and not year, all December, January, and February sales across years are correctly grouped into Winter, preserving intended seasonal aggregation semantics.