cheeyoung-kekSol 3.1
Updated 2022-07-25
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
›
⌄
with table1 as(
select
a.TX_ID,
b.block_timestamp,
d.value as signers,
instructions[0]:parsed:info:amount::float / POW(10,6) AS "Token amount",
post_token_balances[1]:mint::string as destination_address,
address_name,
a.program_id as contract_address,
ROW_NUMBER() OVER (partition by b.tx_id ORDER BY signers) as "row number"
from solana.core.fact_events a
inner join solana.core.fact_transactions b
on a.tx_id = b.tx_id
left join solana.core.dim_labels c on b.post_token_balances[1]:mint::string = c.address,
table(flatten(input => signers)) d
WHERE a.program_id = 'SWiMDJYFUGj6cPrQ6QYYYWZtvXQdRChSVAygDZDsCHC'
and b.succeeded = 'TRUE'
and b.block_timestamp >= '2022-06-01'
)
select
date_trunc('day',block_timestamp) as days,
destination_address,
case when destination_address = 'BJUH9GJLaMSLV1E7B3SQLCy9eCfyr6zsrwGcpS2MkqR1' then 'SwimUSD Solana'
else address_name
end as token_name,
sum ("Token amount") AS total_amount,
count (Distinct tx_id) as total_transaction_volume
from table1
where "row number"=1
group by 1,2,3
Run a query to Download Data