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                            
),                                                                                                   
stress_counts as (                                                                                   
  select                                                                                             
  occurred_on,                                                                                       
  sum(case when level=1 then 1 else 0 end) happy,                                                    
  sum(case when level=2 then 1 else 0 end) upbeat,                                                   
  sum(case when level=3 then 1 else 0 end) ok,                                                       
  sum(case when level=4 then 1 else 0 end) sad,                                                      
  sum(case when level=5 then 1 else 0 end) distraught,                                               
  count(occurred_on) total                                                                           
  from filtered_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                                        
from stress_counts                                                                                   
;