weekly hydration percent.sql

                                                                                                 
with fixed_waters as (                                                                               
  select                                                                                             
  user_id,                                                                                           
  occurred_on,                                                                                       
  sum((case when level=3 then 4 else level end ) * 8) as ounces                                      
  from simple_waters                                                                                 
  group by occurred_on, user_id                                                                      
  order by occurred_on, user_id                                                                      
),                                                                                                   
                                                                                                     
hydrated_waters as (                                                                                 
  select t.user_id, t.ounces, t.occurred_on, u.company_id, u.gender                                  
  from fixed_waters t inner join users u on u.id = t.user_id                                         
),                                                                                                   
filtered_waters as (                                                                                 
  select * from hydrated_waters where company_id = 12 or company_id = 4                              
),                                                                                                   
weekly_waters as (                                                                                   
  select user_id user_id, date_trunc('week', occurred_on) occurred_on, avg(ounces) ounces            
  from filtered_waters                                                                               
  group by user_id, 2                                                                                
  order by 2, 1                                                                                      
),                                                                                                   
water_counts as (                                                                                    
  select                                                                                             
  occurred_on,                                                                                       
  sum(case when ounces <= 24 then 1 else 0 end) light,                                               
  sum(case when ounces > 24 and ounces <= 48 then 1 else 0 end) moderate,                            
  sum(case when ounces > 48 and ounces <= 96 then 1 else 0 end) intense,                             
  sum(case when ounces > 96 then 1 else 0 end) swimming,                                             
  count(occurred_on) total                                                                           
  from weekly_waters                                                                                 
  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,                                             
round((100 * swimming::float/total)::numeric, 2) swimming,                                           
light,                                                                                               
moderate,                                                                                            
intense,                                                                                             
swimming,                                                                                            
total                                                                                                
from water_counts                                                                                    
;