Sajjadiiibonk12
Updated 2023-01-04
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
›
⌄
WITH date_base AS (
SELECT block_timestamp::date AS date,
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
GROUP BY 1
),
overall_base AS (
SELECT
COUNT(DISTINCT CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swapper ELSE NULL END) AS unique_sellers_total,
COUNT(DISTINCT CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swapper ELSE NULL END) AS unique_buyers_total,
COUNT(DISTINCT swapper) AS unique_swappers_total,
SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN -swap_from_amount ELSE swap_to_amount END) AS net_swap_amout_total,
SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_from_amount ELSE 0 END) AS swap_from_amount_total,
SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE 0 END) AS swap_to_amount_total,
SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE swap_from_amount END) AS swap_volume_total
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
)
SELECT *,
CASE WHEN net_swap_amout > 0 THEN 'Net Buy Volume' ELSE 'Net Sell Volume' END AS category
FROM date_base
JOIN overall_base ON TRUE
Run a query to Download Data