flipside

    Hold & Stake Point
    freemartianHold & Stake Point
    Updated 2025-02-08
    WITH hourly_series AS (
    SELECT
    DATE_TRUNC('hour', DATEADD(hour, SEQ4(), '2024-12-28 02:00:00'::timestamp)) AS hour
    FROM
    TABLE(GENERATOR(ROWCOUNT => 100000))
    WHERE
    DATE_TRUNC('hour', DATEADD(hour, SEQ4(), '2024-12-28 02:00:00'::timestamp)) <= CURRENT_TIMESTAMP
    ),

    prices AS(
    SELECT hour AS price_hour, symbol, price
    FROM base.price.ez_prices_hourly
    WHERE token_address = lower('0x0521AaA7C96E25afeE79FDd4f1Bb48F008aE4eac')
    AND hour::date >= '2024-12-28'
    ORDER BY 1
    ),
    users AS (
    -- Extract unique users from transfers to ensure every user has an entry for each hour
    SELECT DISTINCT to_address AS user
    FROM base.core.ez_token_transfers
    WHERE contract_address = lower('0x0521AaA7C96E25afeE79FDd4f1Bb48F008aE4eac')
    -- AND from_address NOT IN (SELECT address FROM ethereum.core.dim_contracts)
    -- AND to_address NOT IN (SELECT address FROM ethereum.core.dim_contracts)
    AND user <> '0x0000000000000000000000000000000000000000'
    -- AND user IN ('0xf2614a233c7c3e7f08b1f887ba133a13f1eb2c55')
    -- AND user IN ('0x662e3363966b8111bff3b2b539adb4533809e026')
    -- AND user IN ('0x7699c613be2ec32984e153dbc27c929fd159caab','0x66ec04035f2f14bbbfe744a8a56ca02fd21582ff')
    -- AND user in ('0xed6ac3a92e4a77b0553f2a243afc51b3b865d3e1')
    ),

    hourly_users AS (
    -- Create all combinations of users and hours
    SELECT hour, user
    FROM hourly_series
    CROSS JOIN users
    ),
    Last run: 3 months ago
    USER_ADDRESS
    TOTAL_HOLD_POINTS
    TOTAL_STAKE_POINTS
    TOTAL_POINTS
    RANK
    1
    		0xfff5d872f617443449f4b40f54e7a04f99755cf9136524452998257543635011
    2
    		0xd9e391abe558d10f7b9e5b99f3dd61cc379217821221731440992126532094402
    3
    		0x2c7d6fc3d0a6ef2e3bcb876d18efc013397feb56168963746314136480037733
    4
    		0x733246bcee1d39f3cae699a1f1cffc97d67d2e57339512142332501457276224
    5
    		0xa55a9a19f5f5e18cbb6b5892d1659956663a1f35171348032373373340868535
    6
    		0x34cae1d9e2d014b7b9e6295c66c554d7e79713d3277512310277512316
    7
    		0x2573e5efe8d28d6bacdbbab396ebd8ecf0ac0dd66854526989208270577537
    8
    		0xb167ccfded57d7cc05728268352f59387a28de69270123560270123568
    9
    		0x5aceb63d52d0d7081a72f254401893ffcf81381a024116007241160079
    10
    		0xed206d089f791a1411228bf0daf9621662af340c860040220787702293881010
    11
    		0xeaa400abec7c62d315fd760cbba817fa35e4e0e8787089221362862292337511
    12
    		0x1af64d49346290f351d81163606398715ab47e0e2286381202286381212
    13
    		0xfa6668faa6e9511b36403a6628b036c7fd70166d2948994199148182286381212
    14
    		0x7b14ce5a0afc89adf25de76b95e3ecf87f0372f3949821219139912286381212
    15
    		0x288e2ffe595909746572da81a182615d759503b51065800217980122286381212
    16
    		0x9f2c18b17cbb539f77997ad800846dd64cee457b2948994199148182286381212
    17
    		0xf0bd95398c014b3f382511f4d4379245863bc8af753501221103112286381212
    18
    		0xe4d7b8c5dd94de9a09fdb174e021f51995062d471858922542745872286381212
    19
    		0x12c1c7e9892ef6e2e1dba4c3851dbeb8cf0166381552791671255472265346319
    20
    		0xc223d7a7ba36681a217ccd5bee83b3f4c94f80732211486002211486020
    ...
    34488
    2MB
    36s