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

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

...
...
...