Product Analytics

1-- Performance theo category
2SELECT
3    p.category,
4    COUNT(DISTINCT p.product_id) AS num_products,
5    COUNT(DISTINCT oi.order_id) AS orders,
6    SUM(oi.quantity) AS units_sold,
7    SUM(oi.line_total) AS total_revenue,
8    ROUND(AVG(oi.unit_price), 0) AS avg_price,
9    ROUND(AVG(p.discount_percent), 1) AS avg_discount_pct,
10    ROUND(AVG(p.rating), 2) AS avg_rating,
11    ROUND(
12        SUM(oi.line_total) * 100.0 / SUM(SUM(oi.line_total)) OVER(), 1
13    ) AS revenue_share_pct
14FROM fact_order_items oi
15JOIN dim_product p ON oi.product_id = p.product_id
16JOIN fact_orders o ON oi.order_id = o.order_id
17WHERE o.status = 'Delivered'
18GROUP BY p.category
19ORDER BY total_revenue DESC;

1-- Top 5 sản phẩm mỗi category
2WITH product_rank AS (
3    SELECT
4        p.category,
5        p.product_name,
6        p.brand,
7        SUM(oi.line_total) AS revenue,
8        SUM(oi.quantity) AS units,
9        ROUND(AVG(p.rating), 1) AS rating,
10        ROW_NUMBER() OVER (
11            PARTITION BY p.category
12            ORDER BY SUM(oi.line_total) DESC
13        ) AS rank_in_category
14    FROM fact_order_items oi
15    JOIN dim_product p ON oi.product_id = p.product_id
16    JOIN fact_orders o ON oi.order_id = o.order_id
17    WHERE o.status = 'Delivered'
18    GROUP BY p.category, p.product_name, p.brand
19)
20SELECT *
21FROM product_rank
22WHERE rank_in_category <= 5
23ORDER BY category, rank_in_category;

1-- ABC Classification cho 2,000 sản phẩm
2WITH product_revenue AS (
3    SELECT
4        p.product_id,
5        p.product_name,
6        p.category,
7        p.brand,
8        SUM(oi.line_total) AS revenue
9    FROM fact_order_items oi
10    JOIN dim_product p ON oi.product_id = p.product_id
11    JOIN fact_orders o ON oi.order_id = o.order_id
12    WHERE o.status = 'Delivered'
13    GROUP BY p.product_id, p.product_name, p.category, p.brand
14),
15cumulative AS (
16    SELECT
17        *,
18        SUM(revenue) OVER (ORDER BY revenue DESC) AS cumulative_revenue,
19        SUM(revenue) OVER () AS total_revenue
20    FROM product_revenue
21)
22SELECT
23    *,
24    ROUND(cumulative_revenue * 100.0 / total_revenue, 1) AS cumulative_pct,
25    CASE
26        WHEN cumulative_revenue * 100.0 / total_revenue <= 80 THEN 'A'
27        WHEN cumulative_revenue * 100.0 / total_revenue <= 95 THEN 'B'
28        ELSE 'C'
29    END AS abc_class
30FROM cumulative
31ORDER BY revenue DESC;

1-- Phân tích Price Range
2WITH price_ranges AS (
3    SELECT
4        CASE
5            WHEN p.selling_price < 100000 THEN 'Under 100K'
6            WHEN p.selling_price < 500000 THEN '100K-500K'
7            WHEN p.selling_price < 1000000 THEN '500K-1M'
8            WHEN p.selling_price < 5000000 THEN '1M-5M'
9            WHEN p.selling_price < 10000000 THEN '5M-10M'
10            ELSE 'Over 10M'
11        END AS price_range,
12        oi.quantity,
13        oi.line_total,
14        p.rating,
15        p.discount_percent
16    FROM fact_order_items oi
17    JOIN dim_product p ON oi.product_id = p.product_id
18    JOIN fact_orders o ON oi.order_id = o.order_id
19    WHERE o.status = 'Delivered'
20)
21SELECT
22    price_range,
23    SUM(quantity) AS units_sold,
24    SUM(line_total) AS revenue,
25    ROUND(AVG(rating), 2) AS avg_rating,
26    ROUND(AVG(discount_percent), 1) AS avg_discount_pct,
27    ROUND(SUM(line_total) * 100.0 / SUM(SUM(line_total)) OVER(), 1) AS revenue_share_pct
28FROM price_ranges
29GROUP BY price_range
30ORDER BY MIN(CASE
31    WHEN price_range = 'Under 100K' THEN 1
32    WHEN price_range = '100K-500K' THEN 2
33    WHEN price_range = '500K-1M' THEN 3
34    WHEN price_range = '1M-5M' THEN 4
35    WHEN price_range = '5M-10M' THEN 5
36    ELSE 6
37END);

1-- Ảnh hưởng của discount đến volume
2SELECT
3    CASE
4        WHEN p.discount_percent = 0 THEN 'No Discount'
5        WHEN p.discount_percent <= 10 THEN '1-10%'
6        WHEN p.discount_percent <= 20 THEN '11-20%'
7        WHEN p.discount_percent <= 30 THEN '21-30%'
8        ELSE 'Over 30%'
9    END AS discount_range,
10    COUNT(DISTINCT oi.order_id) AS orders,
11    SUM(oi.quantity) AS units_sold,
12    ROUND(AVG(oi.unit_price), 0) AS avg_unit_price,
13    ROUND(AVG(p.rating), 2) AS avg_rating
14FROM fact_order_items oi
15JOIN dim_product p ON oi.product_id = p.product_id
16JOIN fact_orders o ON oi.order_id = o.order_id
17WHERE o.status = 'Delivered'
18GROUP BY CASE
19    WHEN p.discount_percent = 0 THEN 'No Discount'
20    WHEN p.discount_percent <= 10 THEN '1-10%'
21    WHEN p.discount_percent <= 20 THEN '11-20%'
22    WHEN p.discount_percent <= 30 THEN '21-30%'
23    ELSE 'Over 30%'
24END
25ORDER BY MIN(p.discount_percent);