OneDataAnalystSwap ETH-Stablecoins
Updated 2022-09-25Copy Reference Fork
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
36
›
⌄
WITH t1 AS (
SELECT Date_trunc('day',BLOCK_TIMESTAMP) AS Date, SUM(AMOUNT_IN) AS Stable_to_eth_amount_sent, SUM(AMOUNT_OUT) AS Stable_to_eth_amount_received,count(*) AS Stable_to_eth_num
FROM ethereum.core.ez_dex_swaps
WHERE SYMBOL_IN IN ('DAI','BUSD','USDC','USDT','USDT')
AND SYMBOL_OUT IN ('WETH','stETH','rETH','aETH')
AND date >= CURRENT_DATE - 60
AND Date < Current_date
GROUP BY 1
),
t2 AS (
SELECT Date_trunc('day',BLOCK_TIMESTAMP) AS Dt, SUM(AMOUNT_IN) AS eth_to_Stable_amount_sent, SUM(AMOUNT_OUT) AS eth_to_Stable_amount_received,count(*) AS eth_to_Stable_num
FROM ethereum.core.ez_dex_swaps
WHERE SYMBOL_OUT IN ('DAI','BUSD','USDC','USDT','USDT')
AND SYMBOL_IN IN ('WETH','stETH','rETH','aETH')
AND dt >= CURRENT_DATE - 60
AND dt < Current_date
GROUP BY 1
)
SELECT Date,
Stable_to_eth_amount_sent,
Stable_to_eth_amount_received,
eth_to_Stable_amount_sent,
eth_to_Stable_amount_received,
Stable_to_eth_num,
eth_to_Stable_num,
Stable_to_eth_amount_sent-eth_to_Stable_amount_received AS Balance_swaps_usd,
Stable_to_eth_amount_received-eth_to_Stable_amount_sent AS Balance_swaps_eth,
IFF(date = '2022-09-15', 'Merge-Day', IFF(date < '2022-09-15','Pre-Merge','Post-Merge')) AS Merge_status,
AVG(Stable_to_eth_amount_sent) OVER (Partition BY Merge_status) AS AVG_Stable_to_eth_sent,
AVG(Stable_to_eth_amount_received) OVER (Partition BY Merge_status) AS AVG_Stable_to_eth_received,
AVG(eth_to_Stable_amount_sent) OVER (Partition BY Merge_status) AS AVG_eth_to_Stable_sent,
AVG(eth_to_Stable_amount_received) OVER (Partition BY Merge_status) AS AVG_eth_to_Stable_received,
Run a query to Download Data