select

date_trunc('week', block_timestamp) as day,

*

from

osmosis.core.fact_swaps

), token_prices as (

select

date_trunc('week', recorded_at) as day,

address,

symbol,

avg(price) as token_price_usd

from

osmosis.core.dim_prices a join osmosis.core.dim_labels b on a.symbol = b.project_name

group by

1, 2, 3

select

s.day,

p.symbol,

count(distinct s.tx_id) as transactions_count,

count(distinct trader) as uses_count,

sum(from_amount/pow(10,from_decimal)) as "Volume",

avg("Volume") over(order by s.day asc rows between 7 preceding and current row) as "Volume Moving Average",

sum(from_amount/pow(10,from_decimal)*token_price_usd) as "Volume (USD)",

avg("Volume (USD)") over(order by s.day asc rows between 7 preceding and current row) as "Volume (USD) Moving Average"

from

swaps s

join token_prices p on s.from_currency=p.address and s.day=p.day

where tx_status='SUCCEEDED'

group by s.day, p.symbol

order by s.day, p.symbol