with prices as (
select
date_trunc('hour', recorded_at) as p_date,
symbol,
avg(price) as price_usd
from osmosis.core.dim_prices
where recorded_at::date BETWEEN '2022-10-26' and '2022-10-31'
group by 1,2
)
, tx as (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) as date,
TX_ID,
TRADER,
b.PROJECT_NAME as symbolIn,
c.PROJECT_NAME as symbolOut,
FROM_AMOUNT/pow(10, FROM_DECIMAL)*d.price_usd as from_amt,
TO_AMOUNT/pow(10, TO_DECIMAL)*e.price_usd as to_amt
FROM osmosis.core.fact_swaps a
join osmosis.core.dim_labels b on a.FROM_CURRENCY = b.Address --PROJECT_NAME
join osmosis.core.dim_labels c on a.TO_CURRENCY = c.Address
join prices d on b.PROJECT_NAME = d.symbol and date_trunc('day', a.BLOCK_TIMESTAMP) = d.p_date
join prices e on c.PROJECT_NAME = e.symbol and date_trunc('day', a.BLOCK_TIMESTAMP) = e.p_date
WHERE date BETWEEN '2022-10-26' and '2022-10-31'
)
SELECT
date,
symbolIn,
symbolOut,
symbolIn || ' => ' || symbolOut as pair,
COUNT(DISTINCT TX_ID) as tx_cnt,
COUNT(DISTINCT TRADER) as TRADER,
sum(from_amt) as swap_from_amt,
sum(to_amt) as swap_to_amt,
ROW_NUMBER() OVER (partition by date order by swap_to_amt desc) rank
from tx