PierandreaUNIBOT DEX
Updated 2023-09-30
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
›
⌄
WITH aggregated_data AS (
SELECT
DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS DATE,
AMOUNT_IN,
AMOUNT_OUT,
CASE
WHEN TOKEN_IN = LOWER('0xf819d9Cb1c2A819Fd991781A822dE3ca8607c3C9') THEN 'BUY'
WHEN TOKEN_OUT = LOWER('0xf819d9Cb1c2A819Fd991781A822dE3ca8607c3C9') THEN 'SELL'
END AS ACTION
FROM
ethereum.core.ez_dex_swaps
WHERE
TOKEN_IN = LOWER('0xf819d9Cb1c2A819Fd991781A822dE3ca8607c3C9')
OR TOKEN_OUT = LOWER('0xf819d9Cb1c2A819Fd991781A822dE3ca8607c3C9')
)
SELECT
DATE,
SUM(CASE WHEN ACTION = 'BUY' THEN AMOUNT_IN ELSE 0 END) AS Buy_Pressure,
SUM(CASE WHEN ACTION = 'SELL' THEN AMOUNT_OUT ELSE 0 END) AS Sell_Pressure,
SUM(SUM(CASE WHEN ACTION = 'BUY' THEN AMOUNT_IN ELSE 0 END)) OVER (ORDER BY DATE) AS Cumulative_Buy_Pressure,
SUM(SUM(CASE WHEN ACTION = 'SELL' THEN AMOUNT_OUT ELSE 0 END)) OVER (ORDER BY DATE) AS Cumulative_Sell_Pressure,
SUM(SUM(CASE WHEN ACTION = 'BUY' THEN AMOUNT_IN ELSE 0 END)) OVER (ORDER BY DATE) + SUM(SUM(CASE WHEN ACTION = 'SELL' THEN AMOUNT_OUT ELSE 0 END)) OVER (ORDER BY DATE) AS Sum_Cumulative_Pressure
FROM aggregated_data
GROUP BY DATE
order by date desc;
Run a query to Download Data