T-SQL query to find the most consecutive number of under par scores in a golf database

Question

Let's say we have a SQL Server database for our golf course. We have a table called Results that has the following columns (all INT NOT NULL):

• PlayerId
• RoundId
• HoleId
• Score

We also have a table called Holes that has the following columns (all INT NOT NULL):

• HoleId
• Number (1 through 18)
• Par (3, 4 or 5)

We want to write a single query that will find who has had the most consecutive under par scores (hole in one, birdie, eagle, double eagle) in a single round. The query should return the following info:

• PlayerId
• RoundId
• NumberOfConsecutiveUnderParScores (an INT)
• UnderParHoleNumbers (a VARCHAR containing comma separated values in ascending order)

I could not come up with anything useful. Maybe using a recursive common table expression is the way to go?

Answer 1

That’s a gaps and islands problem, where an island is a series of hole scores under par for a given player and round.

We can identify the islands with a window sum, then aggregate and sort.

select playerId, roundId,
    count(*) NumberOfConsecutiveUnderParHoles,
    string_agg(number, ', ') within group (order by number) UnderParHoleNumbers
from (
    select r.*, h.number,
        sum(case when r.score < h.par then 0 else 1 end) over(partition by r.playerId, r.roundId order by h.number) grp
    from results r
    inner join holes h on h.holeId = r.holeId
) t
group by playerId, roundId, grp
order by count(*) desc

If you just want the longest under-par streak, you can turn the select to select top (1) with ties.