I have a Presto table as show below:
| Item | Price | Date |
|---|---|---|
| A | 25.96 | 20/11/2022 |
| B | 11.89 | 21/11/2022 |
| B | 11.89 | 22/11/2022 |
| A | 26.67 | 23/11/2022 |
| A | 26.45 | 24/11/2022 |
| B | 11.75 | 25/11/2022 |
| A | 26.45 | 26/11/2022 |
The table has 3 fields. One represents the item field and the other represents the price field whereas the last one is the date when the price was obtained.
What I would like to find out is how many times the prices have changed for an item and order them in descending order.
In the example above, the result I would like to obtain is:
A -> 3
B -> 2
From the documentation I have gone through, it looks like I need to make use of Window function to know the previous fetch price for every fetch and then compare.
Since I am new to these kinds of functions, I was unable to find the right solution. Can someone help me with the expected query here?
you need a date column to make sure of row orders :
with cte as (
select
item
, case when Price = lag(Price,1,price) over (partition by item order by date) then 0 else 1 end as price_Change
from tablename
)
select item , sum(price_change) as price_change
from cte
group by item