Seller & Marketplace Analytics

1-- Seller Scorecard: tổng hợp performance metrics
2SELECT
3    s.seller_id,
4    s.seller_name,
5    s.seller_type,
6    s.city,
7    s.rating AS shop_rating,
8    s.response_rate,
9    s.ship_on_time_rate,
10
11    -- GMV metrics
12    COUNT(DISTINCT oi.order_id) AS total_orders,
13    SUM(oi.line_total) AS total_gmv,
14    ROUND(AVG(oi.line_total), 0) AS avg_item_value,
15    COUNT(DISTINCT oi.product_id) AS active_products,
16
17    -- Quality metrics
18    ROUND(AVG(r.rating), 2) AS avg_review_rating,
19    COUNT(r.review_id) AS total_reviews,
20    ROUND(
21        SUM(CASE WHEN oi.is_returned = 1 THEN 1 ELSE 0 END) * 100.0
22        / COUNT(*), 1
23    ) AS return_rate_pct,
24
25    -- Fulfillment
26    ROUND(
27        SUM(CASE WHEN o.status = 'Delivered' THEN 1 ELSE 0 END) * 100.0
28        / COUNT(DISTINCT oi.order_id), 1
29    ) AS fulfillment_rate_pct
30
31FROM dim_seller s
32LEFT JOIN fact_order_items oi ON s.seller_id = oi.seller_id
33LEFT JOIN fact_orders o ON oi.order_id = o.order_id
34LEFT JOIN fact_reviews r ON oi.order_item_id = r.order_item_id
35GROUP BY s.seller_id, s.seller_name, s.seller_type, s.city,
36    s.rating, s.response_rate, s.ship_on_time_rate
37ORDER BY total_gmv DESC;

1-- So sánh performance theo seller type
2SELECT
3    s.seller_type,
4    COUNT(DISTINCT s.seller_id) AS num_sellers,
5    SUM(oi.line_total) AS total_gmv,
6    ROUND(AVG(s.rating), 2) AS avg_shop_rating,
7    ROUND(AVG(s.ship_on_time_rate), 1) AS avg_ship_on_time,
8    ROUND(AVG(s.response_rate), 1) AS avg_response_rate,
9    ROUND(
10        SUM(CASE WHEN oi.is_returned = 1 THEN 1 ELSE 0 END) * 100.0
11        / NULLIF(COUNT(*), 0), 1
12    ) AS return_rate_pct,
13    ROUND(SUM(oi.line_total) / COUNT(DISTINCT s.seller_id), 0) AS gmv_per_seller
14FROM dim_seller s
15JOIN fact_order_items oi ON s.seller_id = oi.seller_id
16JOIN fact_orders o ON oi.order_id = o.order_id
17WHERE o.status = 'Delivered'
18GROUP BY s.seller_type
19ORDER BY total_gmv DESC;

1-- Rating distribution theo fulfillment quality
2WITH seller_quality AS (
3    SELECT
4        s.seller_id,
5        s.seller_name,
6        s.ship_on_time_rate,
7        ROUND(AVG(r.rating), 2) AS avg_customer_rating,
8        COUNT(r.review_id) AS review_count,
9        CASE
10            WHEN s.ship_on_time_rate >= 95 THEN 'Excellent (95%+)'
11            WHEN s.ship_on_time_rate >= 90 THEN 'Good (90-95%)'
12            WHEN s.ship_on_time_rate >= 80 THEN 'Average (80-90%)'
13            ELSE 'Poor (below 80%)'
14        END AS fulfillment_tier
15    FROM dim_seller s
16    JOIN fact_reviews r ON s.seller_id = r.seller_id
17    GROUP BY s.seller_id, s.seller_name, s.ship_on_time_rate
18)
19SELECT
20    fulfillment_tier,
21    COUNT(*) AS num_sellers,
22    ROUND(AVG(avg_customer_rating), 2) AS avg_review_rating,
23    ROUND(AVG(review_count), 0) AS avg_reviews
24FROM seller_quality
25GROUP BY fulfillment_tier
26ORDER BY avg_review_rating DESC;

1-- So sánh Top 10% vs Bottom 10% sellers
2WITH seller_ranking AS (
3    SELECT
4        s.seller_id,
5        s.seller_type,
6        SUM(oi.line_total) AS gmv,
7        ROUND(AVG(r.rating), 2) AS avg_rating,
8        s.ship_on_time_rate,
9        NTILE(10) OVER (ORDER BY SUM(oi.line_total) DESC) AS gmv_decile
10    FROM dim_seller s
11    JOIN fact_order_items oi ON s.seller_id = oi.seller_id
12    JOIN fact_orders o ON oi.order_id = o.order_id
13    LEFT JOIN fact_reviews r ON s.seller_id = r.seller_id
14    WHERE o.status = 'Delivered'
15    GROUP BY s.seller_id, s.seller_type, s.ship_on_time_rate
16)
17SELECT
18    CASE
19        WHEN gmv_decile = 1 THEN 'Top 10%'
20        WHEN gmv_decile = 10 THEN 'Bottom 10%'
21        ELSE 'Middle 80%'
22    END AS seller_group,
23    COUNT(*) AS num_sellers,
24    ROUND(AVG(gmv), 0) AS avg_gmv,
25    ROUND(AVG(avg_rating), 2) AS avg_rating,
26    ROUND(AVG(ship_on_time_rate), 1) AS avg_ship_on_time
27FROM seller_ranking
28GROUP BY CASE
29    WHEN gmv_decile = 1 THEN 'Top 10%'
30    WHEN gmv_decile = 10 THEN 'Bottom 10%'
31    ELSE 'Middle 80%'
32END;

1-- Seller concentration: top sellers chiếm bao nhiêu % GMV?
2WITH seller_gmv AS (
3    SELECT
4        s.seller_id,
5        SUM(oi.line_total) AS gmv,
6        SUM(SUM(oi.line_total)) OVER (
7            ORDER BY SUM(oi.line_total) DESC
8        ) AS cumulative_gmv,
9        SUM(SUM(oi.line_total)) OVER () AS total_gmv
10    FROM dim_seller s
11    JOIN fact_order_items oi ON s.seller_id = oi.seller_id
12    JOIN fact_orders o ON oi.order_id = o.order_id
13    WHERE o.status = 'Delivered'
14    GROUP BY s.seller_id
15)
16SELECT
17    CASE
18        WHEN ROW_NUMBER() OVER (ORDER BY gmv DESC) <= 10 THEN 'Top 10'
19        WHEN ROW_NUMBER() OVER (ORDER BY gmv DESC) <= 50 THEN 'Top 11-50'
20        WHEN ROW_NUMBER() OVER (ORDER BY gmv DESC) <= 100 THEN 'Top 51-100'
21        ELSE 'Others (400+)'
22    END AS seller_group,
23    COUNT(*) AS num_sellers,
24    SUM(gmv) AS group_gmv,
25    ROUND(SUM(gmv) * 100.0 / MAX(total_gmv), 1) AS gmv_share_pct
26FROM seller_gmv
27GROUP BY CASE
28    WHEN ROW_NUMBER() OVER (ORDER BY gmv DESC) <= 10 THEN 'Top 10'
29    WHEN ROW_NUMBER() OVER (ORDER BY gmv DESC) <= 50 THEN 'Top 11-50'
30    WHEN ROW_NUMBER() OVER (ORDER BY gmv DESC) <= 100 THEN 'Top 51-100'
31    ELSE 'Others (400+)'
32END;