Every number tells a story.

WITH first_orders AS (
    SELECT
        customer_id,
        email,
        MIN(processed_at)   AS first_order_date,
        MIN(order_id)       AS first_order_id
    FROM matrixify.orders
    WHERE payment_status = 'paid'
      AND cancelled_at IS NULL
    GROUP BY customer_id, email
),
second_orders AS (
    SELECT DISTINCT o.customer_id
    FROM matrixify.orders o
    JOIN first_orders f USING(customer_id)
    WHERE o.order_id != f.first_order_id
      AND o.payment_status = 'paid'
      AND DATEDIFF('day', f.first_order_date, o.processed_at) BETWEEN 1 AND 90
)
SELECT
    DATE_TRUNC('month', f.first_order_date)         AS cohort_month,
    COUNT(f.customer_id)                             AS cohort_size,
    COUNT(s.customer_id)                             AS repeated_90d,
    ROUND(COUNT(s.customer_id) * 100.0
        / NULLIF(COUNT(f.customer_id), 0), 1)        AS repeat_rate_90d_pct
FROM first_orders f
LEFT JOIN second_orders s USING(customer_id)
GROUP BY 1
ORDER BY 1 DESC
LIMIT 6

WITH first_orders AS (
    SELECT customer_id,
           DATE_TRUNC('month', MIN(processed_at)) AS cohort_month,
           MIN(processed_at) AS first_date
    FROM matrixify.orders
    WHERE payment_status = 'paid' AND cancelled_at IS NULL
    GROUP BY customer_id
),
retention AS (
    SELECT
        f.customer_id,
        f.cohort_month,
        MAX(CASE WHEN DATEDIFF('day', f.first_date, o.processed_at) BETWEEN 1  AND 30  THEN 1 ELSE 0 END) AS retained_30,
        MAX(CASE WHEN DATEDIFF('day', f.first_date, o.processed_at) BETWEEN 1  AND 60  THEN 1 ELSE 0 END) AS retained_60,
        MAX(CASE WHEN DATEDIFF('day', f.first_date, o.processed_at) BETWEEN 1  AND 90  THEN 1 ELSE 0 END) AS retained_90
    FROM first_orders f
    LEFT JOIN matrixify.orders o
        ON f.customer_id = o.customer_id
       AND o.processed_at > f.first_date
       AND o.payment_status = 'paid'
    GROUP BY f.customer_id, f.cohort_month
)
SELECT
    cohort_month,
    COUNT(*)                                             AS cohort_size,
    ROUND(AVG(retained_30) * 100, 1)                    AS retention_30d_pct,
    ROUND(AVG(retained_60) * 100, 1)                    AS retention_60d_pct,
    ROUND(AVG(retained_90) * 100, 1)                    AS retention_90d_pct
FROM retention
GROUP BY cohort_month
ORDER BY cohort_month DESC
LIMIT 6

WITH first_orders AS (
    SELECT customer_id,
           MIN(processed_at) AS first_date,
           MIN(order_id)     AS first_order_id
    FROM matrixify.orders
    WHERE payment_status = 'paid' AND cancelled_at IS NULL
    GROUP BY customer_id
),
second_orders AS (
    SELECT o.customer_id,
           MIN(o.processed_at) AS second_date
    FROM matrixify.orders o
    JOIN first_orders f USING(customer_id)
    WHERE o.order_id != f.first_order_id
      AND o.payment_status = 'paid'
      AND o.cancelled_at IS NULL
    GROUP BY o.customer_id
)
SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY DATEDIFF('day', f.first_date, s.second_date)) AS p25_days,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY DATEDIFF('day', f.first_date, s.second_date)) AS p50_days,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY DATEDIFF('day', f.first_date, s.second_date)) AS p75_days,
    ROUND(AVG(DATEDIFF('day', f.first_date, s.second_date)), 1)                                 AS mean_days,
    COUNT(*)                                                                                     AS sample_size
FROM first_orders f
JOIN second_orders s USING(customer_id)
WHERE DATEDIFF('day', f.first_date, s.second_date) BETWEEN 1 AND 180

WITH customer_ltv AS (
    SELECT
        o.email,
        p.churn_risk,
        p.predicted_clv_1y,
        CASE
            WHEN o.shipping_country = 'US' THEN 'US'
            WHEN o.shipping_country = 'GB' THEN 'UK'
            ELSE 'Other'
        END                                          AS market,
        SUM(o.total_price_usd)                       AS actual_ltv,
        COUNT(o.order_id)                            AS total_orders,
        DATEDIFF('day', MIN(o.processed_at), MAX(o.processed_at)) AS customer_tenure_days
    FROM matrixify.orders o
    LEFT JOIN klaviyo.profiles p USING(email)
    WHERE o.payment_status = 'paid'
      AND o.cancelled_at IS NULL
    GROUP BY o.email, p.churn_risk, p.predicted_clv_1y, market
)
SELECT
    market,
    ROUND(AVG(actual_ltv), 2)                        AS avg_actual_ltv,
    ROUND(AVG(predicted_clv_1y), 2)                  AS avg_predicted_ltv,
    ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY actual_ltv), 2) AS p75_ltv,
    ROUND(AVG(total_orders), 1)                      AS avg_orders,
    COUNT(*)                                         AS customer_count
FROM customer_ltv
WHERE market IN ('US', 'UK')
GROUP BY market
ORDER BY market