/* Net Revenue Retention */
CREATE VIEW net_revenue_retention AS
WITH date AS (
SELECT GENERATE_SERIES(('01-01-' || (SELECT EXTRACT(YEAR FROM CURRENT_DATE)))::DATE, NOW()::DATE, '1 day')::DATE AS date
), above AS (
SELECT
ps.*
FROM
date d
LEFT JOIN LATERAL (
SELECT
d.date,
ps.user_id,
ps.app_type,
ps.created_at,
ps.downgraded_at,
LAST_VALUE(COALESCE(CASE WHEN ps.promo_end::DATE > d.date - INTERVAL '30 days' THEN ps.promo_price ELSE ps.price END, ps.price)) OVER w AS price_start, /* Just based on how ordering works in this case */
FIRST_VALUE(COALESCE(CASE WHEN ps.promo_end::DATE > d.date THEN ps.promo_price ELSE ps.price END, ps.price)) OVER w AS price_end,
RANK() OVER x AS rank,
BOOL_OR(ps.created_at < d.date - INTERVAL '30 days') OVER y AS qualifier -- True if at least 1
FROM
pro_subscriptions ps
WHERE
ps.created_at < d.date
AND ((ps.downgraded_at IS NULL AND ps.active) OR ps.downgraded_at > d.date - INTERVAL '30 days')
WINDOW w AS (
PARTITION BY ps.user_id, ps.app_type
ORDER BY ps.created_at DESC
), x AS (
PARTITION BY ps.user_id, ps.app_type
ORDER BY ps.created_at ASC
), y AS (
PARTITION BY ps.user_id
ORDER BY ps.created_at ASC
)
) ps ON true
UNION ALL
SELECT
d.date,
cc.user_id,
a.app_type,
asub.created_at,
asub.downgraded_at,
COALESCE(CASE WHEN asub.promo_end::DATE > d.date - INTERVAL '30 days'THEN asub.promo_price ELSE asub.price END, asub.price) AS price_start,
COALESCE(CASE WHEN asub.promo_end::DATE > d.date THEN asub.promo_price ELSE asub.price END, asub.price) AS price_end,
1 AS rank,
true AS qualifier
FROM
date d
LEFT JOIN app_subscriptions asub ON asub.created_at < d.date
AND ((asub.downgraded_at IS NULL AND asub.active) OR asub.downgraded_at > d.date - INTERVAL '30 days')
LEFT JOIN credit_cards cc ON cc.id = asub.credit_card_id
LEFT JOIN apps a ON a.id = asub.app_id
)
SELECT
above.date,
SUM(CASE WHEN created_at < date - INTERVAL '30 days' THEN price_start ELSE 0 END) AS rev_start, -- This filters out any subcsriptions that were only created after the set date
SUM(CASE WHEN qualifier THEN CASE WHEN price_start IS NOT DISTINCT FROM price_end AND (downgraded_at IS NOT NULL AND downgraded_at <= date) THEN 0 ELSE price_end END ELSE 0 END) AS rev_end,
CASE WHEN SUM(CASE WHEN created_at < date - INTERVAL '30 days' THEN price_start ELSE 0 END) = 0 THEN 0
ELSE SUM(CASE WHEN qualifier THEN CASE WHEN price_start IS NOT DISTINCT FROM price_end AND (downgraded_at IS NOT NULL AND downgraded_at <= date) THEN 0 ELSE price_end END ELSE 0 END)/SUM(CASE WHEN created_at < date - INTERVAL '30 days' THEN price_start ELSE 0 END)::FLOAT * 100
END AS nrr
FROM
above
WHERE rank = 1
GROUP BY above.date
ORDER BY above.date