activity_percents.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                          
),                                                                                                   
activity_counts as (                                                                                 
  select                                                                                             
  occurred_on,                                                                                       
  sum(case when intensity=1 then 1 else 0 end) light,                                                
  sum(case when intensity=2 then 1 else 0 end) moderate,                                             
  sum(case when intensity=3 then 1 else 0 end) intense,                                              
  count(occurred_on) total                                                                           
  from filtered_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                                              
from activity_counts                                                                                 
;