CODEWITHSUNDEEP
mysql
jomx99
jomx99

Reputation: 3

Calculate daily percent change in mysql

Given the ff sample table:

DATE SYMBOL CLOSING_PRICE
2022-08-01 ABC 100
2022-08-01 CDE 50
2022-08-01 XYZ 10
2022-08-02 ABC 125
2022-08-02 CDE 40
2022-08-02 XYZ 15
2022-08-03 ABC 150
2022-08-03 CDE 50
2022-08-03 XYZ 20

I want to make a query that will output the following result:

DATE SYMBOL DAILY_CHANGE
2022-08-01 ABC -
2022-08-01 CDE -
2022-08-01 XYZ -
2022-08-02 ABC 25%
2022-08-02 CDE -20%
2022-08-02 XYZ 50%
2022-08-03 ABC 20%
2022-08-03 CDE 20%
2022-08-03 XYZ 33.33%

My data is from the crypto market so there are no gaps between dates. I was planning on expanding to other asset classes like stocks so there will be gaps. Was wondering how would i also deal with that?

Upvotes: 0

Views: 48

Answers (1)

MURTUZA BORIWALA
MURTUZA BORIWALA

Reputation: 556

Here is the code snippet to achieve what you have asked.

SELECT DATE, SYMBOL, 
       (CLOSING_PRICE - LAG(CLOSING_PRICE) OVER (PARTITION BY SYMBOL ORDER BY DATE)) / LAG(CLOSING_PRICE) OVER (PARTITION BY SYMBOL ORDER BY DATE) AS DAILY_CHANGE
FROM TABLE_NAME;

Upvotes: 1

Related Questions