This Season's Elements ranked by Mean Raw GOE Mark

goe_rank.sql

WITH

'season_element' AS (
    SELECT
        el.id 'id',
        uf.flag 'nat',
        u.first_name || ' ' || u.last_name 'unit',
        el.name 'element',
        (
            SELECT avg(goe.score) FROM scores_goemark goe
            WHERE goe.element_id=el.id -- LINK
        ) 'mean_goe',
        (
            SELECT count() FROM scores_goemark goe
            WHERE goe.element_id=el.id -- LINK
            AND goe.score=5
        ) 'max',
        (
            SELECT count() FROM scores_goemark goe
            WHERE goe.element_id=el.id -- LINK
        ) 'n',
        (
            ((SELECT count() FROM scores_goemark goe1
            WHERE goe1.element_id=el.id
            AND goe1.score=5)*1.0)
            /
            ((SELECT count() FROM scores_goemark goe2
            WHERE goe2.element_id=el.id)*1.0)
        ) 'ratio',
        printf('%0.2f', el.total_score) 'score',
        upper(evt.code) 'event',
        upper(dis.code) 'discipline',
        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'
    FROM scores_element el

    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=2019
    AND ser.code IN ('jgp', 'cs', 'gp')

    ORDER BY mean_goe DESC, ratio DESC, score DESC

    LIMIT 20
)

SELECT
    COUNT(DISTINCT rank.mean_goe) '#',
    el.nat 'Nat',
    el.unit 'Skater/Team',
    el.element 'Element',
    printf('%.2f', el.mean_goe) 'Mean Raw GOE',
    (el.max || ' of ' || el.n) 'How many +5s?',
    --el.ratio 'Ratio',
    --el.score 'Score',
    el.event 'Event',
    el.seg 'Seg'
FROM season_element el
    
INNER JOIN season_element rank
    ON (el.mean_goe <= rank.mean_goe)
    
GROUP BY el.id

ORDER BY el.mean_goe DESC, el.ratio DESC, el.score DESC
;