Longest_Winning_Streak_Ever.sql

Longest_Winning_Streak_Ever.sql

WITH 

'vars' AS (
    SELECT
        1 'all_seasons', -- just one season or all seasons?
        1 'any_time' -- start at beg of season or anytime?
),

'results' AS (
    SELECT
        res.id 'result_id',
        res.ordinal 'ordinal',
        res.unit_id 'unit_id',
        u.code 'unit_code',
        u.name_code 'unit_name_code',
        u.country_code 'unit_country_code',
        cou.flag 'unit_flag',
        u.a_first_name 'unit_a_first_name',
        u.a_last_name 'unit_a_last_name',
        u.b_first_name 'unit_b_first_name',
        u.b_last_name 'unit_b_last_name',
        ction.season_code 'season_code',
        ction.event_code 'event_code',
        ction.division_code 'division_code',
        ction.discipline_code 'discipline_code',
        meet.start_date 'start_date'
    FROM scores_result res
    INNER JOIN scores_unit u
        ON res.unit_id=u.id
    INNER JOIN scores_country cou
        ON u.country_code=cou.code
    INNER JOIN scores_competition ction
        ON res.competition_id=ction.id
    INNER JOIN scores_contest cont
        ON ction.contest_id=cont.id
    INNER JOIN scores_meet meet
        ON cont.meet_id=meet.id
    WHERE 
        (:season_all OR ction.season_code IN ({season_codes}))
    AND (:division_all OR ction.division_code IN ({division_codes}))
    AND ction.discipline_code IN ({discipline_codes})
    -- AND ction.season_code=2024
    AND (:event_all OR (:event_intl AND cont.isu_sanctioned=1) OR (:event_major AND ction.major=1 AND ction.event_code!='wtt') OR (:event_major_wtt AND (ction.major=1 OR ction.event_code='wtt')) OR ction.event_code IN ({event_codes}))
    AND cont.contest_type='I'
--    AND res.status>=0 -- ignore WDs, DSQs
    ORDER BY meet.start_date ASC
)

SELECT
    res.unit_id 'unit_id',
    res.unit_code 'unit_code',
    res.unit_name_code 'unit_name_code',
    res.unit_country_code 'unit_country_code',
    res.unit_flag 'unit_flag',
    res.unit_a_first_name 'unit_a_first_name',
    res.unit_a_last_name 'unit_a_last_name',
    res.unit_b_first_name 'unit_b_first_name',
    res.unit_b_last_name 'unit_b_last_name',
    MAX((
        SELECT count()+1
        FROM results res1 
        WHERE 
            res1.unit_id=res.unit_id
        AND ((SELECT all_seasons FROM vars) OR res1.season_code=res.season_code)
        AND res1.ordinal=1
        AND res1.start_date > res.start_date
        AND 0=(
            SELECT count()
            FROM results res2
            WHERE 
                res2.unit_id=res.unit_id
            AND res2.ordinal != 1
            AND res2.start_date BETWEEN res.start_date AND res1.start_date
        )
        ORDER BY res1.start_date ASC
    )) 'win_count',
    res.season_code 'start_season_code',
    (
        SELECT res1.season_code
        FROM results res1 
        WHERE 
            res1.unit_id=res.unit_id
        AND ((SELECT all_seasons FROM vars) OR res1.season_code=res.season_code)
        AND res1.ordinal=1
        AND res1.start_date > res.start_date
        AND 0=(
            SELECT count()
            FROM results res2
            WHERE 
                res2.unit_id=res.unit_id
            AND res2.ordinal != 1
            AND res2.start_date BETWEEN res.start_date AND res1.start_date
        )
        ORDER BY res1.start_date DESC
    ) 'end_season_code',
    upper(res.event_code) || ', ' || (
        SELECT group_concat(upper(sub.event_code), ', ')
        FROM (
            SELECT res1.event_code 'event_code'
            FROM results res1 
            WHERE 
                res1.unit_id=res.unit_id
            AND ((SELECT all_seasons FROM vars) OR res1.season_code=res.season_code)
            AND res1.ordinal=1
            AND res1.start_date > res.start_date
            AND 0=(
                SELECT count()
                FROM results res2
                WHERE 
                    res2.unit_id=res.unit_id
                AND res2.ordinal != 1
                AND res2.start_date BETWEEN res.start_date AND res1.start_date
            )
            ORDER BY res1.start_date ASC
        ) sub -- sub query is required here to get event codes in order. sqlite ORDER BY in group_concat doesn't seem to work although the docs say it should
    ) 'event_codes',
    res.division_code 'division_code',
    res.discipline_code 'discipline_code',
    res.start_date 'start_date',
    'win_count' 'sort'
FROM results res
WHERE 
    res.ordinal=1
AND ((SELECT any_time from vars) OR 0=(
    SELECT count()
    FROM results prev
    WHERE 
        prev.unit_id=res.unit_id
    AND prev.season_code=res.season_code
    AND prev.start_date < res.start_date
))

GROUP BY res.unit_id --, res.season_code NOOOOOOOO

ORDER BY win_count DESC, res.start_date ASC

LIMIT :limit
;