Querying for Consecutive Rows with Certain Characteristics

Question

I've got a table with the following columns:

id int(10)
user int(10)
winner int(10)
profit double
created datetime

The winner column can be either 0 or 1. I'd like to create a query that returns the maximum number of consecutive winners as ordered by the created datetime column along with the first and last created date as well as the sum of the profit column from that period of consecutive winners.

Answer 1

I haven't tested it but maybe this will work.

    select first_winner.created, last_winner.created, sum(mid_winner.profit)
          from T first_winner
     join T last_winner
          on first_winner.created <= last_winner.created
          and first_winner.winner = 1
          and last_winner.winner = 1
          and not exists -- no losers in between first_winner and last_winner
          (
           select * from T loser
           where loser.winner = 0
            and first_winner.created <= loser.created 
                                    and loser.created <= last_winner.created
          )
     join T mid_winner
          on first_winner.created <= mid_winner.created
                                 and mid_winner.created <= last_winner.created
          and mid_winner.winner = 1
     left join T bef_first_winner  -- winner before first winner with no losers in between
          on bef_first_winner.winner = 1
          and bef_first_winner.created < first_winner.created
          and not exists
          ( 
           select * from T b_loser
           where b_loser.winner = 0
            and bef_first_winner.created <= b_loser.created 
               and b_loser.created <= first_winner.created
          )
     left join T after_last_winner -- winner after last winner with no losers in between
          on after_last_winner.winner = 1
          and last_winner.created < after_last_winner.created
          and not exists
          ( 
           select * from T a_loser
           where a_loser.winner = 0
            and last_winner.created <= a_loser.created 
               and a_loser.created <= after_last_winner.created
          )
    where bef_first_winner.id is null
        and after_last_winner.id is null
   group by first_winner.created, last_winner.created

Answer 2

Here's a possible solution that looks at winning streaks per userid.

select head.userid, head.id, sum(profit), count(*)
from #bingo b
inner join (
    select cur.userid, cur.id
    from #bingo cur
    left join #bingo prev 
        on cur.userid = prev.userid
        and prev.id < cur.id
        and not exists(
            select * 
            from #bingo inbetween 
            where prev.userid = inbetween.userid 
            and prev.id < inbetween.id 
            and inbetween.id < cur.id)
    where cur.winner = 1
    and IsNull(prev.winner,0) = 0
) head
    on head.userid = b.userid 
    and head.id <= b.id

left join (
    select cur.userid, cur.id
    from #bingo cur
    left join #bingo prev 
        on cur.userid = prev.userid
        and prev.id < cur.id
        and not exists(
            select * 
            from #bingo inbetween 
            where prev.userid = inbetween.userid 
            and prev.id < inbetween.id 
            and inbetween.id < cur.id)
    where cur.winner = 1
    and IsNull(prev.winner,0) = 0
) nexthead 
    on nexthead.userid = b.userid 
    and head.id < nexthead.id 
    and nexthead.id <= b.id
where nexthead.id is null 
and b.winner = 1
group by head.userid, head.id

The two "heads" subqueries are identical, you could put them in a view or a WITH where those are supported. The "heads" subquery searches for each head of a winning streak; that is, the first win or a win that's preceeded by a loss. I'm assuming your id's increase over time, so I'm not using the Created column.

The query below that searches the corresponding head for every row. A head's id must be smaller or equal to the current row's id, and there must be no other head in between.

After that it's a simple matter of grouping on the head, and summing the profits and counting the rows.