MLDZMNsolt3
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
36
›
⌄
with tb1 as (select
RECORDED_HOUR::date as day,
TOKEN_ADDRESS,
avg (close) as price_token
from solana.core.ez_token_prices_hourly
where day>= '2023-05-15'
group by 1,2)
select
BLOCK_TIMESTAMP::date as day,
'Sell SOL' as swap_type,
count(distinct tx_id) as no_swaps,
count(distinct swapper) as no_traders,
sum(price_token*SWAP_FROM_AMOUNT) as amount_usd,
avg(price_token*SWAP_FROM_AMOUNT) as avg_usd,
median(price_token*SWAP_FROM_AMOUNT) as median_usd
from solana.core.fact_swaps s
left join tb1 b on s.BLOCK_TIMESTAMP::date=b.day and s.SWAP_from_MINT=b.TOKEN_ADDRESS
where block_timestamp>='2023-06-05'
and SUCCEEDED = 'TRUE'
and (s.SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112')
group by 1
union all
select
BLOCK_TIMESTAMP::date as day,
'Buy SOL' as swap_type,
count(distinct tx_id) as no_swaps,
count(distinct swapper) as no_traders,
sum(price_token*SWAP_TO_AMOUNT) as amount_usd,
avg(price_token*SWAP_TO_AMOUNT) as avg_usd,
median(price_token*SWAP_TO_AMOUNT) as median_usd
from solana.core.fact_swaps s
left join tb1 b on s.BLOCK_TIMESTAMP::date=b.day and s.SWAP_TO_MINT=b.TOKEN_ADDRESS
where block_timestamp>='2023-06-05'
and SUCCEEDED = 'TRUE'
and s.SWAP_TO_MINT = 'So11111111111111111111111111111111111111112'
Run a query to Download Data