Snowflakeでaverage rankを出す方法

pandasだとデフォルトのやつだけど、snowflakeだとできなさそうだったので。他のDBでも同様にできるでしょう。

corn_production に average rank rk をつける。

WITH corn_production AS (
    SELECT
        1 AS farmer_ID,
        100 AS bushels
    UNION ALL
    SELECT
        2 AS farmer_ID,
        110 AS bushels
    UNION ALL
    SELECT
        3 AS farmer_ID,
        110 AS bushels
    UNION ALL
    SELECT
        4 AS farmer_ID,
        120 AS bushels
),

tmp AS (
    SELECT
        farmer_ID,
        bushels,
        COUNT(*) OVER () AS cnt
    FROM
        corn_production
),

tmp2 AS (
    SELECT
        farmer_ID,
        bushels,
        RANK() OVER (ORDER BY bushels DESC) AS r,
        1 + cnt - RANK() OVER (ORDER BY bushels ASC) AS r2
    FROM
        tmp
)

SELECT
    farmer_ID,
    bushels,
    (r + r2) / 2 AS rk
FROM
    tmp2
ORDER BY
    rk
;