weekly stress percent.sql

                                                                                                    
with hydrated_stresses as (                                                                          
  select t.user_id, t.level, t.occurred_on, u.company_id, u.gender                                   
  from simple_stresses t inner join users u on u.id = t.user_id                                      
),                                                                                                   
filtered_stresses as (                                                                               
  select * from hydrated_stresses where company_id = 12 or company_id = 4                            
),                                                                                                   
weekly_stresses as (                                                                                 
  select user_id user_id, date_trunc('week', occurred_on) occurred_on, avg(level) stress_level       
  from filtered_stresses                                                                             
  group by user_id, 2                                                                                
  order by 2, 1                                                                                      
),                                                                                                   
stress_counts as (                                                                                   
  select                                                                                             
  occurred_on,                                                                                       
  sum(case when stress_level <= 1.5 then 1 else 0 end) happy,                                        
  sum(case when stress_level > 1.5 and stress_level <= 2.5 then 1 else 0 end) upbeat,                
  sum(case when stress_level > 2.5 and stress_level <= 3.5 then 1 else 0 end) ok,                    
  sum(case when stress_level > 3.5 and stress_level <= 4.5 then 1 else 0 end) sad,                   
  sum(case when stress_level > 4.5 then 1 else 0 end) distraught,                                    
  count(occurred_on) total                                                                           
  from weekly_stresses                                                                               
  group by occurred_on                                                                               
  order by occurred_on                                                                               
)                                                                                                    
                                                                                                     
select                                                                                               
occurred_on,                                                                                         
round((100 * happy::float/total)::numeric, 2) happy,                                                 
round((100 * upbeat::float/total)::numeric, 2) upbeat,                                               
round((100 * ok::float/total)::numeric, 2) ok,                                                       
round((100 * sad::float/total)::numeric, 2) sad,                                                     
round((100 * distraught::float/total)::numeric, 2) distraught,                                       
happy,                                                                                               
upbeat,                                                                                              
ok,                                                                                                  
sad,                                                                                                 
distraught,                                                                                          
total                                                                                                
from stress_counts                                                                                   
;