CODEWITHSUNDEEP
sqlpostgresql
yung peso
yung peso

Reputation: 1766

Trying to calculate percent change between rows using LAG

I'm trying to calculate the percent change between previous rows. The ideal goal is to get the most recent data by date for the variable marketcap, followed by a LIMIT representing one day per row.

If there is a better way to using a LAG function with a time window, then that would probably be better?

Here's my query:

SELECT symbol, 
       date,
       marketcap,
       marketcap - 100.0 * (LAG(marketcap) OVER (ORDER BY date DESC) / marketcap) AS percent_change
FROM api.security_stats
WHERE symbol in ('AAPL','TSLA') 
LIMIT 254;

I'm not able to get the percent difference between rows. I've looked at other stack overflows and tried to create a similar function but I could never get the percentage difference bewteen the rows.

How can I fix my query above to do so?

EDIT: sample data:

symbol |    date    |   marketcap
--------+------------+---------------
 AAPL   | 2020-09-29 | 1978012557000
 AAPL   | 2020-09-30 | 2007832713000
 AAPL   | 2020-10-01 | 2024823267000
 AAPL   | 2020-10-02 | 1959461646000
 AAPL   | 2020-10-05 | 2019795450000
 AAPL   | 2020-10-06 | 1961888868000
 AAPL   | 2020-10-07 | 1995176484000
 AAPL   | 2020-10-08 | 1993269381000
 AAPL   | 2020-10-09 | 2027943981000
 AAPL   | 2020-10-12 | 2156760120000
 AAPL   | 2020-10-13 | 2099547030000
 AAPL   | 2020-10-14 | 2101107387000
 AAPL   | 2020-10-15 | 2092785483000
 AAPL   | 2020-10-16 | 2063485446000
 AAPL   | 2020-10-19 | 2010780054000
 AAPL   | 2020-10-20 | 2037306123000
 AAPL   | 2020-10-21 | 2026210251000
 AAPL   | 2020-10-22 | 2006792475000
 AAPL   | 2020-10-23 | 1994482992000
 AAPL   | 2020-10-26 | 1994656365000
 AAPL   | 2020-10-27 | 2021529180000
 AAPL   | 2020-10-28 | 1927907760000
 AAPL   | 2020-10-29 | 1999337436000
 AAPL   | 2020-10-30 | 1887338478000
 AAPL   | 2020-11-02 | 1849285786000
 AAPL   | 2020-11-03 | 1877678792000

Upvotes: 2

Views: 2756

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Presumably, you want the calculation per symbol. If so:

SELECT symbol, date, marketcap,
       (marketcap - LAG(marketcap) OVER (PARTITION BY symbol ORDER BY date)) * 100.0 / marketcap AS percent_change
FROM api.security_stats
WHERE symbol in ('AAPL','TSLA') 
LIMIT 254;

I also adjusted the calculation.

Note that LAG() is not usually used with ORDER BY DESC. If you want the next row instead, use LEAD().

Upvotes: 2

Related Questions