CODEWITHSUNDEEP
sqlgoogle-bigquery
Labextermin
Labextermin

Reputation: 3

SQLReset rank based on row or flag

Here is my input and expected output. I tried using ranks and flags to identify how to reset the rank and can’t seem to get it to work.

Input:

Type  Date Rank
PA 1/1/2000  1
ON 1/2/2000  2
Acq 1/4/2000 3
PA  2/1/2000 4
ON  4/2/2000 5
ON  6/3/2000 6
PA  6/11/2000 7

Output:

Type  Date Rank
PA 1/1/2000  1
ON 1/2/2000  2
Acq 1/4/2000 3
PA  2/1/2000 1
ON  4/2/2000 2
ON  6/3/2000 3
PA  6/11/2000 1

Basically whenever type equals PA it should reset the count.

Trying to do this is in BigQuery

Upvotes: 0

Views: 435

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this by defining groups using a cumulative count of 'PA'. I recommend countif():

select i.*, row_number() over (partition by grp order by date) as ranking
from (select i.*,
             countif(type = 'PA') over (order by date) as grp
      from input i
     ) i;

If you already have rank as a column, you could also just subtract out the most recent value of 'PA'. That would be:

select t.*,
       (ranking + 1 -
        coalesce(max(case when type = 'PA' then ranking end) over (order by ranking), 1)
       ) as new_ranking
from input

Upvotes: 2

Sabri Karagönen
Sabri Karagönen

Reputation: 2365

You can create a virtual group using PA. So every time a new PA row comes group index increases by 1. I named that column as reset_group.

Then, I used this column as the partition column and used row_number() to calculate rank within the group.

WITH
data AS
(
  SELECT 'PA' as Type, '2000-01-01' as DATE UNION ALL
  SELECT 'ON' as Type, '2000-01-02' as DATE UNION ALL
  SELECT 'Acq' as Type, '2000-01-04' as DATE UNION ALL
  SELECT 'PA' as Type, '2000-02-01' as DATE UNION ALL
  SELECT 'ON' as Type, '2000-03-02' as DATE UNION ALL
  SELECT 'ON' as Type, '2000-06-03' as DATE UNION ALL
  SELECT 'PA' as Type, '2000-06-11' as DATE
),
reset_groups AS
(
  select *, SUM(IF(Type = 'PA', 1, 0)) OVER (ORDER BY Date) as reset_group
  from data
)
select *, ROW_NUMBER() OVER (PARTITION BY reset_group ORDER BY Date) as rank
from reset_groups

Upvotes: 1

Related Questions