takeabreath$COMP Volume, Trade Count, Avg Trade Size, Turnover Rate copy
Updated 2024-03-31
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
›
⌄
-- forked from $comp Volume, Trade Count, Avg Trade Size, Turnover Rate @ https://flipsidecrypto.xyz/edit/queries/71529f3d-e919-4487-95c1-9314335dd04d
WITH comp_swaps AS (
SELECT
block_timestamp,
DATE_TRUNC('day', block_timestamp) AS day,
CASE
WHEN token_in = '0xc00e94cb662c3520282e6f5717214004a7f26888' THEN DIV0(amount_out_usd , amount_in)
ELSE DIV0(amount_in_usd , amount_out)
END AS price,
MAX(price) OVER (PARTITION BY day ORDER BY block_timestamp) AS high,
MIN(price) OVER (PARTITION BY day ORDER BY block_timestamp) AS low,
CASE
WHEN token_in = '0xc00e94cb662c3520282e6f5717214004a7f26888' THEN amount_out_usd
ELSE amount_in_usd
END AS amount_usd,
SUM(amount_usd) OVER (PARTITION BY day ORDER BY block_timestamp) AS volume,
COUNT(block_timestamp) OVER (PARTITION BY day ORDER BY block_timestamp) AS trade_count,
ROW_NUMBER() OVER (PARTITION BY day ORDER BY block_timestamp DESC) AS rank
FROM ethereum.defi.ez_dex_swaps
WHERE
(token_in = '0xc00e94cb662c3520282e6f5717214004a7f26888'
OR token_out = '0xc00e94cb662c3520282e6f5717214004a7f26888')
AND block_timestamp > DATE('2021-12-31 00:00')
),
comp_volume_trade_count_ats_tr_inter AS (
SELECT
day,
volume,
trade_count,
volume / trade_count AS avg_trade_size,
AVG(volume) OVER (ORDER BY block_timestamp ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS volume_7,
AVG(price) OVER (ORDER BY block_timestamp ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS close_7
FROM comp_swaps
WHERE rank = 1
QueryRunArchived: QueryRun has been archived