banterlyticskongium-com-recent
    Updated 2024-03-07
    -- forked from kongium-com @ https://flipsidecrypto.xyz/edit/queries/781a7240-f43b-4498-bc75-ecf7b16903f7

    with boundaries as
    (select
    ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0])::integer as season,
    ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1])/((pow(10,18))) as prize,
    block_timestamp,
    COALESCE(LEAD(block_timestamp) OVER (ORDER BY block_timestamp),to_timestamp(100000000000)) as next_timestamp
    from polygon.core.fact_event_logs
    where block_timestamp::date > '2023-12-15'
    and contract_address = '0x70c575588b98c1f46b1382c706adaf398a874e3e'
    and topics[0]::string = '0x9b492ca62dd845faf66276dbd14ed82e0d819d8b172cb51433ffe4268ab3dd9e')

    SELECT boundaries.season,
    date_trunc('hour', ftt.block_timestamp) as day1,
    COALESCE(sum(case when topics[0]::string = lower('0xf5f311a0329198906d6b8a4b641bb2ae4cfcfa61c2a5d4caf9f9bce29711fd9b') then (ethereum.public.udf_hex_to_int(DATA::string)) end),0) as kongium_com
    FROM polygon.core.fact_event_logs ftt
    LEFT OUTER JOIN boundaries ON
    ftt.block_timestamp >= boundaries.block_timestamp AND
    ftt.block_timestamp < boundaries.next_timestamp
    WHERE ftt.block_timestamp::date > '2023-12-15'
    and contract_address in (lower('0x1421b4337ce370a389cf4e45a6b870487574006e'),lower('0x70c575588b98c1f46b1382c706adaf398a874e3e'))
    and season is not null
    group by 1,2
    order by 1 asc, 2 asc


    QueryRunArchived: QueryRun has been archived