Mrfti$BONK Net Swap Volume by DEX copy
Updated 2023-04-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
›
⌄
-- forked from kellen / $BONK Net Swap Volume by DEX @ https://flipsidecrypto.xyz/kellen/q/2023-01-03-12-37-am-UsUzVT
WITH t0 AS (
SELECT INITCAP(SPLIT(swap_program, ' ')[0]::string) AS dex
, COUNT(DISTINCT CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swapper ELSE NULL END) AS unique_sellers
, COUNT(DISTINCT CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swapper ELSE NULL END) AS unique_buyers
, COUNT(DISTINCT swapper) AS unique_swappers
, SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN -swap_from_amount ELSE swap_to_amount END) AS net_swap_amout
, SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_from_amount ELSE 0 END) AS swap_from_amount
, SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE 0 END) AS swap_to_amount
, SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE swap_from_amount END) AS swap_volume
FROM solana.core.fact_swaps
WHERE block_timestamp >= '2022-12-25'
AND (
swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'
OR swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'
)
AND swap_from_mint <> swap_to_mint
AND succeeded
GROUP BY 1
)
SELECT *
, CASE WHEN net_swap_amout > 0 THEN 'Net Buy Volume' ELSE 'Net Sell Volume' END AS category
FROM t0
Run a query to Download Data