flipside

    Scroll_Ranks
    eferScroll_Ranks
    Updated 8 hours ago
    WITH user AS (
    SELECT
    COALESCE(SUM(value:"points"), 0) AS "Point"
    FROM (
    SELECT livequery.live.udf_api(
    CONCAT(
    'https://kx58j6x5me.execute-api.us-east-1.amazonaws.com/scroll/wallet-points?walletAddress=',
    LOWER('{{Address}}')
    )
    ) AS response
    ), lateral FLATTEN (input => response:data)
    )

    ,

    ranks AS (
    SELECT
    value:"Rank" AS "Rank",
    value:"Category" AS "Category",
    value:"Average Marks" AS "Average Marks",
    value:"Average Marks"::numeric AS "Average",
    FROM (
    SELECT livequery.live.udf_api(
    'https://raw.githubusercontent.com/eferbarn/scroll-marks-sampler/main/assets/Ranks.json'
    ) AS response
    ), lateral FLATTEN (input => response:data:data:rows)
    ORDER BY 4 DESC
    ),

    closest_rank AS (
    SELECT
    r."Rank",
    r."Category",
    r."Average Marks",
    ABS(u."Point" - r."Average Marks") AS "Difference",
    ROW_NUMBER() OVER (ORDER BY ABS(u."Point" - r."Average Marks")) AS "RowNum"
    Last run: about 8 hours agoAuto-refreshes every 24 hours
    Rank
    Category
    Average Marks
    Position
    1
    000001 - 019996
    Top 10%
    		2395.047
    2
    019997 - 039992
    Top 20%
    		263.926📍 You are here(~717.99)
    3
    039993 - 059988
    Top 30%
    		156.716
    4
    059989 - 079984
    Top 40%
    		93.77
    5
    079985 - 099980
    Top 50%
    		47.205
    6
    099981 - 119976
    Top 60%
    		13.789
    7
    119977 - 139972
    Top 70%
    		2.576
    8
    139973 - 159968
    Top 80%
    		0.62
    9
    159969 - 179964
    Top 90%
    		0.067
    10
    179965 - 199960
    Top 100%
    		0
    10
    440B
    3s