winnie-fs$EUL Volume, Trade Count, Avg Trade Size, Turnover Rate copy
Updated 2024-05-29
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 takeabreath / $EUL Volume, Trade Count, Avg Trade Size, Turnover Rate @ https://flipsidecrypto.xyz/takeabreath/q/SbW15VRAyGKe/eul-volume-trade-count-avg-trade-size-turnover-rate
WITH eul_swaps AS (
SELECT
block_timestamp,
DATE_TRUNC('day', block_timestamp) AS day,
CASE
WHEN token_in = '0xd9fcd98c322942075a5c3860693e9f4f03aae07b' 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 = '0xd9fcd98c322942075a5c3860693e9f4f03aae07b' 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 = '0xd9fcd98c322942075a5c3860693e9f4f03aae07b'
OR token_out = '0xd9fcd98c322942075a5c3860693e9f4f03aae07b')
AND block_timestamp > DATE('2021-12-31 00:00')
),
eul_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 eul_swaps
WHERE rank = 1
QueryRunArchived: QueryRun has been archived