I would like to calculate the growth for one ticker, while I'm querying multiple tickers. The code I'm trying only works when I run it on a single ticker.
Source Table:
| line | ticker | calendardate | revenueusd | growth |
|---|---|---|---|---|
| 1 | INTC | 2021-09-30 | 19192 | |
| 2 | AMD | 2021-09-30 | 4313 | |
| 3 | AMD | 2021-12-31 | 4826 | ? |
| 4 | INTC | 2021-12-31 | 20528 | ? |
| 5 | INTC | 2022-03-31 | 18353 | ? |
| 6 | AMD | 2022-03-31 | 5887 | ? |
| 7 | INTC | 2022-06-30 | 15321 | ? |
| 8 | AMD | 2022-06-30 | 6550 | ? |
SELECT
ticker,
calendardate,
(revenueusd - LAG (revenueusd) OVER (ORDER BY calendardate ASC)) / LAG (revenueusd) OVER (ORDER BY calendardate ASC) AS growth
FROM
sf1
WHERE
ticker IN ('AMD', 'INTC')
ORDER BY calendardate ASC
Is there anything that I can add to make LAG get the corresponding ticker not just the one "above"? Currently this code at line 4 (INTC) would use the revenue for line 3 (AMD). Insted I would need line 1 (INTC) data.
The data comes from: Nasdaq, Core US Fundamentals Data
you need to PARTITION BY and what you also need to avoid integer division is to9 convert the data into decimal or float
SELECT
ticker,
calendardate,
(revenueusd - LAG (revenueusd) OVER (PARTITION BY ticker ORDER BY calendardate ASC)::DECIMAL(7,2)
/ LAG (revenueusd) OVER (PARTITION BY ticker ORDER BY calendardate ASC))::DECIMAL (7,2) AS growth
FROM
sf1
WHERE
ticker IN ('AMD', 'INTC')
ORDER BY calendardate ASC
| ticker | calendardate | growth |
|---|---|---|
| AMD | 2021-09-30 | null |
| INTC | 2021-09-30 | null |
| INTC | 2021-12-31 | 20527.00 |
| AMD | 2021-12-31 | 4825.00 |
| AMD | 2022-03-31 | 5886.00 |
| INTC | 2022-03-31 | 18352.00 |
| AMD | 2022-06-30 | 6549.00 |
| INTC | 2022-06-30 | 15320.00 |
SELECT 8