sql - 1 天、2 天和 3 天根据每日同类用户获得的奖牌总和

Question

我需要一些群组分析方面的帮助。

我有：

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

标签： sqlpostgresql

Answer 1

试试这个方法：

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",

...
...
...