pricet as (

select

hour::date as date,

avg(price) as price_usd

from kaia.price.ez_prices_hourly

where SYMBOL = 'KLAY'

group by 1

),

main as (

select

tx_hash,

block_timestamp,

origin_from_address as user,

abs(ethereum.public.udf_hex_to_int('s2c', regexp_substr_all(substr(data, 3, len(data)), '.{64}')[0]::string)::bigint) / 1e18 as amount_usd

from kaia.core.fact_event_logs

left join pricet on block_timestamp::date = date

where contract_address = '0xb64ba987ed3bd9808dbcc19ee3c2a3c79a977e66'

and topics[0] = '0x19b47279256b2a23a1665c810c8d55a1758940ee09377d4f8d26497a3577dc83'

and tx_succeeded = 1

),

overtime as (

date_trunc('{{ period }}', block_timestamp) as date,

count(distinct tx_hash) as swaps,

count(distinct user) as users,

sum(amount_usd) as volume_usd,

avg(amount_usd) as average_amount_usd

from main

group by 1

),

new_users as (