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,

avg(boundaries.prize) as pool_start,

sum(ftt.raw_amount/pow(10,18))*0.25 as Banana_CK,

sum(ftt.raw_amount/pow(10,18)) as Banana_burnt,

sum(ftt.raw_amount/pow(10,18))*1.25 as Banana_pool,

avg(boundaries.prize)+(sum(ftt.raw_amount/pow(10,18))*1.25) as final_prize

FROM polygon.core.fact_token_transfers 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('0xbC91347e80886453F3f8bBd6d7aC07C122D87735')

AND to_address = lower('0x000000000000000000000000000000000000dead')

and season IS NOT NULL

group by 1

order by 1 desc