I'm using the LAG() function in SQL to calculate the difference between the current month's tips sum and the previous month's tips sum for each taxi driver. However, the LAG() function is returning 0 for every row, even though there should be previous rows to reference.
This is the example of the query I am using now:
SELECT
td.taxi_id,
td.year,
td.month,
td.tips_sum,
(td.tips_sum -
LAG(td.tips_sum, 1, 0) OVER (PARTITION BY td.taxi_id ORDER BY td.year, td.month) /
NULLIF(LAG(td.tips_sum, 1, 0) OVER (PARTITION BY td.taxi_id ORDER BY td.year, td.month), 0) AS tips_change
FROM
tips_data td
ORDER BY
td.tips_sum DESC
LIMIT 3