mondovreserve pool stats
Updated 2023-08-30
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
›
⌄
with price as (
SELECT
date_trunc('day', recorded_hour) as day,
avg(close) as token_price
FROM solana.core.ez_token_prices_hourly
WHERE token_address = 'So11111111111111111111111111111111111111112'
group by day
)
select
count(distinct tx_id) as transactions,
count(distinct signers[0]) as users,
-- no_users/count(distinct BLOCK_TIMESTAMP::date) as user_per_day,
sum(i.value:parsed:info:lamports/1e9) as volume_sol,
sum(i.value:parsed:info:lamports/1e9*token_price) as volume_usd
-- avg(b.value:parsed:info:lamports/1e9*token_price) as avg_volume_usd,
-- volume_usd/count(distinct BLOCK_TIMESTAMP::date) as volume_per_day
from solana.core.fact_events s
join lateral flatten (input => inner_instruction:instructions) i
join lateral flatten (input => instruction:accounts) a
left join price p on date_trunc('day', s.BLOCK_TIMESTAMP)=p.day
where program_id = 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4'
and i.value:parsed:info:source = '3rBnnH9TTgd3xwu48rnzGsaQkSr1hR64nY71DrDt6VrQ'
and a.value ilike '3rBnnH9TTgd3xwu48rnzGsaQkSr1hR64nY71DrDt6VrQ' -- Sanctum SOL Reserve Pool
-- or a.value ilike '5Pcu8WeQa3VbBz2vdBT49Rj4gbS4hsnfzuL1LmuRaKFY')
-- and BLOCK_TIMESTAMP>=current_date-{{Days_back}}
Run a query to Download Data