-- Core business metrics

-- Revenue metrics
SELECT
  DATE_TRUNC('month', order_date)::DATE as month,
  ROUND(SUM(amount), 2) as total_revenue,
  COUNT(DISTINCT order_id) as order_count,
  ROUND(SUM(amount) / COUNT(DISTINCT order_id), 2) as avg_order_value,
  COUNT(DISTINCT customer_id) as unique_customers,
  ROUND(SUM(amount) / COUNT(DISTINCT customer_id), 2) as revenue_per_customer
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;

-- Customer metrics
SELECT
  customer_id,
  COUNT(DISTINCT order_id) as lifetime_orders,
  ROUND(SUM(amount), 2) as lifetime_value,
  MIN(order_date) as first_order_date,
  MAX(order_date) as last_order_date,
  ROUND(DATEDIFF(DAY, MIN(order_date), MAX(order_date)) /
    NULLIF(COUNT(DISTINCT order_id) - 1, 0), 2) as avg_days_between_orders,
  ROUND(SUM(amount) / DATEDIFF(DAY, MIN(order_date), CURRENT_DATE), 4) as revenue_per_day
FROM orders
GROUP BY customer_id;

-- Product performance
SELECT
  product_id,
  product_name,
  category,
  COUNT(DISTINCT order_id) as order_count,
  SUM(quantity) as units_sold,
  ROUND(SUM(revenue), 2) as total_revenue,
  ROUND(AVG(revenue), 2) as avg_order_value,
  ROUND(SUM(profit), 2) as total_profit,
  ROUND(100.0 * SUM(profit) / NULLIF(SUM(revenue), 0), 2) as profit_margin_pct
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY product_id, product_name, category
ORDER BY total_revenue DESC;

-- Key Performance Indicator calculations

-- Monthly Active Users (MAU)
SELECT
  DATE_TRUNC('month', activity_date)::DATE as month,
  COUNT(DISTINCT user_id) as mau
FROM user_activity
GROUP BY DATE_TRUNC('month', activity_date);

-- Customer Acquisition Cost (CAC)
SELECT
  DATE_TRUNC('month', acquired_date)::DATE as month,
  COUNT(DISTINCT customer_id) as new_customers,
  ROUND(SUM(marketing_spend) / COUNT(DISTINCT customer_id), 2) as cac
FROM customers c
JOIN marketing_spend m ON EXTRACT(YEAR FROM c.acquired_date) = EXTRACT(YEAR FROM m.spend_date)
  AND EXTRACT(MONTH FROM c.acquired_date) = EXTRACT(MONTH FROM m.spend_date)
GROUP BY DATE_TRUNC('month', acquired_date);

-- Customer Retention Rate
WITH monthly_activity AS (
  SELECT
    DATE_TRUNC('month', activity_date)::DATE as month,
    customer_id
  FROM orders
  GROUP BY DATE_TRUNC('month', activity_date), customer_id
)
SELECT
  current_month.month,
  COUNT(DISTINCT current_month.customer_id) as current_month_customers,
  COUNT(DISTINCT previous_month.customer_id) as retained_customers,
  ROUND(100.0 * COUNT(DISTINCT previous_month.customer_id) /
    COUNT(DISTINCT current_month.customer_id), 2) as retention_rate_pct
FROM monthly_activity current_month
LEFT JOIN monthly_activity previous_month
  ON current_month.customer_id = previous_month.customer_id
  AND current_month.month = previous_month.month + INTERVAL '1 month'
GROUP BY current_month.month
ORDER BY current_month.month;

-- Net Promoter Score (NPS) calculation
SELECT
  department,
  COUNT(CASE WHEN nps_score >= 9 THEN 1 END) as promoters,
  COUNT(CASE WHEN nps_score >= 7 AND nps_score <= 8 THEN 1 END) as passives,
  COUNT(CASE WHEN nps_score <= 6 THEN 1 END) as detractors,
  COUNT(*) as total_responses,
  ROUND(100.0 * (COUNT(CASE WHEN nps_score >= 9 THEN 1 END) -
    COUNT(CASE WHEN nps_score <= 6 THEN 1 END)) / COUNT(*), 1) as nps_score
FROM customer_surveys
GROUP BY department;

-- Atomic grain (transaction-level)
CREATE TABLE fact_sales_atomic (
  transaction_id BIGINT PRIMARY KEY,
  date_id INT,
  customer_id INT,
  product_id INT,
  store_id INT,
  quantity INT,
  unit_price DECIMAL(10, 2),
  net_sales DECIMAL(12, 2),
  FOREIGN KEY (date_id) REFERENCES dim_date(date_id)
);

-- Summary grain (aggregated for performance)
CREATE TABLE fact_sales_summary (
  summary_id BIGINT PRIMARY KEY,
  date_id INT,
  customer_segment VARCHAR(50),
  product_category VARCHAR(50),
  store_region VARCHAR(50),
  transaction_count INT,
  total_quantity INT,
  total_sales DECIMAL(15, 2),
  FOREIGN KEY (date_id) REFERENCES dim_date(date_id)
);

-- Optimized for dashboard performance using pre-aggregations
SELECT
  d.month_name,
  d.quarter,
  d.year,
  dpc.product_category,
  dcs.customer_segment,
  COUNT(*) as transaction_count,
  SUM(fss.total_quantity) as units_sold,
  ROUND(SUM(fss.total_sales), 2) as revenue,
  ROUND(SUM(fss.total_sales) / COUNT(*), 2) as avg_transaction_value,
  ROUND(SUM(fss.total_sales) / NULLIF(COUNT(DISTINCT dcs.customer_id), 0), 2) as revenue_per_customer
FROM fact_sales_summary fss
JOIN dim_date d ON fss.date_id = d.date_id
JOIN dim_product_category dpc ON fss.product_category = dpc.category_id
JOIN dim_customer_segment dcs ON fss.customer_segment = dcs.segment_id
WHERE d.year = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY d.month_name, d.quarter, d.year, dpc.product_category, dcs.customer_segment
ORDER BY d.year DESC, d.quarter DESC, d.month_name DESC;

-- Year-over-year comparison
SELECT
  EXTRACT(MONTH FROM order_date) as month,
  EXTRACT(YEAR FROM order_date) as year,
  ROUND(SUM(amount), 2) as monthly_revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY year DESC, month;

-- Budget vs Actual variance
SELECT
  department,
  EXTRACT(MONTH FROM report_date) as month,
  SUM(budgeted_amount) as budget,
  SUM(actual_amount) as actual,
  SUM(actual_amount) - SUM(budgeted_amount) as variance,
  ROUND(100.0 * (SUM(actual_amount) - SUM(budgeted_amount)) /
    NULLIF(SUM(budgeted_amount), 0), 2) as variance_pct
FROM budget_actuals
GROUP BY department, EXTRACT(MONTH FROM report_date)
ORDER BY department, month DESC;

-- Cumulative variance analysis
WITH monthly_budget AS (
  SELECT
    department,
    EXTRACT(MONTH FROM report_date) as month,
    SUM(budgeted_amount) as budget,
    SUM(actual_amount) as actual
  FROM budget_actuals
  GROUP BY department, EXTRACT(MONTH FROM report_date)
)
SELECT
  department,
  month,
  budget,
  actual,
  SUM(actual) OVER (PARTITION BY department ORDER BY month) as ytd_actual,
  SUM(budget) OVER (PARTITION BY department ORDER BY month) as ytd_budget,
  SUM(actual) OVER (PARTITION BY department ORDER BY month) -
    SUM(budget) OVER (PARTITION BY department ORDER BY month) as ytd_variance
FROM monthly_budget
ORDER BY department, month;

-- Cohort lifetime value
WITH user_cohorts AS (
  SELECT
    DATE_TRUNC('month', customer_acquired_date)::DATE as cohort_month,
    customer_id,
    DATE_TRUNC('month', order_date)::DATE as order_month,
    amount
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
)
SELECT
  cohort_month,
  DATE_PART('month', order_month::timestamp - cohort_month::timestamp) / 1 as months_since_acquisition,
  COUNT(DISTINCT customer_id) as cohort_size,
  ROUND(SUM(amount), 2) as cohort_revenue
FROM user_cohorts
WHERE order_month >= cohort_month
GROUP BY cohort_month, months_since_acquisition
ORDER BY cohort_month, months_since_acquisition;

-- Customer segmentation with RFM analysis
WITH rfm AS (
  SELECT
    customer_id,
    MAX(order_date) as last_order_date,
    DATEDIFF(DAY, MAX(order_date), CURRENT_DATE) as recency,
    COUNT(DISTINCT order_id) as frequency,
    ROUND(SUM(amount), 2) as monetary,
    NTILE(4) OVER (ORDER BY DATEDIFF(DAY, MAX(order_date), CURRENT_DATE) DESC) as r_score,
    NTILE(4) OVER (ORDER BY COUNT(DISTINCT order_id)) as f_score,
    NTILE(4) OVER (ORDER BY SUM(amount)) as m_score
  FROM orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  CASE
    WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Champions'
    WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
    WHEN f_score >= 3 THEN 'At Risk'
    WHEN r_score = 4 THEN 'Lost'
    ELSE 'Other'
  END as segment,
  frequency,
  monetary,
  recency
FROM rfm
ORDER BY monetary DESC;