CODEWITHSUNDEEP
sqlrankdense-rank
Prasanth G
Prasanth G

Reputation: 3

Creating duplicating rank over multiple columns

I have data as below where for one customer ID there are several orders (KEY) which is the primary key. I have also have a activity flag as below (either 0 or 1).

CUST_ID KEY FLAG
1       1   1
1       2   1
1       3   1
1       4   0
1       5   0
1       6   1
1       7   1
1       8   0
1       9   0

Now I want to create ranks as below based on the FLAG. The idea is to give same Rank as preceding row if the FLAG is same as preceding row. The Rank increments if the current value is different from preceding value.

CUST_ID KEY FLAG RN
1       1   1    1
1       2   1    1
1       3   1    1
1       4   0    2
1       5   0    2
1       6   1    3
1       7   1    3
1       8   0    4
1       9   0    4

I'm new to SQL, so please let me know if I need to reframe my question.

Upvotes: 0

Views: 305

Answers (1)

forpas
forpas

Reputation: 164089

Use LAG() window function to get each row's previous flag and then use SUM() window function to create the rankings:

SELECT CUST_ID, KEY, FLAG,
       SUM(CASE WHEN FLAG <> prev_FLAG THEN 1 END) OVER (PARTITION BY CUST_ID ORDER BY KEY) RN
FROM (
  SELECT *, LAG(FLAG, 1, FLAG - 1) OVER (PARTITION BY CUST_ID ORDER BY KEY) prev_FLAG
  FROM tablename
) t;

See the demo.

The code could be simplified, depending on the specific database that you use.

Upvotes: 1

Related Questions