Calculate percentage change of price based on Category with SQL

user_10090978 0
Viewed 212

I am writing a Query with SQL and couldn't figure it out yet...

My table looks like this:

Category Price Date
Cat1       20   2019-04
Cat2       12   2019-04
Cat3        5   2019-04
Cat1       23   2020-04
Cat2       17   2020-04
Cat3        8   2020-04

I would like to get a table that shows this:

Cat  Pct_change Period
Cat 1   0.15     2019-2020
Cat 2   0.41       "

And so on.

I can get this category by category but I have like 100 categories, cant do this manually. It would be great, too, to see both prices side by side. What I don't (can't) allow is to generate new tables just saving the data to join separate tables...

Thank you!!

price sqlite sql
2 Answers

You can use first_value():

select distinct category, min(date), max(date),
       (-1 + first_value(price) over (partition by category order by date desc) /
         first_value(price) over (partition by category order by date asc)
       ) as percent_change
from t;
user_11440350

Use LEAD() window function to get the price and date of the next date for each category:

SELECT Category,
       ROUND(1.0 * next_price / Price - 1, 2) Pct_change,
       SUBSTR(Date, 1, 4) || '-' || SUBSTR(next_date, 1, 4) Period
FROM (
  SELECT *,
         LEAD(Price) OVER (PARTITION BY Category ORDER BY Date) next_price,
         LEAD(Date) OVER (PARTITION BY Category ORDER BY Date) next_date
  FROM tablename
)
WHERE next_date IS NOT NULL

See the demo.
Results:

Category Pct_change Period
Cat1 0.15 2019-2020
Cat2 0.42 2019-2020
Cat3 0.6 2019-2020
user_104988280
Related