shadilOrca User Growth - solana and uniswap
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 prices as (
select
date(block_timestamp) as date,
swap_from_mint as asset,
avg(swap_to_amount) / avg(swap_from_amount) as asset_price
from solana.fact_swaps
where (swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' or swap_to_mint = 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') -- USDC and USDT
and swap_to_amount > 0
and swap_from_amount > 0
and date(block_timestamp) >= '2022-01-01'
group by 1,2
order by 1 asc
),
orca as (
Select date,
sum(asset_volume) as usd_volume,
avg(asset_volume) as avg_usd_volume,
COUNT(DISTINCT swapper) as swappers_count,
COUNT(DISTINCT tx_id) as swaps_count,
'orca' as dex
from (
Select prices.date as date,
tx_id, swapper, swap_from_amount,
s.swap_from_amount * prices.asset_price as asset_volume
From flipside_prod_db.solana.fact_swaps s
join prices on prices.date = s.block_timestamp::date and prices.asset = swap_from_mint
Where block_timestamp::date >= '2022-01-01'
And succeeded = 'True'
and swap_program = 'orca'
And swap_from_amount != 0 AND swap_to_amount != 0
)
GROUP BY date
),
jupiter as (
Select date,
sum(asset_volume) as usd_volume,
Run a query to Download Data