with final_table as (

with tab1 as (

select date_trunc('hour',created_at) as date, id, send_amount as amount, source_chain, destination_chain, sender_address as user

from axelar.axelscan.fact_transfers

where (created_at::date between '2023-01-01' and '2025-01-01') and link is not null and send_amount_received is not null and simplified_status='received'

and send_fee is not null and destination_chain is not null and send_denom='factory/sei10hud5e5er4aul2l7sp2u9qp2lag5u4xf8mvyx38cnjvqhlgsrcls5qn5ke/seilor'),

tab2 as (select hour as date, avg(price) as avg_price

from crosschain.price.ez_prices_hourly

where hour::date between '2023-01-01' and '2025-01-01' and token_address='sei10knxlv9e54z0xcywdhtycc63uf970aefeec73ycqwd75ep9nu45sh66m6a'

group by 1)

select tab1.date as date, amount, amount*avg_price as amount_usd , to_varchar(source_chain) as source_chain,

to_varchar(destination_chain) as destination_chain, to_varchar(id) as id, to_varchar(user) as user

from tab1 left join tab2 on tab1.date=tab2.date)

select source_chain || '➡' || destination_chain as "Path", round(sum(amount)) as "Transfers Volume ($SEILOR)", round(sum(amount_usd)) as "Transfers Volume ($USD)",

count(distinct id) as "Transfers Count", count(distinct user) as "Users Count", round(avg(amount)) as "Avg Volume($SEILOR)", round(avg(amount_USD)) as "Avg Volume($USD)"

from final_table

group by 1

order by 1