Determining "longest X streak" from a table (SQL Server 2018)

Question

I have a table where the data is in the format

+---------------------+-------------------------------------+--------+
|     Start Time      |                Name                 | Result |
+---------------------+-------------------------------------+--------+
| 19/09/2018 00:02:06 | Kevin Jones                         | WIN    |
| 19/09/2018 00:21:44 | Claire Miley                        | LOSE   |
| 19/09/2018 01:07:54 | Claire Miley                        | FISH   |
| 19/09/2018 01:16:40 | Kevin Jones                         | WIN    |
| 19/09/2018 01:14:01 | Kevin Jones                         | RED    |
| 19/09/2018 01:11:12 | Claire Miley                        | LOSE   |
| 19/09/2018 01:28:37 | Claire Miley                        | LOSE   |
| 19/09/2018 01:09:42 | Kevin Jones                         | LOSE   |
| 19/09/2018 01:05:50 | Kevin Jones                         | WIN    |
| 19/09/2018 01:29:52 | Martyn Jones                        | BLUE   |
| 19/09/2018 01:01:01 | Claire Miley                        | RED    |
| 19/09/2018 01:00:21 | Claire Miley                        | WIN    |
| 19/09/2018 00:57:01 | Kevin Jones                         | SOCK   |
| 19/09/2018 01:39:52 | Margaret Weir                       | WIN    |
| 19/09/2018 00:54:06 | Margaret Weir                       | CALL   |
| 19/09/2018 00:53:16 | Martyn Jones                        | LOSE   |
| 19/09/2018 00:50:47 | Kevin Jones                         | FREE   |
| 19/09/2018 00:48:40 | Martyn Jones                        | WIN    |
| 19/09/2018 00:30:14 | Claire Miley                        | WIN    |
| 19/09/2018 00:25:09 | Kevin Jones                         | LOSE   |
| 19/09/2018 02:07:20 | Margaret Weir                       | FISH   |
| 19/09/2018 02:07:30 | Martyn Jones                        | WIN    |
| 19/09/2018 02:09:03 | Kevin Jones                         | RED    |
| 19/09/2018 00:24:11 | Claire Miley                        | LOSE   |
| 19/09/2018 00:17:42 | Kevin Jones                         | LOSE   |
| 19/09/2018 02:15:21 | Kevin Jones                         | LOSE   |
| 19/09/2018 02:19:10 | Martyn Jones                        | WIN    |
| 19/09/2018 00:15:49 | Martyn Jones                        | BLUE   |
| 19/09/2018 00:14:25 | Kevin Jones                         | RED    |
| 19/09/2018 00:11:03 | Kevin Jones                         | WIN    |
| 19/09/2018 00:10:10 | Claire Miley                        | SOCK   |
| 19/09/2018 00:09:56 | Martyn Jones                        | WIN    |
| 19/09/2018 00:05:24 | Martyn Jones                        | CALL   |
| 19/09/2018 00:03:01 | Margaret Weir                       | LOSE   |
| 19/09/2018 00:01:17 | Martyn Jones                        | FREE   |
| 19/09/2018 01:18:46 | Kevin Jones                         | WIN    |
| 19/09/2018 01:23:21 | Margaret Weir                       | WIN    |
| 19/09/2018 01:28:51 | Kevin Jones                         | BLUE   |
| 19/09/2018 01:29:34 | Kevin Jones                         | RED    |
| 19/09/2018 01:07:54 | Claire Miley                        | WIN    |
+---------------------+-------------------------------------+--------+

With a lot more columns that aren't important to the question. What I want to figure out is who currently holds the longest streak of results without one of them being a WIN result and how long that streak is. Is this something I can do in SQL or is it best to do after extracting the raw data to another program?

In this sample data the result I want is

Kevin Jones

5 results in a row without a WIN result

Thanks to martin smith

Answer 1

Take a look at this solution:

SELECT TOP 1 Name, COUNT(*)-1 Total FROM
(
    SELECT *, SUM(CASE WHEN Result='WIN' THEN 1 ELSE 0 END) OVER (PARTITION BY Name ORDER BY Start) Streak
    FROM #t
) T
GROUP BY Name,Streak
ORDER BY Total DESC

Every WIN starts new group. That way, number of items in this group-1 is the result.

---

To see demo, please run following:

--CREATE TABLE #t
--(
--  Start datetime,
--  Name varchar(20),
--  Result varchar(10)
--)
--INSERT #t VALUES
--('2018/09/19 00:02:06', 'Kevin Jones  ', 'WIN '),
--('2018/09/19 00:21:44', 'Claire Miley ', 'LOSE'),
--('2018/09/19 01:07:54', 'Claire Miley ', 'FISH'),
--('2018/09/19 01:16:40', 'Kevin Jones  ', 'WIN '),
--('2018/09/19 01:14:01', 'Kevin Jones  ', 'RED '),
--('2018/09/19 01:11:12', 'Claire Miley ', 'LOSE'),
--('2018/09/19 01:28:37', 'Claire Miley ', 'LOSE'),
--('2018/09/19 01:09:42', 'Kevin Jones  ', 'LOSE'),
--('2018/09/19 01:05:50', 'Kevin Jones  ', 'WIN '),
--('2018/09/19 01:29:52', 'Martyn Jones ', 'BLUE'),
--('2018/09/19 01:01:01', 'Claire Miley ', 'RED '),
--('2018/09/19 01:00:21', 'Claire Miley ', 'WIN '),
--('2018/09/19 00:57:01', 'Kevin Jones  ', 'SOCK'),
--('2018/09/19 01:39:52', 'Margaret Weir', 'WIN '),
--('2018/09/19 00:54:06', 'Margaret Weir', 'CALL'),
--('2018/09/19 00:53:16', 'Martyn Jones ', 'LOSE'),
--('2018/09/19 00:50:47', 'Kevin Jones  ', 'FREE'),
--('2018/09/19 00:48:40', 'Martyn Jones ', 'WIN '),
--('2018/09/19 00:30:14', 'Claire Miley ', 'WIN '),
--('2018/09/19 00:25:09', 'Kevin Jones  ', 'LOSE'),
--('2018/09/19 02:07:20', 'Margaret Weir', 'FISH'),
--('2018/09/19 02:07:30', 'Martyn Jones ', 'WIN '),
--('2018/09/19 02:09:03', 'Kevin Jones  ', 'RED '),
--('2018/09/19 00:24:11', 'Claire Miley ', 'LOSE'),
--('2018/09/19 00:17:42', 'Kevin Jones  ', 'LOSE'),
--('2018/09/19 02:15:21', 'Kevin Jones  ', 'LOSE'),
--('2018/09/19 02:19:10', 'Martyn Jones ', 'WIN '),
--('2018/09/19 00:15:49', 'Martyn Jones ', 'BLUE'),
--('2018/09/19 00:14:25', 'Kevin Jones  ', 'RED '),
--('2018/09/19 00:11:03', 'Kevin Jones  ', 'WIN '),
--('2018/09/19 00:10:10', 'Claire Miley ', 'SOCK'),
--('2018/09/19 00:09:56', 'Martyn Jones ', 'WIN '),
--('2018/09/19 00:05:24', 'Martyn Jones ', 'CALL'),
--('2018/09/19 00:03:01', 'Margaret Weir', 'LOSE'),
--('2018/09/19 00:01:17', 'Martyn Jones ', 'FREE'),
--('2018/09/19 01:18:46', 'Kevin Jones  ', 'WIN '),
--('2018/09/19 01:23:21', 'Margaret Weir', 'WIN '),
--('2018/09/19 01:28:51', 'Kevin Jones  ', 'BLUE'),
--('2018/09/19 01:29:34', 'Kevin Jones  ', 'RED '),
--('2018/09/19 01:07:54', 'Claire Miley ', 'WIN ')

Answer 2

This should do it (Demo)

WITH T
     AS (SELECT *,
                ROW_NUMBER()
                  OVER (
                    PARTITION BY [Name]
                    ORDER BY [Start Time]) - ROW_NUMBER()
                                               OVER (
                                                 PARTITION BY [Name], Win
                                                 ORDER BY [Start Time]) AS Grp
         FROM   Table1
                CROSS APPLY (SELECT IIF([Result] = 'WIN', 1, 0))CA(Win))
SELECT TOP 1 [Name],
             count(*),
             MIN([Start Time]) AS StreakStart,
             MAX([Start Time]) AS StreakEnd
FROM   T
WHERE  Win = 0
GROUP  BY [Name],
          Grp
ORDER  BY count(*) DESC