with tb1 as (select

RECORDED_HOUR::date as day,

CURRENCY,

avg (price) as price_token

from osmosis.price.ez_prices

group by 1,2),

tb2 as (SELECT

date_trunc('day',block_timestamp) as day,

count(distinct tx_id) as "Count of Swaps",

count(distinct TRADER) as "Count of Swappers",

sum(price_token*TO_AMOUNT/pow(10,t.DECIMAL)) as "Trading volume (USD)",

avg(price_token*TO_AMOUNT/pow(10,t.DECIMAL)) as "Avg trading volume (USD)"

from osmosis.defi.fact_swaps s

left join tb1 b on s.BLOCK_TIMESTAMP::date=b.day and s.TO_CURRENCY=b.CURRENCY

left join osmosis.core.dim_tokens t on s.TO_CURRENCY=t.address

where block_timestamp >= '2023-01-01'

and block_timestamp < '2024-03-16'

and TX_SUCCEEDED = 'TRUE'

and t.decimal is not NULL

and t.decimal>0

group by 1),

tb3 as (SELECT

BLOCK_DATE as day,

sum(FEES*price_token) as "Trading fee (USD)",

sum("Trading fee (USD)") over (order by BLOCK_DATE) as "Cumulative trading fee (USD)"