shadiltmp Orca User Growth - solana
Updated 2022-05-21
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 sol as (
select
date(block_timestamp) as date,
avg(swap_to_amount) / avg(swap_from_amount) as sol_price
from solana.fact_swaps
where swap_from_mint = 'So11111111111111111111111111111111111111112'
and swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
and swap_to_amount > 0
and swap_from_amount > 0
and date(block_timestamp) >= '2022-01-01'
group by 1
order by 1 asc
),
orca as (
Select sol.date as date,
sum(case when s.swap_from_mint='So11111111111111111111111111111111111111112' then s.swap_from_amount else s.swap_to_amount END ) * sol.sol_price as sol_volume,
avg(case when s.swap_from_mint='So11111111111111111111111111111111111111112' then s.swap_from_amount else s.swap_to_amount END ) * sol.sol_price as avg_sol_volume,
COUNT(DISTINCT swapper) as swappers_count,
COUNT(DISTINCT tx_id) as swaps_count,
'orca' as dex
From flipside_prod_db.solana.fact_swaps s
join sol on sol.date = s.block_timestamp::date
Where block_timestamp::date >= '2022-01-01'
And succeeded = 'True'
and swap_program = 'orca'
And ( (swap_from_mint = 'So11111111111111111111111111111111111111112' and swap_to_mint != 'So11111111111111111111111111111111111111112') or
(swap_to_mint = 'So11111111111111111111111111111111111111112' and swap_from_mint != 'So11111111111111111111111111111111111111112')
)
And swap_from_amount != 0 AND swap_to_amount != 0
Group by sol.date, sol.sol_price
Order by sol.date, sol_volume DESC
),
jupiter as (
Select sol.date as date,
sum(case when s.swap_from_mint='So11111111111111111111111111111111111111112' then s.swap_from_amount else s.swap_to_amount END ) * sol.sol_price as sol_volume,
avg(case when s.swap_from_mint='So11111111111111111111111111111111111111112' then s.swap_from_amount else s.swap_to_amount END ) * sol.sol_price as avg_sol_volume,
Run a query to Download Data