MLDZMNhpf8
Updated 2023-03-23
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 t1 as (select
RECORDED_HOUR::date as day,
avg(close) as price
from solana.core.fact_token_prices_hourly
where symbol ='SOL'
group by 1),
t2 as (select
distinct INNER_INSTRUCTION:instructions[0]:parsed:info:destination as collected_wallets,
sum(INNER_INSTRUCTION:instructions[0]:parsed:info:lamports/1e9) as volume_sol,
sum((INNER_INSTRUCTION:instructions[0]:parsed:info:lamports/1e9)*price) as volume_usd
from solana.core.fact_events s left join t1 a on s.BLOCK_TIMESTAMP::date=a.day
where PROGRAM_ID='3VtjHnDuDD1QreJiYNziDsdkeALMT6b2F9j3AXdL4q8v'
and SUCCEEDED='TRUE'
group by 1),
t3 as ( select
distinct TX_TO as collected_wallets1,
sum(AMOUNT) as sent_sol
from solana.core.fact_transfers
where BLOCK_TIMESTAMP>='2022-03-04' and BLOCK_TIMESTAMP<='2022-04-24'
and TX_FROM in (select collected_wallets from t2)
group by 1
)
select
BLOCK_TIMESTAMP::date as date,
l.LABEL_TYPE as type,
count(distinct TX_FROM) as no_wallets,
sum(AMOUNT) as sent_sol
from solana.core.fact_transfers t join solana.core.dim_labels l on t.TX_TO= l.address
where BLOCK_TIMESTAMP>='2022-03-04' and BLOCK_TIMESTAMP<='2022-04-24'
--and l.label != 'solana'
and TX_FROM in (select collected_wallets1 from t3)
group by 1,2
Run a query to Download Data