bergTop Tokens per day in terms of swap numbers at 2 weeks ago
Updated 2022-12-08Copy Reference Fork
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
33
34
35
›
⌄
with prices as (
select block_timestamp::date as day,
swap_from_mint as mint,
median (swap_to_amount/swap_from_amount) as price_usd
from solana.fact_swaps
where swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDav','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB')
and swap_to_amount > 0
and swap_from_amount > 0
and succeeded = 1
group by 1, 2
)
select
a.block_timestamp::date "Date",
b.symbol "Token",
count(distinct a.tx_id) "Swaps Count",
count(distinct swapper) "Swappers Count",
sum(price_usd * swap_from_amount) "Volume (USD)",
avg(price_usd * swap_from_amount) "Average Volume (USD)",
median(price_usd * swap_from_amount) "Median Amount (USD)",
sum("Swaps Count") over (partition by "Token" order by "Date" asc) as "Comulative Swaps Count",
sum("Swappers Count") over (partition by "Token" order by "Date" asc) as "Comulative Swappers Count",
sum("Volume (USD)") over (partition by "Token" order by "Date" asc) as "Comulative Volume (USD)",
row_number() over (partition by "Date" order by "Swappers Count" desc) as rank
from solana.core.fact_swaps a
join prices c on a.block_timestamp::Date = c.day and a.swap_from_mint = c.mint
join crosschain.core.dim_asset_metadata b
on a.swap_from_mint = b.token_address
where a.block_timestamp >= current_date - interval '2 weeks'
and swap_program = 'raydium v4'
and platform = 'solana'
and a.succeeded = 1
group by 1, 2
qualify rank <= 10
order by 1, rank
Run a query to Download Data