--drop table temp1
CREATE TEMP TABLE temp1 AS
select to_char( dateFirst,'YYYY-MM') as startMonth, (cast(date_part('day',age(DateNoTime,dateFirst)) as int))/7 as Intervall,
count(distinct id_for_vendor) as amountofUsers
From
(select Primery.*,PlayerFirst.DateFirst
FROM
(SELECT distinct T_Pr.id_for_vendor,date(T_Pr.device_timestamp) as DateNoTime

FROM

/*add Row number column by user and date for finding the first usage date*/
(SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.id_for_vendor ,date(T.device_timestamp) ORDER BY T.device_timestamp) AS ROWNUMBER
FROM public.Facetune2_usage_app_background  AS T) AS T_Pr
WHERE T_Pr.ROWNUMBER=1) as Primery inner join
/* */
(SELECT T_Pr.*,date(T_Pr.device_timestamp) as DateFirst

FROM

/*add Row number column by user and date for finding the first usage date*/
(SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.id_for_vendor  ORDER BY T.device_timestamp) AS ROWNUMBER
FROM public.Facetune2_usage_app_background  AS T) AS T_Pr
WHERE T_Pr.ROWNUMBER=1
) as PlayerFirst on (Primery.id_for_vendor=PlayerFirst.id_for_vendor)) as Y
group by  to_char( dateFirst,'YYYY-MM') , (cast(date_part('day',age(DateNoTime,dateFirst)) as int))/7;

SELECT
    D.startmonth,
    max(case when intervall = '0'  then retentionRation end) AS inter0,
   max(case when intervall = '1'  then retentionRation end) AS inter1,
   max(case when intervall = '2'  then retentionRation end) AS inter2,
   max(case when intervall = '3'  then retentionRation end) AS inter3,
   max(case when intervall = '4'  then retentionRation end) AS inter4,
   max(case when intervall = '5'  then retentionRation end) AS inter5,
   max(case when intervall = '6'  then retentionRation end) AS inter6,
   max(case when intervall = '7'  then retentionRation end) AS inter7,
   max(case when intervall = '8'  then retentionRation end) AS inter8,
   max(case when intervall = '9'  then retentionRation end) AS inter9,
   max(case when intervall = '10'  then retentionRation end) AS inter10,
   max(case when intervall = '11'  then retentionRation end) AS inter11,
    max(case when intervall = '12'  then retentionRation end) AS inter12,
   max(case when intervall = '13'  then retentionRation end) AS inter13,
    max(case when intervall = '14'  then retentionRation end) AS inter14,
   max(case when intervall = '15'  then retentionRation end) AS inter15,
    max(case when intervall = '16'  then retentionRation end) AS inter16,
   max(case when intervall = '17'  then retentionRation end) AS inter17,
    max(case when intervall = '18'  then retentionRation end) AS inter18,
   max(case when intervall = '19'  then retentionRation end) AS inter19,
    max(case when intervall = '20'  then retentionRation end) AS inter20,
       max(case when intervall = '21'  then retentionRation end) AS inter21,
    max(case when intervall = '22'  then retentionRation end) AS inter22,
   max(case when intervall = '23'  then retentionRation end) AS inter23,
    max(case when intervall = '24'  then retentionRation end) AS inter24,
   max(case when intervall = '25'  then retentionRation end) AS inter25,
    max(case when intervall = '26'  then retentionRation end) AS inter26,
   max(case when intervall = '27'  then retentionRation end) AS inter27,
    max(case when intervall = '28'  then retentionRation end) AS inter28,
   max(case when intervall = '29'  then retentionRation end) AS inter29

    from
(select t1.startmonth,t1.intervall,(cast(t1.amountofusers as float)/cast(t2.amountofusers as float))*100 as retentionRation
from temp1 as t1 inner join (select startmonth,amountofusers from temp1 where intervall=0 )
as t2 on (t1.startmonth=t2.startmonth)) as D
group by startmonth
order by startmonth