This is the fairly common exercise of finding streaks (runs, patterns) in your data.
There is an excellent
article[
^] by Jeff Smith at SqlTeam.com that points out the "trick" you need exploit - each "run" of wins is preceded by a (single) loss. You can use this to identify a new winning streak by comparing its
streak
value against the value from the previous row. Give each block of wins and losses a number -
RunGroup
in the following sql
SELECT Match_Date, MatchNo, Player,
(SELECT COUNT(*)
FROM results G
WHERE G.streak <> GR.streak
AND G.MatchNo <= GR.MatchNo) as RunGroup
FROM results GR
WHERE streak = 1
Which will give the following result
Match_date MatchNo Player RunGroup
2015-04-08 1 Aditya 0
2015-04-12 7 Aditya 5
2015-04-13 8 Aditya 5
2015-04-15 10 Aditya 6
2015-04-19 15 Aditya 10
2015-04-19 16 Aditya 10
2015-04-20 17 Aditya 10
2015-04-21 18 Aditya 10
2015-04-22 19 Aditya 10
If we turn that sql into a Common Table Expression we can then use GROUP BY to get a count of the number of matches in each RunGroup:
;WITH CTE AS (
SELECT Match_Date, MatchNo, Player,
(SELECT COUNT(*)
FROM results G
WHERE G.streak <> GR.streak
AND G.MatchNo <= GR.MatchNo) as RunGroup
FROM results GR
WHERE streak = 1
)
SELECT Player,
COUNT(*)
FROM CTE
GROUP BY Player, RunGroup
which gives the results
Aditya 1
Aditya 2
Aditya 1
Aditya 5
Because you are only interested in hattricks (sequences of 3 wins in a row) then you need to restrict the results using
HAVING COUNT(*) >= 3
so that you only get the row
Aditya 5
To get your expected result you also need to change the final
COUNT(*)
to get the (integer) value divided by 3 i.e.
COUNT(*) / 3 as Games