sql - MySQL Calculating the value (price) difference between rows

Question

I have a table for product price logs and I want to calculate the price difference between the first date and the last date. I have tried with the min and max function but it doesn't provide desired results.

select (min(price) - max(price)) as diff from price_logs GROUP BY product_id

The output should like.

Kindly suggest. Thanks

question from:https://stackoverflow.com/questions/65845562/mysql-calculating-the-value-price-difference-between-rows

Answer 1

You can use analytical function as follows:

Select product,
       Sum(case when rnd=1 then price else -price end) as diff
  From
(Select t.*,
       Row_number() over (partition by product order by date) as rn,
       Row_number() over (partition by product order by date desc) as rnd
  From your_table t) t
Where 1 in (rnd, rn)
Group by product;