Analytics and Dashboarding Questions

-- Postgres/Redshift style: compute daily 95th per endpoint
SELECT endpoint, day,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_ms
FROM raw_logs
WHERE day BETWEEN '2025-11-01' AND '2025-11-30'
GROUP BY endpoint, day;

from tdigest import TDigest
# build per-partition
td = TDigest()
for x in latencies:
    td.update(x)
blob = td.compress().serialize()  # store blob in table

SELECT endpoint, day,
  quantile_from_merged_tdigest(collect(digest_blob), 0.95) AS p95
FROM endpoint_day_digests
GROUP BY endpoint, day;

WITH cohorts AS (
  SELECT
    customer_id,
    date_trunc('week', signup_date)::date AS cohort_week,
    signup_date::date AS signup_date
  FROM customers
),
orders_with_rates AS (
  SELECT
    o.order_id,
    o.customer_id,
    o.order_date::date AS order_date,
    o.amount,
    o.currency,
    -- pick the latest exchange rate on or before order_date
    er.usd_rate
  FROM orders o
  LEFT JOIN LATERAL (
    SELECT usd_rate
    FROM exchange_rates er
    WHERE er.currency = o.currency
      AND er.exchange_date <= o.order_date::date
    ORDER BY er.exchange_date DESC
    LIMIT 1
  ) er ON true
),
orders_enriched AS (
  SELECT
    owr.*,
    c.cohort_week,
    c.signup_date,
    FLOOR(EXTRACT(EPOCH FROM (owr.order_date - c.signup_date)) / 604800)::int AS week_number, -- 7*24*60*60
    -- convert to USD; if no rate found, treat as NULL (could filter or impute)
    (owr.amount * owr.usd_rate) AS amount_usd
  FROM orders_with_rates owr
  JOIN cohorts c USING (customer_id)
)
SELECT
  cohort_week,
  week_number,
  SUM(COALESCE(amount_usd, 0)) AS ltv_usd
FROM orders_enriched
WHERE week_number BETWEEN 0 AND 11
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number;