SQL query of Month over Month difference-CodePudding

Question: The leadership team wants to see if monthly purchases are increasing in value or decreasing. Calculate the personal month over month difference in purchase amount so that each month has a value that represents the person’s difference from the previous month

My code:

SELECT 
  person_id, 
  Month(transaction_date), 
  SUM(purchase_amount) - LAG(SUM(purchase_amount)) 
    OVER (ORDER BY person_id, Month(transaction_date))
FROM transactions
GROUP BY person_id, Month(transaction_date)
ORDER BY person_id, Month(transaction_date);

This code seems to be running, however, the output is incorrect. For every 1st unique patient id the sum-lag amount should be null but that is not what I am getting. Do I need to use a Paritition By function?

CodePudding user response：

You mostly answered your own question. You just need to replace order by person_id in your window with partition by person_id.

SELECT 
  person_id, 
  Month(transaction_date), 
  SUM(purchase_amount) - LAG(SUM(purchase_amount)) OVER (
    partition by person_id 
    order by month(transaction_date)
  )
FROM transactions
GROUP BY person_id, Month(transaction_date)
ORDER BY person_id, Month(transaction_date)