azerbaijanCopy of Copy of Copy of Untitled Query
Updated 2022-11-20
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
›
⌄
⌄
WITH prices as (
select
date_trunc('day', BLOCK_TIMESTAMP) as dates,
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')
and swap_to_amount > 0
and swap_from_amount > 0
and date(block_timestamp) >= '2022-11-01'
group by 1,2
order by 1 asc
)
select date_trunc('day', a.BLOCK_TIMESTAMP) as date,
b.label as name,
sum(amount * asset_price) as amount_usd, count(DISTINCT tx_id) tx_count,
'TO CEXs' as type
from solana.core.fact_transfers a join solana.core.dim_labels b on a.tx_to = b.address and b.label_type = 'cex'
join prices d on a.mint = d.asset and a.BLOCK_TIMESTAMP::date = dates
where BLOCK_TIMESTAMP::date BETWEEN '2022-11-01' and CURRENT_DATE - 1
GROUP by 1,2
/*UNION ALL
select date_trunc('day', a.BLOCK_TIMESTAMP) as date,
--c.label as name,
sum(amount * asset_price) as amount_usd,
count(DISTINCT tx_id) as tx_count,
'From CEXs' as type
from solana.core.fact_transfers a join solana.core.dim_labels c on a.tx_from = c.address and c.label_type = 'cex'
join prices d on a.mint = d.asset and a.BLOCK_TIMESTAMP::date= dates
where BLOCK_TIMESTAMP::date BETWEEN '2022-11-01' and CURRENT_DATE - 1
GROUP by 1*/
Run a query to Download Data