Eman-RazTOP 5 Routes By Volume
Updated 2024-07-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
›
⌄
with final_tab as (with tab1 as (select date_trunc('day',created_at) as "Date", sum(send_amount) as "Transfers Volume ($KUJI)", count(distinct id) as "Transfers Count",
source_chain || '➡' || destination_chain as "Route"
from axelar.axelscan.fact_transfers
where send_denom='ukuji' and destination_chain is not null
and id<>'65a945b4d6e194d647cb1914bf3f26488fb4d188ee59b68953c15d108f07f64d_kujira'
and id<>'340f3e7a00ec8ec7263284840606dc299df10927874ebc67f2b452dd99ccd65f_kujira'
and id<>'6bee3a6dde53c69f64e080a60b63011744fd1f05ad7152f58c9c568391fe3db8_kujira'
and id<>'51b164eb95e68963ec49b1f12d16a06bdfe1a7b1608ce0fbee30250f40c6dda9_kujira'
and id<>'afabd4b339d0871f8fbb95cdb32957323ef608a7f2745af00799ce452bab087a_kujira'
group by 1,4
order by 1),
tab2 as (select hour::date as "Date", avg(price) as "Avg Price"
from crosschain.price.ez_prices_hourly
where token_address=lower('0x96543ef8d2C75C26387c1a319ae69c0BEE6f3fe7')
group by 1)
select date_trunc('day',tab1."Date") as "Date", "Transfers Volume ($KUJI)", "Transfers Volume ($KUJI)"*"Avg Price" as "Transfers Volume ($USD)",
"Transfers Count", "Route"
from tab1 left join tab2 on tab1."Date"=tab2."Date"
where tab1."Date" between '{{Start_Date}}' and '{{End_Date}}'
order by 1)
select "Route", round(sum("Transfers Volume ($KUJI)")) as "Total Transfers Volume ($KUJI)", round(sum("Transfers Volume ($USD)")) as "Total Transfers Volume ($USD)",
sum("Transfers Count") as "Total Transfers Count"
from final_tab
WHERE "Transfers Volume ($USD)" IS NOT NULL
group by 1
order by 2 desc
limit 5
Auto-refreshes every 24 hours
QueryRunArchived: QueryRun has been archived