admin_dashboard_participation.csv

create temp table hydrated_temp (                                                                    
  occurred_on date,                                                                                  
  user_id int,                                                                                       
  company_id int);                                                                                   
                                                                                                     
with                                                                                                 
participation as (                                                                                   
  select occurred_on, user_id from simple_activities                                                 
  union                                                                                              
  select occurred_on, user_id from simple_sleeps                                                     
  union                                                                                              
  select occurred_on, user_id from simple_waters                                                     
  union                                                                                              
  select occurred_on, user_id from simple_stresses                                                   
  union                                                                                              
  select occurred_on, user_id from simple_weights                                                    
),                                                                                                   
hydrated as ( select p.*, u.company_id from participation p                                          
  inner join users u on u.id = p.user_id )                                                           
insert into hydrated_temp                                                                            
select * from hydrated;                                                                              
                                                                                                     
                                                                                                     
#---                                                                                                 
with daily_participation as (                                                                        
  select occurred_on, count(*) average                                                               
  from hydrated_temp                                                                                 
  where company_id = 4 or company_id = 12                                                            
  group by occurred_on                                                                               
  order by occurred_on asc                                                                           
)                                                                                                    
                                                                                                     
select                                                                                               
occurred_on,                                                                                         
round(average * 100 / total, 2) percent,                                                             
round(average, 2),                                                                                   
total                                                                                                
from                                                                                                 
(                                                                                                    
  select                                                                                             
  occurred_on,                                                                                       
  avg(average) over (order by occurred_on rows between 6 preceding and current row) average,         
  ( select count(*) from users where company_id = 4 or company_id = 12 and verified_at is not null and deleted_at is null) total
  from daily_participation                                                                           
) foo;                                                                                               
                                                                                                     
#---- actual counts!                                                                                 
                                                                                                     
with full_weekly_participation as (                                                                  
  select dates.occurred_on as on_date, hydrated_temp.*                                               
  from                                                                                               
  (select distinct(occurred_on) from hydrated_temp) as dates                                         
  inner join hydrated_temp                                                                           
  on hydrated_temp.occurred_on <= dates.occurred_on                                                  
  and hydrated_temp.occurred_on >= dates.occurred_on - interval '6 days'                             
  where company_id = 4 or company_id = 12                                                            
  order by on_date, occurred_on                                                                      
)                                                                                                    
                                                                                                     
select                                                                                               
on_date,                                                                                             
round(average * 100.0 / total, 4) percent,                                                           
average,                                                                                             
total                                                                                                
from (                                                                                               
  select                                                                                             
  on_date,                                                                                           
  count(on_date) average,                                                                            
  (                                                                                                  
    select count(*) from users where company_id = 4 or company_id = 12 and verified_at is not null and deleted_at is null   
  ) total                                                                                            
  from (                                                                                             
    select on_date, user_id                                                                          
    from full_weekly_participation                                                                   
    group by on_date, user_id                                                                        
  ) as weekly_participation                                                                          
  group by on_date                                                                                   
  order by on_date asc                                                                               
) foo;