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.
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.