MLDZMNcontri1
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
29
30
31
32
33
34
35
36
›
⌄
-- forked from List of top contributors by volume of contributions @ https://flipsidecrypto.xyz/edit/queries/815b74f5-27de-48a0-9f66-c037839fcac7
-- forked from cubuser1 @ https://flipsidecrypto.xyz/edit/queries/baf161c7-4e5c-4329-a365-e568620537c6
-- '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),
t2 as (select
distinct signers[0] as contributor,
count(distinct tx_id) as no_contribution
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')
group by 1
)
select
case
when no_contribution=1 then '1 time'
when no_contribution=2 then '2 times'
when no_contribution> 2 and no_contribution < 5 then '3-5 times'
Run a query to Download Data