Skip to content

Instantly share code, notes, and snippets.

@jdwyah
Created May 11, 2016 04:50
Show Gist options
  • Save jdwyah/bba4c268d34cf07ce339c167c33200ff to your computer and use it in GitHub Desktop.
Save jdwyah/bba4c268d34cf07ce339c167c33200ff to your computer and use it in GitHub Desktop.
WITH monthly_usage AS (
SELECT
user_id,
date_part('month', age(created_at, '1970-01-01')) +
12 * date_part('year', age(created_at, '1970-01-01')) AS time_period
FROM orders
WHERE order_state = 'completed'
GROUP BY 1, 2
ORDER BY 1, 2)
,
lag_lead AS (
SELECT
user_id,
time_period,
lag(time_period, 1)
OVER (PARTITION BY user_id
ORDER BY user_id, time_period),
lead(time_period, 1)
OVER (PARTITION BY user_id
ORDER BY user_id, time_period)
FROM monthly_usage),
lag_lead_with_diffs AS (
SELECT
user_id,
time_period,
lag,
lead,
time_period - lag lag_size,
lead - time_period lead_size
FROM lag_lead),
calculated AS (SELECT
time_period,
CASE WHEN lag IS NULL
THEN 'NEW'
WHEN lag_size = 1
THEN 'ACTIVE'
WHEN lag_size > 1
THEN 'RETURN'
END AS this_month_value,
CASE WHEN (lead_size > 1 OR lead_size IS NULL)
THEN 'CHURN'
ELSE NULL
END AS next_month_churn,
count(DISTINCT user_id)
FROM lag_lead_with_diffs
GROUP BY 1, 2, 3)
SELECT
time_period,
this_month_value,
sum(count)
FROM calculated
GROUP BY 1, 2
UNION
SELECT
time_period + 1,
'CHURN',
count
FROM calculated
WHERE next_month_churn IS NOT NULL
ORDER BY 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment