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

and contract_address = '0x70c575588b98c1f46b1382c706adaf398a874e3e'

and topics[0]::string = '0x9b492ca62dd845faf66276dbd14ed82e0d819d8b172cb51433ffe4268ab3dd9e')

SELECT boundaries.season,

count(distinct(case when topics[0]::string = lower('0x2944dc5d451b45dded3cec3b6e39d1b6b9cd06b0af663da7b6761d923f554d55') then topics[2] end)) as active_player,

sum(case when topics[0]::string = lower('0x2944dc5d451b45dded3cec3b6e39d1b6b9cd06b0af663da7b6761d923f554d55') then (ethereum.public.udf_hex_to_int(DATA::string)) end) as kongium_earned,

sum(case when topics[0]::string = lower('0xe01d38f2b826d7fd0bc08b84cbacc482a184e2f3c96f1c42b5f72f8302e1920d') then (ethereum.public.udf_hex_to_int(DATA::string)) end) as kongium_burned,

COALESCE(sum(case when topics[0]::string = lower('0xe01d38f2b826d7fd0bc08b84cbacc482a184e2f3c96f1c42b5f72f8302e1920d') and ORIGIN_FUNCTION_SIGNATURE = lower('0xd2f7265a') then (ethereum.public.udf_hex_to_int(DATA::string)) end),0) as kongium_craft,

sum(case when topics[0]::string = lower('0xf5f311a0329198906d6b8a4b641bb2ae4cfcfa61c2a5d4caf9f9bce29711fd9b') then (ethereum.public.udf_hex_to_int(DATA::string)) end) as kongium_com,

kongium_burned-kongium_craft-kongium_com as kongium_levelup

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 in (lower('0x1421b4337ce370a389cf4e45a6b870487574006e'),lower('0x70c575588b98c1f46b1382c706adaf398a874e3e'))

and season is not null

group by 1

order by 1 desc