Skip to content

Instantly share code, notes, and snippets.

@savadev

savadev/growth_accounting_and_ltv.sql

Forked from hsurreal/growth_accounting_and_ltv.sql
Created November 19, 2023 23:56
Show Gist options
  • Save savadev/6b43dc308667b10b9a2f20e4ad62405c to your computer and use it in GitHub Desktop.
Save savadev/6b43dc308667b10b9a2f20e4ad62405c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @hsurreal hsurreal revised this gist Oct 25, 2016. 1 changed file with 60 additions and 2 deletions.
    62 changes: 60 additions & 2 deletions growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -86,6 +86,7 @@ with
    and tm.month = lm.month + interval '1 month'
    )
    group by 1
    order by 1
    ),
    -- This generates the familiar monthly cohort retention dataset.
    mau_retention_by_cohort as (
    @@ -152,6 +153,7 @@ with
    and tm.month = lm.month + interval '1 month'
    )
    group by 1
    order by 1
    ),
    -- These next tables are to compute LTV via the cohorts_cumulative table.
    -- The LTV here is being computed for weekly cohorts on weekly intervals.
    @@ -196,7 +198,7 @@ with
    1.0 * c1.users/cs.users as retained_pctg,
    c1.inc_amt,
    sum(c2.inc_amt) as cum_amt,
    sum(c2.inc_amt)/cs.users as cum_amt_per_user
    1.0*sum(c2.inc_amt)/cs.users as cum_amt_per_user
    from
    cohorts c1,
    cohorts c2,
    @@ -207,6 +209,50 @@ with
    and cs.first_week = c1.first_week
    group by 1,2,3,4,5,6,7
    order by 1,2
    ),
    -- monthly cumulative cohorts
    cohorts_m as (
    select
    first_month,
    month as active_month,
    extract(month from month) - extract(month from first_month)
    + 12*(extract(year from month) - extract(year from first_month)) as months_since_first,
    count(distinct user_id) as users,
    sum(inc_amt) as inc_amt
    from mau_decorated
    group by 1,2,3
    order by 1,2
    ),
    cohort_sizes_m as (
    select
    first_month,
    users,
    inc_amt
    from cohorts_m
    where months_since_first = 0
    ),
    cohorts_cumulative_m as (
    -- A semi-cartesian join accomplishes the cumulative behavior.
    select
    c1.first_month,
    c1.active_month,
    c1.months_since_first,
    c1.users,
    cs.users as cohort_num_users,
    1.0 * c1.users/cs.users as retained_pctg,
    c1.inc_amt,
    sum(c2.inc_amt) as cum_amt,
    1.0*sum(c2.inc_amt)/cs.users as cum_amt_per_user
    from
    cohorts_m c1,
    cohorts_m c2,
    cohort_sizes_m cs
    where
    c1.first_month = c2.first_month
    and c2.months_since_first <= c1.months_since_first
    and cs.first_month = c1.first_month
    group by 1,2,3,4,5,6,7
    order by 1,2
    )

    -- For MAU retention by cohort, useful for the standard retention heatmap
    @@ -215,16 +261,28 @@ select * from mau_retention_by_cohort
    -- For cumulative LTV data use this
    select * from cohorts_cumulative

    -- For cumulative LTV with monthly cohorts use this
    select * from cohorts_cumulative_m

    -- For MAU growth accuonting use this
    select * from mau_growth_accounting

    -- For MRR growth accuonting use this
    select * from mrr_growth_accounting

    -- For use as input in the 8-ball tool use this
    -- For use as weekly input in the 8-ball tool use this
    select
    first_week as cohort_week,
    active_week as activity_week,
    users,
    inc_amt as revenue
    from cohorts_cumulative


    -- For use as monthly input in the 8-ball tool use this
    select
    first_month as cohort_month,
    active_month as activity_month,
    users,
    inc_amt as revenue
    from cohorts_cumulative_m
  2. @hsurreal hsurreal revised this gist Jan 5, 2016. 1 changed file with 0 additions and 3 deletions.
    3 changes: 0 additions & 3 deletions growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,3 @@



    with
    dau as (
    -- This part of the query can be pretty much anything.
  3. @hsurreal hsurreal revised this gist Jan 5, 2016. 1 changed file with 48 additions and 30 deletions.
    78 changes: 48 additions & 30 deletions growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,6 @@



    with
    dau as (
    -- This part of the query can be pretty much anything.
    @@ -25,7 +28,7 @@ with
    -- First, set up WAU and MAU tables for future use
    wau as (
    select
    date_trunc('week',dt) as week,
    date_trunc('week', dt) as week,
    user_id,
    sum(inc_amt) as inc_amt
    from dau
    @@ -46,8 +49,8 @@ with
    select
    user_id,
    min(dt) as first_dt,
    date_trunc('week',min(dt)) as first_week,
    date_trunc('month',min(dt)) as first_month
    date_trunc('week', min(dt)) as first_week,
    date_trunc('month', min(dt)) as first_month
    from dau
    group by 1
    ),
    @@ -58,8 +61,8 @@ with
    d.inc_amt,
    f.first_month
    from mau d, first_dt f
    where d.user_id=f.user_id
    and inc_amt>0
    where d.user_id = f.user_id
    and inc_amt > 0
    ),
    -- This is MAU growth accounting. Note that this does not require any
    -- information about inc_amt. As discussed in the articles, these
    @@ -72,21 +75,33 @@ with
    count(distinct tm.user_id) as mau,
    count(distinct case when lm.user_id is not NULL then tm.user_id
    else NULL end) as retained,
    count(distinct case when tm.first_month=tm.month then tm.user_id
    count(distinct case when tm.first_month = tm.month then tm.user_id
    else NULL end) as new,
    count(distinct case when tm.first_month!=tm.month
    count(distinct case when tm.first_month != tm.month
    and lm.user_id is NULL then tm.user_id else NULL end
    ) as resurrected,
    -1*count(distinct case when tm.user_id is NULL then lm.user_id
    else NULL end) as churned
    from
    mau_decorated tm
    full outer join mau_decorated lm on (
    tm.user_id=lm.user_id
    tm.user_id = lm.user_id
    and tm.month = lm.month + interval '1 month'
    )
    group by 1
    ),
    -- This generates the familiar monthly cohort retention dataset.
    mau_retention_by_cohort as (
    select
    first_month,
    12 * extract(year from age(month, first_month)) +
    extract(month from age(month, first_month)) as months_since_first,
    count(1) as active_users,
    sum(inc_amt) as inc_amt
    from mau_decorated
    group by 1,2
    order by 1,2
    ),
    -- This is the MRR growth accounting (or growth accounting of whatever
    -- value you put in inc_amt). These also satisfy some identities:
    -- MRR(t) = retained(t) + new(t) + resurrected(t) + expansion(t)
    @@ -98,45 +113,45 @@ with
    sum(
    case
    when tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt>=lm.inc_amt then lm.inc_amt
    and tm.inc_amt >= lm.inc_amt then lm.inc_amt
    when tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt<lm.inc_amt then tm.inc_amt
    and tm.inc_amt < lm.inc_amt then tm.inc_amt
    else 0
    end
    ) as retained,
    sum(
    case when tm.first_month=tm.month then tm.inc_amt
    case when tm.first_month = tm.month then tm.inc_amt
    else 0 end
    ) as new,
    sum(
    case when tm.month!=tm.first_month and tm.user_id is not NULL
    and lm.user_id is not NULL and tm.inc_amt>lm.inc_amt
    and lm.inc_amt>0 then tm.inc_amt - lm.inc_amt
    case when tm.month != tm.first_month and tm.user_id is not NULL
    and lm.user_id is not NULL and tm.inc_amt > lm.inc_amt
    and lm.inc_amt > 0 then tm.inc_amt - lm.inc_amt
    else 0 end
    ) as expansion,
    sum(
    case when tm.user_id is not NULL
    and (lm.user_id is NULL or lm.inc_amt=0)
    and tm.inc_amt>0 and tm.first_month!=tm.month
    and (lm.user_id is NULL or lm.inc_amt = 0)
    and tm.inc_amt > 0 and tm.first_month != tm.month
    then tm.inc_amt
    else 0 end
    ) as resurrected,
    -1*sum(
    -1 * sum(
    case
    when tm.month!=tm.first_month and tm.user_id is not NULL
    when tm.month != tm.first_month and tm.user_id is not NULL
    and lm.user_id is not NULL
    and tm.inc_amt<lm.inc_amt and tm.inc_amt>0
    and tm.inc_amt < lm.inc_amt and tm.inc_amt > 0
    then lm.inc_amt - tm.inc_amt
    else 0 end
    ) as contraction,
    -1*sum(
    case when lm.inc_amt>0 and (tm.user_id is NULL or tm.inc_amt=0)
    -1 * sum(
    case when lm.inc_amt > 0 and (tm.user_id is NULL or tm.inc_amt = 0)
    then lm.inc_amt else 0 end
    ) as churned
    from
    mau_decorated tm
    full outer join mau_decorated lm on (
    tm.user_id=lm.user_id
    tm.user_id = lm.user_id
    and tm.month = lm.month + interval '1 month'
    )
    group by 1
    @@ -152,13 +167,13 @@ with
    w.inc_amt,
    f.first_week
    from wau w, first_dt f
    where w.user_id=f.user_id
    where w.user_id = f.user_id
    ),
    cohorts as (
    select
    first_week,
    week as active_week,
    floor(extract(DAYS from (week - first_week))/7.0) as weeks_since_first,
    ceil(extract(DAYS from (week - first_week))/7.0) as weeks_since_first,
    count(distinct user_id) as users,
    sum(inc_amt) as inc_amt
    from wau_decorated
    @@ -171,7 +186,7 @@ with
    users,
    inc_amt
    from cohorts
    where weeks_since_first=0
    where weeks_since_first = 0
    ),
    cohorts_cumulative as (
    -- A semi-cartesian join accomplishes the cumulative behavior.
    @@ -181,7 +196,7 @@ with
    c1.weeks_since_first,
    c1.users,
    cs.users as cohort_num_users,
    1.0*c1.users/cs.users as retained_pctg,
    1.0 * c1.users/cs.users as retained_pctg,
    c1.inc_amt,
    sum(c2.inc_amt) as cum_amt,
    sum(c2.inc_amt)/cs.users as cum_amt_per_user
    @@ -190,13 +205,16 @@ with
    cohorts c2,
    cohort_sizes cs
    where
    c1.first_week=c2.first_week
    and c2.weeks_since_first<=c1.weeks_since_first
    and cs.first_week=c1.first_week
    c1.first_week = c2.first_week
    and c2.weeks_since_first <= c1.weeks_since_first
    and cs.first_week = c1.first_week
    group by 1,2,3,4,5,6,7
    order by 1,2
    )

    -- For MAU retention by cohort, useful for the standard retention heatmap
    select * from mau_retention_by_cohort

    -- For cumulative LTV data use this
    select * from cohorts_cumulative

    @@ -206,7 +224,7 @@ select * from mau_growth_accounting
    -- For MRR growth accuonting use this
    select * from mrr_growth_accounting

    -- For use in the 8-ball tool, use this
    -- For use as input in the 8-ball tool use this
    select
    first_week as cohort_week,
    active_week as activity_week,
  4. @hsurreal hsurreal revised this gist Nov 10, 2015. 1 changed file with 7 additions and 10 deletions.
    17 changes: 7 additions & 10 deletions growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,3 @@



    with
    dau as (
    -- This part of the query can be pretty much anything.
    @@ -16,13 +13,13 @@ with
    -- data set provided for free by Mode.
    -- If you edit just this part to represent your data, the rest
    -- of the query should run just fine.
    -- The query here is a sample that works in the public Mode Analytics
    -- tutorial.
    select
    date(date(order_date) + random()*90*(interval '1 day')) as dt,
    user_guid as user_id,
    sum(order_gross_rev) as inc_amt
    from washio_data
    where order_date>='2014-02-03'
    and order_gross_rev>0
    user_id,
    date(occurred_at) as dt,
    sum(user_type) as inc_amt
    from tutorial.yammer_events
    group by 1,2
    ),
    -- First, set up WAU and MAU tables for future use
    @@ -209,7 +206,7 @@ select * from mau_growth_accounting
    -- For MRR growth accuonting use this
    select * from mrr_growth_accounting

    -- For uploading to the 8-ball tool use this
    -- For use in the 8-ball tool, use this
    select
    first_week as cohort_week,
    active_week as activity_week,
  5. @hsurreal hsurreal revised this gist Nov 9, 2015. 1 changed file with 26 additions and 8 deletions.
    34 changes: 26 additions & 8 deletions growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,6 @@



    with
    dau as (
    -- This part of the query can be pretty much anything.
    @@ -14,10 +17,12 @@ with
    -- If you edit just this part to represent your data, the rest
    -- of the query should run just fine.
    select
    date(occurred_at) as dt,
    user_id,
    sum(user_type) as inc_amt
    from tutorial.yammer_events
    date(date(order_date) + random()*90*(interval '1 day')) as dt,
    user_guid as user_id,
    sum(order_gross_rev) as inc_amt
    from washio_data
    where order_date>='2014-02-03'
    and order_gross_rev>0
    group by 1,2
    ),
    -- First, set up WAU and MAU tables for future use
    @@ -155,11 +160,12 @@ with
    cohorts as (
    select
    first_week,
    week as active_week,
    floor(extract(DAYS from (week - first_week))/7.0) as weeks_since_first,
    count(distinct user_id) as users,
    sum(inc_amt) as inc_amt
    from wau_decorated
    group by 1,2
    group by 1,2,3
    order by 1,2
    ),
    cohort_sizes as (
    @@ -173,7 +179,8 @@ with
    cohorts_cumulative as (
    -- A semi-cartesian join accomplishes the cumulative behavior.
    select
    c1.first_week,
    c1.first_week,
    c1.active_week,
    c1.weeks_since_first,
    c1.users,
    cs.users as cohort_num_users,
    @@ -189,12 +196,23 @@ with
    c1.first_week=c2.first_week
    and c2.weeks_since_first<=c1.weeks_since_first
    and cs.first_week=c1.first_week
    group by 1,2,3,4,5,6
    group by 1,2,3,4,5,6,7
    order by 1,2
    )

    -- For cumulative LTV data use this
    select * from cohorts_cumulative

    -- For MAU growth accuonting use this
    select * from mau_growth_accounting

    -- For MRR growth accuonting use this
    select * from mrr_growth_accounting
    select * from mrr_growth_accounting

    -- For uploading to the 8-ball tool use this
    select
    first_week as cohort_week,
    active_week as activity_week,
    users,
    inc_amt as revenue
    from cohorts_cumulative
  6. @hsurreal hsurreal revised this gist Nov 4, 2015. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -192,5 +192,9 @@ with
    group by 1,2,3,4,5,6
    order by 1,2
    )
    -- For cumulative LTV data use this
    select * from cohorts_cumulative

    -- For MAU growth accuonting use this
    select * from mau_growth_accounting
    -- For MRR growth accuonting use this
    select * from mrr_growth_accounting
  7. @hsurreal hsurreal revised this gist Nov 3, 2015. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,3 @@

    with
    dau as (
    -- This part of the query can be pretty much anything.
    @@ -10,6 +9,10 @@ with
    -- The most common case is
    -- inc_amt = incremental revenue from the user on dt
    -- If you want to do L28 growth accounting, user inc_amt=1.
    -- The version here derives everything from the tutorial.yammer_events
    -- data set provided for free by Mode.
    -- If you edit just this part to represent your data, the rest
    -- of the query should run just fine.
    select
    date(occurred_at) as dt,
    user_id,
    @@ -189,4 +192,5 @@ with
    group by 1,2,3,4,5,6
    order by 1,2
    )
    select * from cohorts_cumulative
    select * from cohorts_cumulative

  8. @hsurreal hsurreal revised this gist Nov 3, 2015. 1 changed file with 40 additions and 23 deletions.
    63 changes: 40 additions & 23 deletions growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,14 @@

    with
    dau as (
    -- This part of the query can be pretty much anything.
    -- The only requirement is that it have three columns: dt, user_id, inc_amt
    -- The only requirement is that it have three columns:
    -- dt, user_id, inc_amt
    -- Where dt is a date and user_id is some unique identifier for a user.
    -- Each dt-user_id pair should be unique in this table.
    -- inc_amt represents the amount of value that this user created for this dt.
    -- The most common case is inc_amt = incremental revenue from the user on dt.
    -- inc_amt represents the amount of value that this user created on dt.
    -- The most common case is
    -- inc_amt = incremental revenue from the user on dt
    -- If you want to do L28 growth accounting, user inc_amt=1.
    select
    date(occurred_at) as dt,
    @@ -53,18 +56,24 @@ with
    where d.user_id=f.user_id
    and inc_amt>0
    ),
    -- This is MAU growth accounting. Note that this does not require any information about inc_amt.
    -- As discussed in the articles, these quantities satisfy some identities:
    -- This is MAU growth accounting. Note that this does not require any
    -- information about inc_amt. As discussed in the articles, these
    -- quantities satisfy some identities:
    -- MAU(t) = retained(t) + new(t) + resurrected(t)
    -- MAU(t - 1 month) = retained(t) + churned(t)
    mau_growth_accounting as (
    select
    coalesce(tm.month, lm.month + interval '1 month') as month,
    count(distinct tm.user_id) as mau,
    count(distinct case when lm.user_id is not NULL then tm.user_id else NULL end) as retained,
    count(distinct case when tm.first_month=tm.month then tm.user_id else NULL end) as new,
    count(distinct case when tm.first_month!=tm.month and lm.user_id is NULL then tm.user_id else NULL end) as resurrected,
    -1*count(distinct case when tm.user_id is NULL then lm.user_id else NULL end) as churned
    count(distinct case when lm.user_id is not NULL then tm.user_id
    else NULL end) as retained,
    count(distinct case when tm.first_month=tm.month then tm.user_id
    else NULL end) as new,
    count(distinct case when tm.first_month!=tm.month
    and lm.user_id is NULL then tm.user_id else NULL end
    ) as resurrected,
    -1*count(distinct case when tm.user_id is NULL then lm.user_id
    else NULL end) as churned
    from
    mau_decorated tm
    full outer join mau_decorated lm on (
    @@ -73,8 +82,8 @@ with
    )
    group by 1
    ),
    -- This is the MRR growth accounting (or growth accounting of whatever value you put in inc_amt)
    -- These also satisfy some identities:
    -- This is the MRR growth accounting (or growth accounting of whatever
    -- value you put in inc_amt). These also satisfy some identities:
    -- MRR(t) = retained(t) + new(t) + resurrected(t) + expansion(t)
    -- MAU(t - 1 month) = retained(t) + churned(t) + contraction(t)
    mrr_growth_accounting as (
    @@ -83,8 +92,10 @@ with
    sum(tm.inc_amt) as rev,
    sum(
    case
    when tm.user_id is not NULL and lm.user_id is not NULL and tm.inc_amt>=lm.inc_amt then lm.inc_amt
    when tm.user_id is not NULL and lm.user_id is not NULL and tm.inc_amt<lm.inc_amt then tm.inc_amt
    when tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt>=lm.inc_amt then lm.inc_amt
    when tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt<lm.inc_amt then tm.inc_amt
    else 0
    end
    ) as retained,
    @@ -93,23 +104,29 @@ with
    else 0 end
    ) as new,
    sum(
    case when tm.month!=tm.first_month and tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt>lm.inc_amt and lm.inc_amt>0 then tm.inc_amt - lm.inc_amt
    case when tm.month!=tm.first_month and tm.user_id is not NULL
    and lm.user_id is not NULL and tm.inc_amt>lm.inc_amt
    and lm.inc_amt>0 then tm.inc_amt - lm.inc_amt
    else 0 end
    ) as expansion,
    sum(
    case when tm.user_id is not NULL and (lm.user_id is NULL or lm.inc_amt=0)
    and tm.inc_amt>0 and tm.first_month!=tm.month then tm.inc_amt
    case when tm.user_id is not NULL
    and (lm.user_id is NULL or lm.inc_amt=0)
    and tm.inc_amt>0 and tm.first_month!=tm.month
    then tm.inc_amt
    else 0 end
    ) as resurrected,
    -1*sum(
    case
    when tm.month!=tm.first_month and tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt<lm.inc_amt and tm.inc_amt>0 then lm.inc_amt - tm.inc_amt
    when tm.month!=tm.first_month and tm.user_id is not NULL
    and lm.user_id is not NULL
    and tm.inc_amt<lm.inc_amt and tm.inc_amt>0
    then lm.inc_amt - tm.inc_amt
    else 0 end
    ) as contraction,
    -1*sum(
    case when lm.inc_amt>0 and (tm.user_id is NULL or tm.inc_amt=0) then lm.inc_amt else 0 end
    case when lm.inc_amt>0 and (tm.user_id is NULL or tm.inc_amt=0)
    then lm.inc_amt else 0 end
    ) as churned
    from
    mau_decorated tm
    @@ -121,7 +138,8 @@ with
    ),
    -- These next tables are to compute LTV via the cohorts_cumulative table.
    -- The LTV here is being computed for weekly cohorts on weekly intervals.
    -- The queries can be modified to compute it for cohorts of any size on any frequency.
    -- The queries can be modified to compute it for cohorts of any size
    -- on any time window frequency.
    wau_decorated as (
    select
    week,
    @@ -171,5 +189,4 @@ with
    group by 1,2,3,4,5,6
    order by 1,2
    )
    select * from cohorts_cumulative

    select * from cohorts_cumulative
  9. @hsurreal hsurreal renamed this gist Nov 3, 2015. 1 changed file with 0 additions and 13 deletions.
    13 changes: 0 additions & 13 deletions gistfile1.txt → growth_accounting_and_ltv.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -173,16 +173,3 @@ with
    )
    select * from cohorts_cumulative














  10. @hsurreal hsurreal created this gist Nov 3, 2015.
    188 changes: 188 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,188 @@
    with
    dau as (
    -- This part of the query can be pretty much anything.
    -- The only requirement is that it have three columns: dt, user_id, inc_amt
    -- Where dt is a date and user_id is some unique identifier for a user.
    -- Each dt-user_id pair should be unique in this table.
    -- inc_amt represents the amount of value that this user created for this dt.
    -- The most common case is inc_amt = incremental revenue from the user on dt.
    -- If you want to do L28 growth accounting, user inc_amt=1.
    select
    date(occurred_at) as dt,
    user_id,
    sum(user_type) as inc_amt
    from tutorial.yammer_events
    group by 1,2
    ),
    -- First, set up WAU and MAU tables for future use
    wau as (
    select
    date_trunc('week',dt) as week,
    user_id,
    sum(inc_amt) as inc_amt
    from dau
    group by 1,2
    ),
    mau as (
    select
    date_trunc('month',dt) as month,
    user_id,
    sum(inc_amt) as inc_amt
    from dau
    group by 1,2
    ),
    -- This determines the cohort date of each user. In this case we are
    -- deriving it from DAU data but you can feel free to replace it with
    -- registration date if that's more appropriate.
    first_dt as (
    select
    user_id,
    min(dt) as first_dt,
    date_trunc('week',min(dt)) as first_week,
    date_trunc('month',min(dt)) as first_month
    from dau
    group by 1
    ),
    mau_decorated as (
    select
    d.month,
    d.user_id,
    d.inc_amt,
    f.first_month
    from mau d, first_dt f
    where d.user_id=f.user_id
    and inc_amt>0
    ),
    -- This is MAU growth accounting. Note that this does not require any information about inc_amt.
    -- As discussed in the articles, these quantities satisfy some identities:
    -- MAU(t) = retained(t) + new(t) + resurrected(t)
    -- MAU(t - 1 month) = retained(t) + churned(t)
    mau_growth_accounting as (
    select
    coalesce(tm.month, lm.month + interval '1 month') as month,
    count(distinct tm.user_id) as mau,
    count(distinct case when lm.user_id is not NULL then tm.user_id else NULL end) as retained,
    count(distinct case when tm.first_month=tm.month then tm.user_id else NULL end) as new,
    count(distinct case when tm.first_month!=tm.month and lm.user_id is NULL then tm.user_id else NULL end) as resurrected,
    -1*count(distinct case when tm.user_id is NULL then lm.user_id else NULL end) as churned
    from
    mau_decorated tm
    full outer join mau_decorated lm on (
    tm.user_id=lm.user_id
    and tm.month = lm.month + interval '1 month'
    )
    group by 1
    ),
    -- This is the MRR growth accounting (or growth accounting of whatever value you put in inc_amt)
    -- These also satisfy some identities:
    -- MRR(t) = retained(t) + new(t) + resurrected(t) + expansion(t)
    -- MAU(t - 1 month) = retained(t) + churned(t) + contraction(t)
    mrr_growth_accounting as (
    select
    coalesce(tm.month, lm.month + interval '1 month') as month,
    sum(tm.inc_amt) as rev,
    sum(
    case
    when tm.user_id is not NULL and lm.user_id is not NULL and tm.inc_amt>=lm.inc_amt then lm.inc_amt
    when tm.user_id is not NULL and lm.user_id is not NULL and tm.inc_amt<lm.inc_amt then tm.inc_amt
    else 0
    end
    ) as retained,
    sum(
    case when tm.first_month=tm.month then tm.inc_amt
    else 0 end
    ) as new,
    sum(
    case when tm.month!=tm.first_month and tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt>lm.inc_amt and lm.inc_amt>0 then tm.inc_amt - lm.inc_amt
    else 0 end
    ) as expansion,
    sum(
    case when tm.user_id is not NULL and (lm.user_id is NULL or lm.inc_amt=0)
    and tm.inc_amt>0 and tm.first_month!=tm.month then tm.inc_amt
    else 0 end
    ) as resurrected,
    -1*sum(
    case
    when tm.month!=tm.first_month and tm.user_id is not NULL and lm.user_id is not NULL
    and tm.inc_amt<lm.inc_amt and tm.inc_amt>0 then lm.inc_amt - tm.inc_amt
    else 0 end
    ) as contraction,
    -1*sum(
    case when lm.inc_amt>0 and (tm.user_id is NULL or tm.inc_amt=0) then lm.inc_amt else 0 end
    ) as churned
    from
    mau_decorated tm
    full outer join mau_decorated lm on (
    tm.user_id=lm.user_id
    and tm.month = lm.month + interval '1 month'
    )
    group by 1
    ),
    -- These next tables are to compute LTV via the cohorts_cumulative table.
    -- The LTV here is being computed for weekly cohorts on weekly intervals.
    -- The queries can be modified to compute it for cohorts of any size on any frequency.
    wau_decorated as (
    select
    week,
    w.user_id,
    w.inc_amt,
    f.first_week
    from wau w, first_dt f
    where w.user_id=f.user_id
    ),
    cohorts as (
    select
    first_week,
    floor(extract(DAYS from (week - first_week))/7.0) as weeks_since_first,
    count(distinct user_id) as users,
    sum(inc_amt) as inc_amt
    from wau_decorated
    group by 1,2
    order by 1,2
    ),
    cohort_sizes as (
    select
    first_week,
    users,
    inc_amt
    from cohorts
    where weeks_since_first=0
    ),
    cohorts_cumulative as (
    -- A semi-cartesian join accomplishes the cumulative behavior.
    select
    c1.first_week,
    c1.weeks_since_first,
    c1.users,
    cs.users as cohort_num_users,
    1.0*c1.users/cs.users as retained_pctg,
    c1.inc_amt,
    sum(c2.inc_amt) as cum_amt,
    sum(c2.inc_amt)/cs.users as cum_amt_per_user
    from
    cohorts c1,
    cohorts c2,
    cohort_sizes cs
    where
    c1.first_week=c2.first_week
    and c2.weeks_since_first<=c1.weeks_since_first
    and cs.first_week=c1.first_week
    group by 1,2,3,4,5,6
    order by 1,2
    )
    select * from cohorts_cumulative