finding percentages between 2 different columns in sql

Question

I was create this query:

select first_price, last_price, cast((sum(1 - (first_price / nullif(last_price,0)))) as double) as first_vs_last_percentages 
from prices 
group by first_price, last_price 
having first_vs_last_percentages >= 0.1 

unfortunately this is my wrong data in first_vs_last_percentages col

ID	first_price	last_price	first_vs_last_percentages
1	10	11	1-(10/11) = 1.0
2	66	68	1-(66/68) = 1.0

It was supposed to return this output:

ID	first_price	last_price	first_vs_last_percentages
1	10	11	1-(10/11) = 0.0909
2	66	68	1-(66/68) = 0.0294

if someone has a good solution and it will be in presto syntax it will be wonderful.

Answer 1

It seems you got struck by another case of integer division (your cast to double is a bit late), update the query so the divisor or dividend type changes (for example by multiplying one of them by 1.0 which is a bit shorter then cast to double):

select -- ...
  , sum(1 - (first_price * 1.0) / nullif(last_price, 0)) first_vs_last_percentages
from ...

P.S.

Your query is a bit strange, not sure why do you need grouping and sum here.