prices.sql

with prices as (  
  select     new.name,     new.units,     old_price,     new_price,     new_price::float - old_price::float as price_diff,     (old_price %2B new_price)/2.0 as price_avg   
  from (     
    select name, units, price as old_price
    from coop where date = current_date - '7 days'::interval   
  ) old   
  join (     
    select name, units, price as new_price     
    from coop where date = (select max(date) from coop)   
  ) new     
  on old.name = new.name     and old.units = new.units 
)  
select   *,   price_diff/price_avg * 100 as pcg_change 
from prices order by price_diff;