(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,

ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(INPUT, 11, len(INPUT)), '.{64}')[1]) as fuelrod,

count(case when SUBSTR(INPUT, 1, 10) = lower('0x11237ba0') then TX_HASH end) as stx,

count(case when SUBSTR(INPUT, 1, 10) = lower('0x11237ba0') then TX_HASH end) as srun,

count(case when SUBSTR(INPUT, 1, 10) = lower('0xd422b242') then TX_HASH end) as btx,

coalesce(sum(case when SUBSTR(INPUT, 1, 10) = lower('0xd422b242') then ethereum.public.udf_hex_to_int(regexp_substr_all(SUBSTR(INPUT, 11, len(INPUT)), '.{64}')[2]) end),0) as brun,

coalesce(srun,0)+coalesce(brun,0) as tot_run

FROM polygon.core.fact_traces 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 tx_status = 'SUCCESS'

and to_address = lower('0x70c575588b98c1f46b1382c706adaf398a874e3e')

and SUBSTR(INPUT, 1, 10) in (lower('0x11237ba0'),lower('0xd422b242'))

and season IS NOT NULL

group by 1,2