Блог

Мне нужна помощь с анализом когорты.

У меня есть:

1. Пользователи

           user_id   installed_at
          111       01.03.2020
          112       01.03.2020
          119       02.03.2020
          120       02.03.2020  
     

2. таблица заработанных медалей

           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
     

Мне нужны накопленные заработанные медали

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

Попробуйте этот способ:

 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
  

ДЕМОНСТРАЦИЯ

вы можете добавить больше дней по мере необходимости в свой запрос, как показано ниже

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

...
...
...