CryptoIcicle[Flash] Solana Swaps V2 - Top 5 - Weekly Volume
Updated 2022-02-07
999
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
›
⌄
-- Analyzing swaps on Solana is difficult because each major DEX uses a slightly different data structure.
-- The location of the tokens and amounts swapped can vary in index and between pre and postTokenBalances as well as the Instruction.
-- On each of the major Solana DEXes, what 5 tokens have seen the most volume in each week in January?
-- What is the volume of each stable coin for each DEX for each week in January?
-- Use the Solana events table to solve this problem
with swap_txns as (
select
TRY_PARSE_JSON(get(pretokenbalances, 0)) as pre_f,
TRY_PARSE_JSON(get(pretokenbalances, 1)) as pre_s,
TRY_PARSE_JSON(get(posttokenbalances, 0)) as post_f,
TRY_PARSE_JSON(get(posttokenbalances, 1)) as post_s,
TRY_PARSE_JSON(get(posttokenbalances, array_size(posttokenbalances) - 1)) as post_l,
*
from solana.events
where event_type = 'transfer'
and array_size(posttokenbalances) > 2
and post_f:owner = post_s:owner
and post_f:mint <> post_l:mint
and post_f:mint <> post_s:mint
and succeeded = 'TRUE'
and block_timestamp between '2022-01-01' and '2022-01-31' -- Only Jan Transactions
),
parsed_swap_txns as (
select
block_timestamp,
tx_id,
post_s:mint as token_from_address,
post_f:mint as token_to_address,
abs(pre_s:uiTokenAmount:uiAmountString - post_s:uiTokenAmount:uiAmountString) as token_from_amount,
abs(pre_f:uiTokenAmount:uiAmountString - post_f:uiTokenAmount:uiAmountString) as token_to_amount,
post_f:owner
from swap_txns
where token_to_amount > 0 and token_from_amount > 0
),
Run a query to Download Data