select

a.tx_hash,

a.block_timestamp,

c.tx_signer as user,

iff(f.value ilike 'Swapped % usdt.tether-token.near', 'Inflow', 'Outflow') as type,

iff(type = 'Inflow', split(split(f.value, ' for ')[1], ' ')[0], split(split(f.value, 'Swapped ')[1], ' ')[0])/1e6 as amount_usd,

iff(type = 'Inflow', split(split(f.value, ' for')[0], ' ')[2], split(split(f.value, 'for ')[1], ' ')[1]) as token_address

from near.core.fact_receipts a, table(flatten(logs)) f

join near.core.fact_transactions c

where a.tx_hash = c.tx_hash

and f.value ilike any ('Swapped % usdt.tether-token.near', 'Swapped % usdt.tether-token.near for %')

and status_value ilike '%success%'

count(*) as swaps,

count(distinct user) as swappers,

sum(amount_usd) as volume_usd,

avg(amount_usd) as average_volume_usd,

median(amount_usd) as median_volume_usd,

swaps / count(distinct date_trunc('day', block_timestamp::date)) as daily_average_swaps,

swappers / count(distinct date_trunc('day', block_timestamp::date)) as daily_average_swappers

from t

where block_timestamp >= current_date - 7