-- One current row per contract / balance / transition (snapshot tables): latest export wins
WITH contracts AS (
SELECT id, customer_id, name, starting_at, ending_before, archived_at, created_at,
json_extract_scalar(metadata, '$.custom_fields.contract_type') AS contract_type
FROM (SELECT c.*, row_number() OVER (PARTITION BY c.id ORDER BY c.updated_at DESC) AS rn
FROM contracts_contracts c WHERE c.environment_type = 'PRODUCTION')
WHERE rn = 1
),
trials AS (SELECT * FROM contracts WHERE contract_type = 'trial'),
conversions AS (
SELECT t.from_contract_id AS trial_contract_id, t.to_contract_id AS paid_contract_id, t.date AS converted_at
FROM (SELECT t.*, row_number() OVER (PARTITION BY t.id ORDER BY t.updated_at DESC) AS rn
FROM contracts_transitions t WHERE t.environment_type = 'PRODUCTION') t
JOIN trials tr ON tr.id = t.from_contract_id
WHERE t.rn = 1 AND upper(t.type) = 'RENEWAL'
),
-- Trial credits with their expiration entries (ledger is a JSON array string)
trial_credits AS (
SELECT b.id AS credit_id, b.contract_id, b.customer_id, b.balance,
json_extract_scalar(b.access_schedule, '$.schedule_items[0].date') AS segment_start,
json_extract_scalar(b.access_schedule, '$.schedule_items[0].end_date') AS segment_end,
b.ledger
FROM (SELECT b.*, row_number() OVER (PARTITION BY b.id ORDER BY b.updated_at DESC) AS rn
FROM contracts_balances b WHERE b.environment_type = 'PRODUCTION') b
WHERE b.rn = 1 AND b.type = 'credit'
AND json_extract_scalar(b.metadata, '$.custom_fields.trial') = 'true'
),
expirations AS (
SELECT tc.credit_id, tc.contract_id,
CAST(json_extract_scalar(e, '$.amount') AS double) AS expired_amount, -- negative in the export
from_iso8601_timestamp(json_extract_scalar(e, '$.timestamp')) AS expired_at
FROM trial_credits tc
CROSS JOIN UNNEST(CAST(json_parse(tc.ledger) AS ARRAY(JSON))) AS u(e)
WHERE json_extract_scalar(e, '$.type') = 'credit_segment_expiration'
),
-- Daily trial funnel: starts, conversions, expired-unused (attrition).
-- Aggregate each metric on its own date, then join. Conversions and expirations land days
-- after the start, so a day spine built only from starting_at would drop most of them.
starts_by_day AS (
SELECT date(starting_at) AS day, count(DISTINCT id) AS n FROM trials GROUP BY 1
),
conversions_by_day AS (
SELECT date(converted_at) AS day, count(DISTINCT trial_contract_id) AS n FROM conversions GROUP BY 1
),
expired_unused_by_day AS (
SELECT date(ex.expired_at) AS day, count(DISTINCT ex.contract_id) AS n
FROM expirations ex
LEFT JOIN conversions cv ON cv.trial_contract_id = ex.contract_id
WHERE cv.trial_contract_id IS NULL AND ex.expired_amount < 0
GROUP BY 1
),
days AS (
SELECT day FROM starts_by_day
UNION SELECT day FROM conversions_by_day
UNION SELECT day FROM expired_unused_by_day
)
SELECT d.day,
coalesce(s.n, 0) AS trial_starts,
coalesce(c.n, 0) AS conversions,
coalesce(e.n, 0) AS expired_unused
FROM days d
LEFT JOIN starts_by_day s ON s.day = d.day
LEFT JOIN conversions_by_day c ON c.day = d.day
LEFT JOIN expired_unused_by_day e ON e.day = d.day
ORDER BY d.day;
