Query for Consecutive Strings with Specific Characteristics
I have 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 would like to create a query that returns the maximum number of consecutive winners according to the ordinal of the created datetime column along with the first and last date created, and the sum of the column profits from that period of consecutive winners.
a source to share
A possible solution can be found here that looks at the winning streaks for each user.
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 "head" subqueries are identical, you can put them in view or WITH where they are supported. The subquery "head" searches for each chapter of the winning streak; that is, the first victory or the victory suffered by loss. I am assuming that your ID is increasing over time, so I am not using the Create columns.
The query is below that looks for a matching title for each row. The chapter ID must be less than or equal to the current row ID and there must be no other head in between.
After that, it's a simple matter of head-grouping and summing up the profits and counting the lines.
a source to share
I haven't tested it, but maybe it 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
a source to share