Contoso Retail: End-to-End SQL Analysis

SELECT
    YEAR(datekey)                                    AS year,
    MONTH(datekey)                                   AS month,
    COUNT(DISTINCT SalesKey)                         AS total_orders,
    ROUND(SUM(salesamount))                          AS total_revenue,
    ROUND(SUM(salesamount) - SUM(TotalCost))         AS total_profit
FROM factsales
GROUP BY 1, 2
ORDER BY 1, 2;

WITH product_category_data AS (
    SELECT p.productkey, pc.ProductCategoryName,
           fs.salesamount, fs.TotalCost
    FROM factsales fs
    JOIN dimproduct p USING (ProductKey)
    JOIN dimproductsubcategory ps USING (ProductSubcategoryKey)
    JOIN dimproductcategory pc USING (ProductCategoryKey)
    UNION ALL
    SELECT p.productkey, pc.ProductCategoryName,
           os.salesamount, os.TotalCost
    FROM factonlinesales os
    JOIN dimproduct p USING (ProductKey)
    JOIN dimproductsubcategory ps USING (ProductSubcategoryKey)
    JOIN dimproductcategory pc USING (ProductCategoryKey)
)
SELECT
    ProductCategoryName,
    ROUND(SUM(salesamount) / 1e9, 2)                AS revenue_bn,
    ROUND((SUM(salesamount) - SUM(TotalCost))
          / 1e9, 2)                                 AS profit_bn,
    ROUND((SUM(salesamount) - SUM(TotalCost))
          / SUM(salesamount) * 100, 1)              AS profit_margin_pct
FROM product_category_data
GROUP BY 1
ORDER BY profit_bn DESC;

SELECT
    c.CustomerType,
    COUNT(DISTINCT os.SalesOrderNumber)  AS total_orders,
    ROUND(SUM(os.salesamount) / 1e9, 2)   AS revenue_bn
FROM factonlinesales os
JOIN dimcustomer c USING (CustomerKey)
GROUP BY 1
ORDER BY revenue_bn DESC;

WITH all_sales AS (
    SELECT ch.ChannelName, fs.salesamount, fs.TotalCost
    FROM factsales fs
    JOIN dimchannel ch USING (ChannelKey)
    UNION ALL
    SELECT 'Online' AS ChannelName,
           os.salesamount, os.TotalCost
    FROM factonlinesales os
)
SELECT
    ChannelName,
    ROUND(SUM(salesamount) / 1e9, 2)                AS revenue_bn,
    ROUND((SUM(salesamount) - SUM(TotalCost))
          / SUM(salesamount) * 100, 1)              AS profit_margin_pct
FROM all_sales
GROUP BY 1
ORDER BY revenue_bn DESC;

WITH all_sales AS (
    SELECT g.RegionCountryName, fs.salesamount, fs.TotalCost
    FROM factsales fs
    JOIN dimstore st USING (StoreKey)
    JOIN dimgeography g USING (GeographyKey)
    UNION ALL
    SELECT g.RegionCountryName, os.salesamount, os.TotalCost
    FROM factonlinesales os
    JOIN dimcustomer c USING (CustomerKey)
    JOIN dimgeography g USING (GeographyKey)
)
SELECT
    RegionCountryName,
    ROUND(SUM(salesamount) / 1e9, 2)  AS revenue_bn,
    ROUND((SUM(salesamount) - SUM(TotalCost))
          / SUM(salesamount) * 100, 1) AS margin_pct
FROM all_sales
GROUP BY 1
ORDER BY revenue_bn DESC
LIMIT 10;

WITH rfm_base AS (
    SELECT
        os.customerkey,
        c.CustomerLabel,
        (SELECT MAX(datekey) FROM factonlinesales) AS dataset_max_date,
        MAX(os.datekey)                             AS last_purchase_date,
        COUNT(DISTINCT os.SalesOrderNumber)          AS frequency,
        ROUND(SUM(os.salesamount))                   AS monetary
    FROM factonlinesales os
    JOIN dimcustomer c USING (CustomerKey)
    WHERE c.CustomerType = 'Person'
    GROUP BY 1, 2
    HAVING COUNT(DISTINCT os.SalesOrderNumber) >= 2
),
rfm_scored AS (
    SELECT customerkey, CustomerLabel,
        DATEDIFF(dataset_max_date, last_purchase_date) AS recency_days,
        frequency, monetary,
        NTILE(5) OVER (
            ORDER BY DATEDIFF(dataset_max_date, last_purchase_date) ASC
        )                                              AS R,
        NTILE(5) OVER (ORDER BY frequency DESC)      AS F,
        NTILE(5) OVER (ORDER BY monetary DESC)       AS M
    FROM rfm_base
)
SELECT *, CONCAT(R,F,M) AS rfm_score,
    CASE
        WHEN R >= 4 AND F >= 4 AND M >= 4  THEN 'Champions'
        WHEN R >= 3 AND F >= 3              THEN 'Loyal Customers'
        WHEN R >= 4 AND F <= 2              THEN 'Recent Customers'
        WHEN R <= 2 AND F >= 3 AND M >= 3  THEN 'At Risk'
        WHEN R <= 2 AND F <= 2 AND M <= 2  THEN 'Lost'
        ELSE                                      'Potential Loyalists'
    END                                       AS customer_segment
FROM rfm_scored
ORDER BY monetary DESC;

SELECT
    c.CustomerKey, c.CustomerLabel, c.CustomerType,
    COUNT(DISTINCT os.SalesOrderNumber)              AS total_orders,
    ROUND(SUM(os.salesamount))                       AS total_spend,
    ROUND(
        DATEDIFF(MAX(os.datekey), MIN(os.datekey))
        / 365.0, 2)                                  AS tenure_years,
    ROUND(
        SUM(os.salesamount) /
        NULLIF(DATEDIFF(MAX(os.datekey),
               MIN(os.datekey)) / 365.0, 0))        AS estimated_clv
FROM factonlinesales os
JOIN dimcustomer c USING (CustomerKey)
GROUP BY 1, 2, 3
ORDER BY estimated_clv DESC
LIMIT 5;

WITH customer_demo AS (
    SELECT
        c.CustomerType, c.Gender,
        CASE
            WHEN (2009 - YEAR(c.BirthDate)) < 35  THEN 'Young Adult'
            WHEN (2009 - YEAR(c.BirthDate)) < 55  THEN 'Middle Age'
            ELSE                                       'Senior'
        END                                        AS AgeGroup,
        os.salesamount, os.TotalCost
    FROM factonlinesales os
    JOIN dimcustomer c USING (CustomerKey)
)
SELECT
    CustomerType, Gender, AgeGroup,
    ROUND(SUM(salesamount) / 1e9, 2)  AS revenue_bn,
    ROUND((SUM(salesamount) - SUM(TotalCost))
          / SUM(salesamount) * 100, 1) AS margin_pct
FROM customer_demo
GROUP BY 1, 2, 3
ORDER BY revenue_bn DESC;

WITH first_purchase AS (
    SELECT CustomerKey,
        DATE_FORMAT(MIN(datekey), '%Y-%m-01') AS cohort_month
    FROM factonlinesales
    GROUP BY 1
),
monthly_activity AS (
    SELECT DISTINCT CustomerKey,
        DATE_FORMAT(datekey, '%Y-%m-01')      AS activity_month
    FROM factonlinesales
),
cohort_data AS (
    SELECT
        fp.cohort_month, ma.activity_month,
        TIMESTAMPDIFF(MONTH, fp.cohort_month,
                      ma.activity_month)           AS month_number,
        COUNT(DISTINCT ma.CustomerKey)           AS active_customers
    FROM first_purchase fp
    JOIN monthly_activity ma USING (CustomerKey)
    GROUP BY 1, 2, 3
)
SELECT
    cd.cohort_month,
    cs.cohort_customers,
    cd.month_number,
    cd.active_customers,
    ROUND(cd.active_customers
          / cs.cohort_customers * 100, 1)         AS retention_rate
FROM cohort_data cd
JOIN cohort_size cs USING (cohort_month)
ORDER BY cd.cohort_month, cd.month_number;

WITH ranked AS (
    SELECT ProductKey, ProductName, total_revenue,
        SUM(total_revenue) OVER ()                  AS grand_total,
        SUM(total_revenue) OVER (
            ORDER BY total_revenue DESC
            ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW
        )                                            AS cumulative_revenue
    FROM product_totals
)
SELECT *,
    CASE
        WHEN cumulative_revenue / grand_total <= 0.70 THEN 'A'
        WHEN cumulative_revenue / grand_total <= 0.90 THEN 'B'
        ELSE                                               'C'
    END                                              AS abc_tier
FROM ranked
ORDER BY total_revenue DESC;