CODEWITHSUNDEEP
sql
Jerry
Jerry

Reputation: 13

SQL Query to find streak with unique record types

This is my example table data:

CustomerType     Date          Records
    1          2018-01-01        233
    2          2018-01-01         12
    1          2018-01-02          0
    2          2018-01-02         34
    1          2018-01-03          0
    2          2018-01-03         35
    1          2018-01-04          0
    2          2018-01-04          0
    1          2018-01-05       5562
    2          2018-01-05          3

I would like the output to show the maximum 0 Record streak, by CustomerType, to be fetched for each CustomerType. In other words, I would like the maximum number of zero records in a row for each particular CustomerType

Example output

CustomerType  MaxZeroRecordsStreak
      1              3
      2              1

The farthest I've gotten so far is something like this:

SELECT CustomerType,
 (SELECT COUNT(*)
 FROM Table1 as t1
 AND Records = '0'
 AND t1.Date <= t2.Date) AS MaxZeroRecordsStreak
FROM Table2 As t2
ORDER BY CustomerType ASC;

But this is clearly not what I want.

Any help with this complex query would be appreciated.

Upvotes: 1

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Most databases support the ANSI-standard window functions. You can do this using the difference of row_numbers(). The following gets all sequences of zeros for a customer type:

select customertype, count(*) as num_zeros
from (select t.*,
             row_number() over (partition by records, customertype order by date) as seqnum_rt,
             row_number() over (partition by customertype order by date) as seqnum_t
      from t
     ) t
where records = 0
group by records, customertype, seqnum_t - seqnum_rt;

You can get the maximum just by using this as a subquery:

select customertype, max(num_zeros)
from (select customertype, count(*) as num_zeros
      from (select t.*,
                   row_number() over (partition by records, customertype order by date) as seqnum_rt,
                   row_number() over (partition by customertype order by date) as seqnum_t
            from t
           ) t
      where records = 0
      group by records, customertype, seqnum_t - seqnum_rt
     ) t
group by customertype;

Upvotes: 1

Related Questions