utils.udf_hex_to_int(substr(substr(data, 0,66),3,66)) as timestamp,

(utils.udf_hex_to_int(substr(data,67,64))::int)/1e18 as pot_before,

(utils.udf_hex_to_int(substr(data,131,64))::int)/1e18 as pot_after,

utils.udf_hex_to_int(substr(data,323,64))::int as yeet_count,

utils.udf_hex_to_int(substr(data,387,64))::int as yeet_round,

from berachain.testnet.fact_event_logs

where ORIGIN_TO_ADDRESS=LOWER('0x14D65204c710997F595F4663f0451b66d3532f53')

and ORIGIN_FUNCTION_SIGNATURE='0x8bbefe4b'

and topics[0]='0x67e0e8435cacda8ca6cf3a8f63b166cdb95dc96e7d1c6e068009b117d47e1ac6'

order by block_timestamp),

rounds_data as (select yeet_round,max(pot_after) as pot_per_round,count(distinct address) as yeetards, max(yeet_count) as yeets,

(max(timestamp)-min(timestamp)) as round_duration

from data

group by yeet_round

order by 1 desc)

select yeet_round, pot_per_round, yeetards, yeets, round_duration,

(SELECT

concat(cast((hours / 24) as INTEGER) ,' days')

FROM (

SELECT datediff('hour', date('1970-01-01 00:00:00'), date(round_duration)) AS hours

)) as duration,

sum(pot_per_round) over (order by yeet_round) as cum_yeeted,

sum(yeets) over (order by yeet_round) as cum_yeets

from rounds_data

order by 1 desc