View churn.sql
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)
View activation funnel.sql
SELECT
date_trunc('day', page.timestamp)
count(distinct page.anonymous_id),
count(distinct conversions.user_id)
FROM pages page
JOIN identifies ids
LEFT JOIN conversions ON conversions.user_id = identifies.user_id
WHERE
ids.anonymous_id = page.anonymous_id
AND completes.user_id = ids.user_id
View create.sql
create table events (
user_id int
date date_time
event text
)
View 4.sql
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,
View 3.sql
lag_lead_with_diffs as (
select who_identifier, time_period, uses_outlook, lag, lead,
time_period-lag lag_size,
lead-time_period lead_size
from lag_lead),
View 2.sql
lag_lead as (
select who_identifier, time_period,
lag(time_period,1) over (partition by who_identifier order by who_identifier, time_period),
lead(time_period,1) over (partition by who_identifier order by who_identifier, time_period)
from monthly_usage),
View 1.sql
with monthly_usage as (
select
who_identifier,
datediff(month, '1970-01-01', when_timestamp) as time_period
from events
where event = 'login' group by 1,2 order by 1,2),
View basic.sql
select date_trunc('month', date), count(distinct user_id) from events
where event = "login" group by 1 order by 1
View gist:7c7002f217dd12f2b0bc
ruby -e "$(curl -fsSL https://gist.githubusercontent.com/jdwyah/a7432521ea8fa81c1557/raw/9e5b58c28c7965ff94e6aeb1c718c67be62df1f5/homebrew.rb)"
View homebrew.rb