weekly activity percent.sql

                                                                                                     
with hydrated_activities as (                                                                        
  select t.user_id, t.intensity, t.occurred_on, u.company_id, u.gender                               
  from simple_activities t inner join users u on u.id = t.user_id                                    
),                                                                                                   
filtered_activities as (                                                                             
  select * from hydrated_activities where company_id = 12 or company_id = 4                          
),                                                                                                   
weekly_activities as (                                                                               
  select user_id user_id, date_trunc('week', occurred_on) occurred_on, avg(intensity) intensity      
  from filtered_activities                                                                           
  group by user_id, 2                                                                                
  order by 2, 1                                                                                      
),                                                                                                   
activity_counts as (                                                                                 
  select                                                                                             
  occurred_on,                                                                                       
  sum(case when intensity <= 1.5 then 1 else 0 end) light,                                           
  sum(case when intensity > 1.5 and intensity <= 2.5 then 1 else 0 end) moderate,                    
  sum(case when intensity > 2.5 then 1 else 0 end) intense,                                          
  count(occurred_on) total                                                                           
  from weekly_activities                                                                             
  group by occurred_on                                                                               
  order by occurred_on                                                                               
)                                                                                                    
                                                                                                     
select                                                                                               
occurred_on,                                                                                         
round((100 * light::float/total)::numeric, 2) light,                                                 
round((100 * moderate::float/total)::numeric, 2) moderate,                                           
round((100 * intense::float/total)::numeric, 2) intense,                                             
light,                                                                                               
moderate,                                                                                            
intense,                                                                                             
total                                                                                                
from activity_counts                                                                                 
;