SQLize | PHPize | SQLtest

-- Step 1: Assign group numbers to consecutive flips WITH RankedFlips AS ( SELECT [flipper], -- Employee performing the flip [flip result], -- Result of the flip (H or T) -- Calculate the difference between row numbers to create groups of consecutive flips ROW_NUMBER() OVER (PARTITION BY [flipper] ORDER BY [time of flip]) - ROW_NUMBER() OVER (PARTITION BY [flipper], [flip result] ORDER BY [time of flip]) AS grp FROM [IWPR].[dbo].[coin_flipping] ), -- Step 2: Calculate the length of each run of heads GroupedHeads AS ( SELECT [flipper], -- Employee performing the flip COUNT(*) AS run_length, -- Length of the run of heads grp -- Group number of the run FROM RankedFlips WHERE [flip result] = 'H' -- Consider only the runs of heads GROUP BY [flipper], grp -- Group by employee and group number ) -- Step 3: Find the maximum run length of heads for each flipper SELECT [flipper], -- Employee performing the flip MAX(run_length) AS longest_run_heads -- Longest unbroken run of heads FROM GroupedHeads GROUP BY [flipper] ORDER BY 2 DESC; -- Group by employee