weekly_participation.sql

                                                                                            
with filtered_temp as (                                                                              
  select *                                                                                           
  from hydrated_temp                                                                                 
  where company_id = 4 or company_id = 12                                                            
) ,                                                                                                  
full_weekly_participation as (                                                                       
  select                                                                                             
  date_trunc('week',                                                                                 
    'epoch'::timestamp                                                                               
    + '7 days'::interval * (extract(epoch from occurred_on)::int4 / ( 7 * 24 * 60 * 60))             
    + '1 days'::interval * (extract(dow from now()))                                                 
  ) - '1 days'::interval * (extract(dow from now())) as occurred_on,                                 
  count(distinct user_id) as active_count,                                                           
  (                                                                                                  
    select count(*) from users where company_id = 4 or company_id = 12                               
    and verified_at is not null and deleted_at is null                                               
  ) total_users                                                                                      
  from                                                                                               
  filtered_temp                                                                                      
  group by 1                                                                                         
  order by 1                                                                                         
)                                                                                                    
                                                                                                     
select                                                                                               
occurred_on,                                                                                         
active_count,                                                                                        
total_users as total_users,                                                                          
round((active_count * 100.0 / total_users)::numeric, 2) percent                                      
from full_weekly_participation;