MLDZMNcubuser1
Updated 2023-07-26
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
›
⌄
-- '2023-06-23' to '2023-07-03'
with t1 as (select
RECORDED_HOUR as hour,
avg(close) as price_sol
from solana.core.fact_token_prices_hourly
where SYMBOL ilike 'sol'
group by 1)
select
count(distinct tx_id) as no_contribution,
count(distinct signers[0]) as no_user,
sum(transfer.amount) as total_volume,
avg(transfer.amount) as avg_volume,
sum(transfer.amount*price_sol) as volume_usd
from solana.core.fact_transactions s
join (select distinct block_timestamp, tx_id
from solana.core.fact_events where succeeded = True
and program_id = 'BVo5TquTYMAASZhfX392BcjFUxda6DKzHStNapJE6Wyz') events
using(tx_id, block_timestamp)
join lateral flatten (input => log_messages) logs
join (select tx_id, block_timestamp,amount
from solana.core.fact_transfers where mint='So11111111111111111111111111111111111111112') transfer
using(tx_id, block_timestamp)
left join t1 on date_trunc('hour',s.block_timestamp)=t1.hour
where BLOCK_TIMESTAMP>='2023-06-15' --between '2023-06-23' and '2023-07-03'
and logs.value in ('Program log: Instruction: CreateContributionSol','Program log: Instruction: UpdateContributionSol')
limit 5
Run a query to Download Data