DATE | DEX | UNIQUE_TRADERS | TOTAL_DAILY_VOLUME | AVG_DAILY_VOLUME | |
|---|---|---|---|---|---|
1 | 2025-02-15 00:00:00.000 | uniswap-v3 | 5346 | 100390664.08 | 5939.924506242 |
2 | 2025-02-15 00:00:00.000 | pancakeswap-v3 | 297 | 2601275.38 | 4122.464944532 |
3 | 2025-02-14 00:00:00.000 | uniswap-v3 | 30780 | 1166679168.74 | 9971.020270069 |
4 | 2025-02-14 00:00:00.000 | pancakeswap-v3 | 1424 | 21510446.3 | 4674.151738375 |
5 | 2025-02-14 00:00:00.000 | hashflow-v3 | 121 | 4892033.83 | 19412.83265873 |
6 | 2025-02-13 00:00:00.000 | uniswap-v3 | 28679 | 1200480588.47 | 10597.182176231 |
7 | 2025-02-13 00:00:00.000 | pancakeswap-v3 | 1494 | 22889198.07 | 4363.171572627 |
8 | 2025-02-13 00:00:00.000 | hashflow-v3 | 86 | 4626385.21 | 22242.236586538 |
9 | 2025-02-12 00:00:00.000 | uniswap-v3 | 26923 | 1654104727.56 | 13729.858705624 |
10 | 2025-02-12 00:00:00.000 | pancakeswap-v3 | 1456 | 30161232.59 | 5334.494621507 |
11 | 2025-02-12 00:00:00.000 | hashflow-v3 | 98 | 10494246.05 | 38440.461721612 |
12 | 2025-02-11 00:00:00.000 | uniswap-v3 | 29086 | 1139484863.11 | 9662.465238491 |
13 | 2025-02-11 00:00:00.000 | pancakeswap-v3 | 1482 | 20235647.39 | 3499.766065375 |
14 | 2025-02-11 00:00:00.000 | hashflow-v3 | 101 | 5540126.48 | 22798.874403292 |
15 | 2025-02-10 00:00:00.000 | uniswap-v3 | 28732 | 1082749929.1 | 8750.484330348 |
16 | 2025-02-10 00:00:00.000 | pancakeswap-v3 | 1296 | 19765303.52 | 3871.753872674 |
17 | 2025-02-10 00:00:00.000 | hashflow-v3 | 104 | 3304032.49 | 13766.802041667 |
18 | 2025-02-09 00:00:00.000 | uniswap-v3 | 27610 | 863045525.76 | 7758.270489204 |
19 | 2025-02-09 00:00:00.000 | pancakeswap-v3 | 1311 | 17003054.83 | 3441.913933198 |
20 | 2025-02-09 00:00:00.000 | hashflow-v3 | 118 | 7031119.93 | 27465.312226563 |
angelnathExercise on Aggregate with CTE
Updated 2025-02-15
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
›
⌄
-- Exercise on Aggregate with CTE forked copy @ https://flipsidecrypto.xyz/studio/queries/827b2277-7404-47cc-8f31-55e7e2ca5d13
--Create a query to analyze DEX swap activity on Ethereum using ethereum.defi.ez_dex_swaps. Find:
--Daily swap statistics for each DEX (platform)
--Only include days with total volume > $1 million
--Only look at the last 30 days of data
--Show statistics for number of swaps, unique traders, total volume, average swap size
--Order by daily volume
WITH vol_data AS (
SELECT
DATE_TRUNC('day', block_timestamp) as date,
--DATE_TRUNC('month', block_timestamp) as month,
platform AS dex,
Origin_from_address AS user,
COALESCE(AMOUNT_IN_USD, amount_out_usd) AS Total_volume
FROM
ethereum.defi.ez_dex_swaps
WHERE
block_timestamp >= DATEADD(day, -30, Current_date())
--WHERE block_timestamp >= DATEADD(month, -12, Current_date())
)
SELECT
date,
FROM
vol_data
WHERE dex like '%v3' --new addition and tweaking from the office hour
GROUP BY
date,
Last run: 2 months ago
92
7KB
2s