Consistency.sql

consistency.sql

WITH

'key' AS (
    SELECT 
        'men' 'dis_code',
        2019 'season_code'
),

'season_jump' AS (
    SELECT
        el.id 'id',
        u.id 'unit_id',
        uf.flag 'nat',
        CASE WHEN (dis.code='pairs' OR dis.code='dance') THEN
            SUBSTR(u.first_name, INSTR(u.first_name, ' ')+1)
            || '/' || 
            SUBSTR(u.last_name, INSTR(u.last_name, ' ')+1)
        ELSE
            u.first_name || ' ' || u.last_name 
        END 'unit',
        el.code 'code',
        el.goe_total 'goe_total',
        el.total_score 'total_score',
        upper(evt.code) 'event',
        evt.start_date 'date',
        CASE WHEN dis.code='dance' THEN
            CASE WHEN seg.code='short' THEN 'RD' ELSE 'FD' END
        ELSE
            CASE WHEN seg.code='short' THEN 'SP' ELSE 'FS' END
        END 'seg',
        evt.season_code 'season_code'
    FROM scores_element el

    INNER JOIN scores_elementtype et
        ON el.element_type_id=et.id

    INNER JOIN scores_performance perf
        ON el.performance_id=perf.id
    INNER JOIN scores_segment seg
        ON perf.segment_id=seg.id
    INNER JOIN scores_event evt
        ON perf.event_id=evt.id
    INNER JOIN scores_series ser
        ON evt.series_id=ser.id

    INNER JOIN scores_unit u
        ON perf.unit_id=u.id
    INNER JOIN scores_discipline dis
        ON u.discipline_id=dis.id
    INNER JOIN scores_flag uf
        ON u.country_code=uf.country_code

    WHERE
        evt.season_code=(SELECT k.season_code FROM key k)
    AND ser.code IN ('jgp', 'cs', 'gp', 'champ', 'owg')
    AND dis.code=(SELECT k.dis_code FROM key k)
    AND et.code='jump'
    --AND el.is_combination=1

    ORDER BY el.total_score DESC

    --LIMIT 20
),

'season_data' AS (
    SELECT
        uf.flag 'nat',
        u.first_name || ' ' || u.last_name 'unit',
        (
            (1.0*(SELECT count() FROM season_jump j1 WHERE j1.unit_id=u.id AND j1.goe_total > 0.0))
            /
            (1.0*(SELECT count() FROM season_jump j2 WHERE j2.unit_id=u.id))
        ) 'ratio',
        (
            SELECT group_concat(evt, ',') FROM
            (SELECT j.event 'evt' FROM season_jump j WHERE j.unit_id=u.id GROUP BY j.unit_id,j.event ORDER BY j.date)
        ) 'evt',
        (
            SELECT count() FROM season_jump j WHERE j.unit_id=u.id AND j.goe_total > 0.0
        ) 'top',
        (
            SELECT count() FROM season_jump j WHERE j.unit_id=u.id
        ) 'bot'
    FROM scores_unit u

    INNER JOIN scores_discipline dis
        ON u.discipline_id=dis.id
    INNER JOIN scores_flag uf
        ON u.country_code=uf.country_code

    WHERE
        dis.code=(SELECT k.dis_code FROM key k)
    AND bot >= 30

    ORDER BY ratio DESC, top DESC, bot DESC
)

SELECT 
    (SELECT count()+1 FROM season_data rank WHERE rank.ratio > d.ratio) '#',
    d.nat 'Nat',
    d.unit 'Skater',
    printf('%d%%', round(d.ratio*100.0)) '% Pos',
    printf('%d of %d', d.top, d.bot) '# Pos',
    d.evt 'Events'
FROM season_data d
;