<aside> 📈

Run Times for NS Beta System -

300k records returned when all Person records were queried

Cold Run: 18s Warm Run: 5.8s

112k records returned when only Active records were queried

Warm Run: 4.4s

640k person records existed in the database

</aside>

;WITH Last5 AS (
    SELECT
        p.[Id]                  AS PersonId,
        p.[NickName],
        p.[LastName],
        s.[Name] AS [ScheduleName],
        ao.[ScheduleId],
        a.[StartDateTime],      -- use whatever "attendance time" column is correct in your schema
        ROW_NUMBER() OVER (
            PARTITION BY p.[Id]
            ORDER BY a.[StartDateTime] DESC
        ) AS rn
    FROM [Person] p
    INNER JOIN [PersonAlias] pa        ON pa.[PersonId] = p.[Id]
    INNER JOIN [Attendance] a          ON a.[PersonAliasId] = pa.[Id]
    INNER JOIN [AttendanceOccurrence] ao ON ao.[Id] = a.[OccurrenceId]
    INNER JOIN [Group] g               ON g.[Id] = ao.[GroupId]
    INNER JOIN [GroupType] gt          ON gt.[Id] = g.[GroupTypeId]
    INNER JOIN [Schedule] s ON s.[Id] = ao.[ScheduleId]
    WHERE gt.[AttendanceCountsAsWeekendService] = 1
        AND a.[DidAttend] = 1
),
Counts AS (
    SELECT
        PersonId,
        NickName,
        LastName,
        ScheduleName,
        ScheduleId,
        COUNT(*) AS Cnt,
        MAX([StartDateTime]) AS [LastAttendanceDateTime]
    FROM Last5
    WHERE rn <= 5
    GROUP BY PersonId, ScheduleId, NickName, LastName, ScheduleName
)
SELECT
    PersonId,
    ScheduleId,
    NickName,
    LastName,
    ScheduleName,
    [LastAttendanceDateTime],
    Cnt
FROM (
    SELECT
        PersonId,
        NickName,
        LastName,
        ScheduleName,
        ScheduleId,
        Cnt,
        [LastAttendanceDateTime],
        ROW_NUMBER() OVER (
            PARTITION BY PersonId
            ORDER BY Cnt DESC, ScheduleId
        ) AS pick
    FROM Counts
) x
WHERE pick = 1;