Analytics Fundamentals

-- One row per attempt (a payment intent can have many attempts)
auth_attempts (
  attempt_id        UUID PRIMARY KEY,
  payment_intent_id UUID,           -- stable across retries
  attempt_seq       INT,            -- 1, 2, 3...
  user_id           UUID,
  rail              TEXT,           -- 'card','upi','pix','sepa_inst','fps','ob_pis'
  geo               TEXT,           -- ISO country code
  psp_name          TEXT,
  acquirer          TEXT,
  bin               TEXT,
  issuer_country    TEXT,
  issuer_name       TEXT,
  mcc               TEXT,
  scheme            TEXT,           -- 'visa','mc','elo','rupay','mada'...
  amount_cents      BIGINT,
  currency          CHAR(3),
  status            TEXT,           -- 'approved','soft_decline','hard_decline','timeout','3ds_pending'
  decline_reason    TEXT,
  decline_family    TEXT,           -- 'soft','hard','stepup','system'
  three_ds_result   TEXT,           -- 'frictionless','challenge_passed','failed','not_attempted'
  network_token     BOOLEAN,
  fee_interchange_c INT,
  fee_scheme_c      INT,
  fee_psp_c         INT,
  fee_xborder_c     INT,
  fee_fx_c          INT,
  auth_latency_ms   INT,
  created_at        TIMESTAMP,
  responded_at      TIMESTAMP
);

settlements (
  settlement_id   UUID,
  payment_intent_id UUID,
  settled_at      TIMESTAMP,
  settled_amount_c BIGINT,
  psp_name        TEXT
);

disputes (
  dispute_id    UUID,
  payment_intent_id UUID,
  filed_at      TIMESTAMP,
  reason_code   TEXT,
  amount_c      BIGINT,
  status        TEXT  -- 'open','won','lost','accepted'
);

-- Funnel: intents → first attempt → approved → settled, by rail, last 30d
WITH intents AS (
  SELECT payment_intent_id, MIN(rail) AS rail
  FROM auth_attempts
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY payment_intent_id
),
first_attempt AS (
  SELECT DISTINCT ON (payment_intent_id)
    payment_intent_id, status
  FROM auth_attempts
  ORDER BY payment_intent_id, attempt_seq ASC
),
final AS (
  SELECT payment_intent_id,
    BOOL_OR(status='approved') AS ever_approved
  FROM auth_attempts
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY payment_intent_id
)
SELECT
  i.rail,
  COUNT(*) AS intents,
  COUNT(*) FILTER (WHERE f.ever_approved) AS approved_eventually,
  COUNT(*) FILTER (WHERE fa.status='approved') AS approved_first_attempt,
  COUNT(s.payment_intent_id) AS settled
FROM intents i
LEFT JOIN first_attempt fa USING (payment_intent_id)
LEFT JOIN final f USING (payment_intent_id)
LEFT JOIN settlements s USING (payment_intent_id)
GROUP BY i.rail
ORDER BY intents DESC;

-- First-attempt AAR by issuer × BIN × MCC × issuer_country, last 14 days
WITH first_attempts AS (
  SELECT DISTINCT ON (payment_intent_id)
    payment_intent_id, issuer_name, bin, mcc, issuer_country, status
  FROM auth_attempts
  WHERE created_at >= NOW() - INTERVAL '14 days'
  ORDER BY payment_intent_id, attempt_seq ASC
)
SELECT
  issuer_name, bin, mcc, issuer_country,
  COUNT(*) AS attempts,
  COUNT(*) FILTER (WHERE status='approved') AS approved,
  ROUND(100.0 * COUNT(*) FILTER (WHERE status='approved') / COUNT(*), 2) AS aar_pct
FROM first_attempts
GROUP BY issuer_name, bin, mcc, issuer_country
HAVING COUNT(*) >= 200
ORDER BY aar_pct ASC;
-- The bottom of this list is your AAR investigation queue.

-- Retry success rate by decline_family on first attempt, last 30d
WITH first AS (
  SELECT payment_intent_id,
    MIN(attempt_seq) FILTER (WHERE status='approved')  AS approved_seq,
    MAX(attempt_seq) AS max_seq,
    FIRST_VALUE(decline_family) OVER (
      PARTITION BY payment_intent_id ORDER BY attempt_seq
    ) AS first_decline_family,
    FIRST_VALUE(status) OVER (
      PARTITION BY payment_intent_id ORDER BY attempt_seq
    ) AS first_status
  FROM auth_attempts
  WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT
  first_decline_family,
  COUNT(DISTINCT payment_intent_id) AS first_declined,
  COUNT(DISTINCT payment_intent_id) FILTER (WHERE approved_seq IS NOT NULL) AS recovered,
  ROUND(100.0 * COUNT(DISTINCT payment_intent_id) FILTER (WHERE approved_seq IS NOT NULL)
        / NULLIF(COUNT(DISTINCT payment_intent_id), 0), 2) AS recovery_pct
FROM first
WHERE first_status <> 'approved'
GROUP BY first_decline_family;

-- All-in cost per successful payment, last 30d, by rail + geo
WITH per_intent AS (
  SELECT
    payment_intent_id,
    MIN(rail) AS rail,
    MIN(geo) AS geo,
    BOOL_OR(status='approved') AS approved,
    SUM(fee_interchange_c + fee_scheme_c + fee_psp_c + fee_xborder_c + fee_fx_c) AS total_fees_c
  FROM auth_attempts
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY payment_intent_id
)
SELECT
  rail, geo,
  COUNT(*) FILTER (WHERE approved) AS successes,
  SUM(total_fees_c) FILTER (WHERE approved) AS total_cost_c,
  ROUND(SUM(total_fees_c) FILTER (WHERE approved)::numeric
        / NULLIF(COUNT(*) FILTER (WHERE approved), 0), 2) AS cost_per_success_c
FROM per_intent
GROUP BY rail, geo
ORDER BY rail, geo;

-- Compare expected settlement vs actual, by PSP, by settlement date
WITH expected AS (
  SELECT
    DATE(responded_at + INTERVAL '1 day') AS expected_settle_date,
    psp_name,
    SUM(amount_cents) AS expected_amount_c,
    COUNT(*) AS expected_count
  FROM auth_attempts
  WHERE status='approved'
    AND responded_at >= NOW() - INTERVAL '14 days'
  GROUP BY 1, 2
),
actual AS (
  SELECT
    DATE(settled_at) AS settle_date,
    psp_name,
    SUM(settled_amount_c) AS actual_amount_c,
    COUNT(*) AS actual_count
  FROM settlements
  WHERE settled_at >= NOW() - INTERVAL '14 days'
  GROUP BY 1, 2
)
SELECT
  COALESCE(e.expected_settle_date, a.settle_date) AS d,
  COALESCE(e.psp_name, a.psp_name) AS psp_name,
  e.expected_amount_c, a.actual_amount_c,
  e.expected_count, a.actual_count,
  (a.actual_amount_c - e.expected_amount_c) AS delta_amount_c
FROM expected e
FULL OUTER JOIN actual a
  ON e.expected_settle_date = a.settle_date AND e.psp_name = a.psp_name
ORDER BY d DESC, psp_name;

-- Repeat-deposit retention by first-deposit rail (weekly cohort), 8 weeks
WITH first_deposit AS (
  SELECT
    user_id,
    DATE_TRUNC('week', MIN(created_at)) AS cohort_wk,
    (ARRAY_AGG(rail ORDER BY created_at))[1] AS first_rail
  FROM auth_attempts
  WHERE status='approved'
  GROUP BY user_id
),
subsequent AS (
  SELECT
    a.user_id,
    fd.cohort_wk,
    fd.first_rail,
    DATE_TRUNC('week', a.created_at) AS active_wk
  FROM auth_attempts a
  JOIN first_deposit fd USING (user_id)
  WHERE a.status='approved'
)
SELECT
  cohort_wk,
  first_rail,
  active_wk,
  COUNT(DISTINCT user_id) AS active_users
FROM subsequent
WHERE active_wk >= cohort_wk
  AND cohort_wk >= NOW() - INTERVAL '8 weeks'
GROUP BY cohort_wk, first_rail, active_wk
ORDER BY cohort_wk, first_rail, active_wk;