block_timestamp,

tx_hash,

from_address as bet_wallet,

regexp_substr_all(substr(input_data, 11, len(input_data)), '.{64}') as data,

concat('0x', substr(data[0], 25, 40)) as game_address,

case

when ethereum.public.udf_hex_to_int(data[1]::string)::float = 0 then 'home'

when ethereum.public.udf_hex_to_int(data[1]::string)::float = 1 then 'away'

when ethereum.public.udf_hex_to_int(data[1]::string)::float = 2 then 'draw'

end as position,

ethereum.public.udf_hex_to_int(data[2]::string)::float/pow(10,18) as amount,

ethereum.public.udf_hex_to_int(data[3]::string)::float/pow(10,18) as bet_vol,

case when origin_function_signature in ('0x6cc5a6ff', '0xec933f83') then concat('0x', substr(data[5], 25, 40))

else lower('0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9') end as bet_token_address,

case when origin_function_signature = '0x3ce1108d' then 'withdraw' else 'bet' end as type

from optimism.core.fact_transactions

where to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and block_timestamp::date < current_date and block_timestamp::date >= current_date-14

and origin_function_signature in ('0x8875eb84', '0x5169181f', '0x6cc5a6ff', '0x9f916c9f', '0xec933f83', '0x3ce1108d')

and status = 'SUCCESS'

)

select count(distinct tx_hash) as tx_count,

count(distinct bet_wallet) as unique_better,

sum(bet_vol) as bet_vol

from total where type = 'bet'