Skip to content
Advertisement

sum of amount earned medals for 1-day, 2-day and 3-day based on daily cohort users

I need some help with cohort analys.

I have:

  1. users

           user_id   installed_at
           111       01.03.2020
           112       01.03.2020
           119       02.03.2020
           120       02.03.2020  
    
  2. table of earned medals

           user_id   created_at  earned_medals
               111   01.03.2020  1
               112   01.03.2020  1
               111   02.03.2020  2
               112   02.03.2020  2
               119   02.03.2020  1
               120   02.03.2020  1
               111   03.03.2020  3
               112   03.03.2020  3
               119   03.03.2020  2
               120   03.03.2020  2
    

I need acumulated earned medals

       Daily_cohort-user       1-day    2-day   3-day
       01.03.2020                 2       6      12
       02.03.2020                 2       6     null

Advertisement

Answer

Try this way:

with cte as (
select
t2.installed_at, t1.user_id,created_at,
sum(t1.earned_medals) over (partition by t1.user_id order by t1.user_id,created_at)  as "sum_"

from earned_medals t1 inner join users t2 on t1.user_id=t2.user_id
)

select 
installed_at,
sum(sum_) filter (where created_at-installed_at =0 )   as "Day1",
sum(sum_) filter (where created_at-installed_at =1 )   as "Day2",
sum(sum_) filter (where created_at-installed_at =2 )   as "Day3"
from cte
group by 1
order by 1

DEMO

you can add more days as required in your query like below

sum(sum_) filter (where created_at-installed_at =3 )   as "Day4",
sum(sum_) filter (where created_at-installed_at =4 )   as "Day5",

...
...
...
6 People found this is helpful
Advertisement