Unable to use lag function correctly in sql

Question

I have created a table from multiple tables like this:

Week | Cid | CustId | L1
10 | 1 | 1 | 2
10 | 2 | 1 | 2
10 | 5 | 1 | 2
10 | 4 | 1 | 1
10 | 3 | 2 | 1
4  | 6 | 1 | 2
4  | 7 | 1 | 2

I want the output as:

Repeat
0
1
1
0
0
0
1

So, basically what I want is for each week, if a person (custid) comes in again with the same L1, then the value in the column Repeat should become 1, otherwise 0 ( so like, here, in row 2 & 3, custid 1, came with L1=2 again, so it will get 1 in column "Repeat", however in row 4, custid 1 came with L1=1, so it will get value as ).

By the way, the table isn't ordered (as I've shown).

I'm trying to do it as follows:

select t.*,
lag(0, 1, 0) over (partition by week, custid, L1 order by cid) as repeat
from
table;

But this is not giving the output and is giving empty result.

Answer 1

I think you need a case, but I would use row_number() for this:

select t.*,
       (case when row_number() over (partition by week, custid, l1 order by cid) = 1
             then 0 else 1
        end) as repeat
from table;