headitmanagerhistogram
Updated 2022-03-08
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
›
⌄
with tbldate as (select (inner_instruction:instructions[0]:parsed:info:amount/1e9) as amount ,
(CASE
WHEN amount<0.5 THEN 'a.less than 0.5 SOL'
WHEN amount>=0.5 and amount<1 THEN 'b.between 0.5 and 1 SOL'
WHEN amount>=1 and amount<2 THEN 'c.between 1 and 2 SOL'
WHEN amount>=2 and amount<3 THEN 'd.between 2 and 3 SOL'
WHEN amount>=3 and amount<4 THEN 'e.between 3 and 4 SOL'
WHEN amount>=4 and amount<5 THEN 'f.between 4 and 5 SOL'
WHEN amount>=5 and amount<10 THEN 'g.between 5 and 10 SOL'
WHEN amount>=10 and amount <50 THEN 'h.between 10 and 50 SOL'
WHEN amount>=50 THEN 'i.above 50' END) as histogram
from solana.events
inner join solana.transactions
on solana.transactions.tx_id=solana.events.tx_id
where
inner_instruction:instructions[0]:parsed:type='transfer'
and inner_instruction:instructions[1]:parsed:type='transfer'
and inner_instruction:instructions[0]:programId='TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA'
and inner_instruction:instructions[1]:programId='11111111111111111111111111111111'
and program_Id = 'MarBmsSgKXdrN1egZf5sqe1TMai9K1rChYNDJgjq7aD'
and date(solana.events.block_timestamp) > '2022-02-01'
)
select count(*),histogram from tbldate
group by histogram