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 > '2024-01-04'

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 > '2024-01-04'

and contract_address= lower('0x7cbccc4a1576d7a05eb6f6286206596bcbee14ac')

and season is not null

group by 1

order by 1 desc