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:

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

CodePudding user response：

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

SELECT 8

fiddle