Updated 2024-02-18
    with boundaries as

    (select
    ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0])::integer as season,
    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 BETWEEN '2022-03-14 16:40:20.000' and '2024-01-06 06:52:00.000'
    and contract_address = '0x70c575588b98c1f46b1382c706adaf398a874e3e'
    and topics[0]::string = '0x9b492ca62dd845faf66276dbd14ed82e0d819d8b172cb51433ffe4268ab3dd9e')


    SELECT boundaries.season,
    COALESCE(sum(case when topics[2]= lower('0x0000000000000000000000000000000000000000000000000000000000000000') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 1 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Cyber_Fragment,
    COALESCE(sum(case when topics[2]= lower('0x0000000000000000000000000000000000000000000000000000000000000000') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 2 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Rainbow_Crystal,
    COALESCE(sum(case when topics[2]= lower('0x0000000000000000000000000000000000000000000000000000000000000000') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 3 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Promethean_Relic,
    CYBER_FRAGMENT+RAINBOW_CRYSTAL+PROMETHEAN_RELIC as charmz_claimed,
    COALESCE(sum(case when topics[3]= lower('0x000000000000000000000000000000000000000000000000000000000000dead') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 1 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Cyber_Fragmentb,
    COALESCE(sum(case when topics[3]= lower('0x000000000000000000000000000000000000000000000000000000000000dead') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 2 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Rainbow_Crystalb,
    COALESCE(sum(case when topics[3]= lower('0x000000000000000000000000000000000000000000000000000000000000dead') and ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[0]) = 3 then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}')[1]) end),0) as Promethean_Relicb,
    CYBER_FRAGMENTB+RAINBOW_CRYSTALB+PROMETHEAN_RELICB as charmz_burnt
    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 BETWEEN '2022-03-14 16:40:20.000' and '2024-01-06 06:52:00.000'
    and contract_address= lower('0x7cbccc4a1576d7a05eb6f6286206596bcbee14ac')
    and season is not null
    group by 1
    order by 1 desc
    Last run: 12 months ago
    SEASON
    CYBER_FRAGMENT
    RAINBOW_CRYSTAL
    PROMETHEAN_RELIC
    CHARMZ_CLAIMED
    CYBER_FRAGMENTB
    RAINBOW_CRYSTALB
    PROMETHEAN_RELICB
    CHARMZ_BURNT
    1
    65111314146234229
    2
    6415395521121022001051306
    3
    6312214021416372141032319
    4
    62107332591407177910268
    5
    611026331191376181561238
    6
    6065221515882173740247
    7
    5910543361514052111271339
    8
    58142845015189321511313341
    9
    574691685642135593197
    10
    566001898797180631244
    11
    5554014910699238450283
    12
    5450015510665205550260
    13
    5350915186682341041339
    14
    5249514256423341498491
    15
    51869274911524413177765
    16
    501283416151714790316131119
    17
    49137342319181566131110982
    18
    4832261017444287766295101071
    19
    471012329171358998323111332
    20
    46996345171358795248101053
    65
    2KB
    176s