sql - Keeping differenced value of month for last occuring key -

sql - Keeping differenced value of month for last occuring key -

i have dataset this:

month key 1 x 1 y 1 z 2 y 2 z 3 x 3 y

i want dataset this:

month key last_occured_before_month 1 x 0 1 y 0 1 z 0 2 y 0 2 z 0 3 x 1 3 y 0

where in each month, lastly occuring value of key , wherever month found maintain differenced value.

most databases back upwards ansi standard lag() function. think next want:

select month, key, coalesce(month - lag(month) on (partition key order month) - 1, 0 ) last_occured_before_month table t;

i find -1 curious. why lastly line 3-y-0 instead of 3-y-1? but, appears logic using.

sql