Search code examples
sqlamazon-redshiftwindow-functionslag

SQL - Redshift Lag Function getting duplicates

I have a table below

ID  Type  Sub_ID   Date    CNT
A    P     A1    4/1/2020   5
A    P     A2    4/5/2020   NULL
A    P     A3    4/8/2020   NULL

What I want to get is 

ID  Type  Sub_ID   Date    CNT    LAG
A    P     A1    4/1/2020   5     NULL
A    P     A2    4/5/2020   NULL   5
A    P     A3    4/8/2020   NULL   NULL

I have below queries but it's giving me duplicates like

ID  Type  Sub_ID   Date    CNT    LAG
A    P     A1    4/1/2020   5     NULL
A    P     A1    4/1/2020   5      5 (duplicate)
A    P     A2    4/5/2020   NULL   5 
A    P     A2    4/5/2020   NULL   NULL (duplicate)
A    P     A3    4/8/2020   NULL   NULL

select *, lag(cnt,1) over (partition by id, type order by date)
from mytable

Anything wrong?

Solution

  • Ok...I have duplicate data in my table..Need to dedup first and then do the lag on top of the cleaned table