1WITH monthly_sales AS (
2 SELECT
3 DATE_TRUNC('month', order_date) AS month,
4 category,
5 SUM(amount) AS total,
6 COUNT(*) AS order_count
7 FROM orders
8 WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE)
9 GROUP BY DATE_TRUNC('month', order_date), category
10),
11
12ranked_categories AS (
13 SELECT
14 month,
15 category,
16 total,
17 order_count,
18 ROW_NUMBER() OVER (
19 PARTITION BY month
20 ORDER BY total DESC
21 ) AS rank,
22 LAG(total) OVER (
23 PARTITION BY category
24 ORDER BY month
25 ) AS prev_month_total
26 FROM monthly_sales
27),
28
29growth AS (
30 SELECT
31 month,
32 category,
33 total,
34 order_count,
35 rank,
36 CASE
37 WHEN prev_month_total IS NULL THEN NULL
38 WHEN prev_month_total = 0 THEN NULL
39 ELSE ROUND(
40 (total - prev_month_total) * 100.0
41 / prev_month_total, 2
42 )
43 END AS growth_pct,
44 SUM(total) OVER (
45 PARTITION BY category
46 ORDER BY month
47 ROWS UNBOUNDED PRECEDING
48 ) AS running_total,
49 AVG(total) OVER (
50 PARTITION BY category
51 ORDER BY month
52 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
53 ) AS moving_avg_3m
54 FROM ranked_categories
55)
56
57SELECT
58 month,
59 category,
60 total,
61 order_count,
62 rank,
63 growth_pct,
64 running_total,
65 ROUND(moving_avg_3m, 2) AS moving_avg_3m,
66 ROUND(
67 total * 100.0 / SUM(total) OVER (PARTITION BY month),
68 2
69 ) AS market_share_pct
70FROM growth
71WHERE rank <= 5
72ORDER BY month DESC, rank ASC;