coop queries

minified.sql

with prices as (select new.name, new.units, old_price, new_price, new_price::float - old_price::float as price_diff, (old_price + 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 pcg_change;

server.sql

-- works on the server
with prices as (
	select
		new.name,
		new.units,
		old_price,
		new_price,
		new_price::float - old_price::float as price_diff,
		(old_price + 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 pcg_change
;

working.sql

	select
		new.name,
		new.units,
		old_price,
		new_price,
		new_price::float - old_price::float as price_diff,
		(old_price + 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