Analytical SQL for Business Intelligence

-- COHORT ANALYSIS: group users by signup month, track retention

WITH cohorts AS (
    SELECT customer_id,
           DATE_TRUNC('month', first_order_date) AS cohort_month
    FROM (
        SELECT customer_id, MIN(order_date) AS first_order_date
        FROM orders GROUP BY customer_id
    ) first_orders
),
monthly_activity AS (
    SELECT o.customer_id,
           c.cohort_month,
           DATE_TRUNC('month', o.order_date) AS activity_month,
           EXTRACT(MONTH FROM AGE(
               
...

-- FUNNEL ANALYSIS: track conversion through stages

WITH funnel AS (
    SELECT
        session_id,
        MAX(CASE WHEN event = 'page_view' THEN 1 ELSE 0 END) AS viewed,
        MAX(CASE WHEN event = 'add_to_cart' THEN 1 ELSE 0 END) AS added,
        MAX(CASE WHEN event = 'checkout_start' THEN 1 ELSE 0 END) AS checkout,
        MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY session_id
)
SE
...

-- BI-GRADE WINDOW FUNCTIONS

-- Year-over-Year comparison:
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue,
    LAG(SUM(total), 12) OVER (ORDER BY DATE_TRUNC('month', order_date))
        AS revenue_last_year,
    ROUND(
        (SUM(total) - LAG(SUM(total), 12) OVER (
            ORDER BY DATE_TRUNC('month', order_date)
        )) / NULLIF(LAG(SUM(total), 12) OVER (
            ORDER BY DATE_TRUNC('month', order_date)
        ), 0) * 100, 1
    ) AS yoy_growth_pc
...

-- ROLLUP for BI dashboards

-- Sales dashboard with drill-down levels:
SELECT
    COALESCE(region, '🌍 GLOBAL') AS region,
    COALESCE(category, '📦 ALL CATEGORIES') AS category,
    COALESCE(TO_CHAR(DATE_TRUNC('quarter', sale_date), 'YYYY-"Q"Q'),
             '📅 ALL TIME') AS quarter,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers,
    ROUND(SUM(revenue) / NULLIF(COUNT(DISTINCT customer_id), 0), 2)
        AS revenue_per_customer,
    COUNT(*) AS trans
...

-- KPI QUERIES for executive dashboards

-- Customer Lifetime Value (LTV):
SELECT
    ROUND(AVG(lifetime_value), 2) AS avg_ltv,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY lifetime_value
    ), 2) AS median_ltv
FROM (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM orders
    GROUP BY customer_id
) customer_ltv;

-- Monthly Recurring Revenue (MRR):
SELECT DATE_TRUNC('month', billing_date) AS month,
    SUM(CASE WHEN type = 'new' THEN amount END) AS new_mrr,
    S
...