Retail Sales SQL Portfolio

SELECT
  YEAR(order_date)  AS order_year,
  MONTH(order_date) AS order_month,
  SUM(sales_amount) AS total_sales,
  COUNT(DISTINCT customer_key) AS unique_customers
FROM gold.fact_sales
WHERE order_date IS NOT NULL
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year, order_month;

WITH m AS (
  SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start,
         SUM(sales_amount) AS monthly_sales
  FROM gold.fact_sales
  WHERE order_date IS NOT NULL
  GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1)
)
SELECT month_start,
       monthly_sales,
       SUM(monthly_sales) OVER (ORDER BY month_start) AS cumulative_sales
FROM m
ORDER BY month_start;

SELECT
  p.category,
  CAST(SUM(f.sales_amount)*100.0 / SUM(SUM(f.sales_amount)) OVER() AS decimal(5,2)) AS pct_total
FROM gold.fact_sales f
JOIN gold.dim_products p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY pct_total DESC;

SELECT TOP (10)
  p.product_name, p.category,
  SUM(f.sales_amount) AS total_sales,
  SUM(f.quantity)     AS units
FROM gold.fact_sales f
JOIN gold.dim_products p ON p.product_key = f.product_key
GROUP BY p.product_name, p.category
ORDER BY total_sales DESC;

-- Top 10 customers by LTV + order count
SELECT TOP (10)
  c.customer_number,
  c.first_name, c.last_name,
  SUM(f.sales_amount)            AS lifetime_value,
  COUNT(DISTINCT f.order_number) AS orders
FROM gold.fact_sales f
JOIN gold.dim_customers c ON c.customer_key = f.customer_key
GROUP BY c.customer_number, c.first_name, c.last_name
ORDER BY lifetime_value DESC;

-- Cumulative contribution curve (ranked by LTV)
WITH cust AS (
  SELECT c.customer_number, SUM(f.sales_amount) AS ltv
  FROM gold.fact_sales f
  JOIN gold.dim_customers c ON c.customer_key = f.customer_key
  GROUP BY c.customer_number
),
r AS (
  SELECT
    customer_number,
    ltv,
    ROW_NUMBER() OVER (ORDER BY ltv DESC)                      AS rk,
    SUM(ltv) OVER (ORDER BY ltv DESC ROWS UNBOUNDED PRECEDING) AS cum_ltv,
    SUM(ltv) OVER ()                                           AS total_ltv
  FROM cust
)
SELECT TOP (20)
  rk, customer_number, ltv,
  CAST(cum_ltv*100.0/total_ltv AS decimal(6,2)) AS cumulative_pct
FROM r
ORDER BY rk;