Welcome to Subscribe On Youtube
3564. Seasonal Sales Analysis
Description
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).
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.
Solutions
Solution 1: Equi Join + Group Aggregation + Window Function
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.
-
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"], ascending=[True, False, False], ) .groupby("season") .cumcount() + 1 ) result = seasonal_sales[seasonal_sales["rk"] == 1].copy() return result[ ["season", "category", "total_quantity", "total_revenue"] ].sort_values("season") -
# 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 ) AS rk FROM SeasonalSales ) SELECT season, category, total_quantity, total_revenue FROM TopCategoryPerSeason WHERE rk = 1 ORDER BY 1;