BlockTrackerphoenix (transfers) based on tokens
Updated 2023-09-03
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 phoenix_tx as (
SELECT
tx_id as tx
FROM solana.core.fact_events
WHERE program_id = 'PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY'
AND block_timestamp::date >= '2023-08-23')
,
tokens_price as (
SELECT
date_trunc('d',RECORDED_HOUR) as date,
a.symbol,
token_address,
median(close) as usd_price
FROM solana.price.fact_token_prices_hourly a
INNER JOIN solana.core.dim_tokens b ON a.id = b.COIN_MARKET_CAP_ID
WHERE provider = 'coinmarketcap'
AND date >= '2023-08-23'
GROUP BY 1 , 2 , 3
)
SELECT
date_trunc('d', block_timestamp) as date,
symbol,
count(DISTINCT a.tx_id) as n_transfer,
sum(amount*usd_price) as value,
count(DISTINCT tx_from) as sender,
count(DISTINCT tx_to) as reciever
FROM solana.core.fact_transfers a
INNER JOIN phoenix_tx b ON a.tx_id = b.tx
LEFT JOIN tokens_price c ON date_trunc('d', block_timestamp) = c.date AND a.mint = c.token_address
WHERE block_timestamp::date >= '2023-08-23'
GROUP BY 1 , 2
ORDER BY 1 DESC
Run a query to Download Data