MotilolaExercise on Aggregate copy
Updated 2025-02-12
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 angelnath / Exercise on Aggregate @ https://flipsidecrypto.xyz/angelnath/q/L70Mx8x_mQZ9/exercise-on-aggregate
--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 volume_raw AS (
SELECT
Date_trunc('day', block_timestamp) AS date,
platform,
COALESCE(amount_in_usd, amount_out_usd) AS volume_usd,
origin_from_address
FROM
ethereum.defi.ez_dex_swaps
WHERE
block_timestamp :: date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
date,
platform AS dex,
COUNT(DISTINCT origin_from_address) AS unique_trader,
SUM(volume_usd) AS total_daily_volume_usd,
AVG(volume_usd) AS AVG_daily_volume_usd
FROM
volume_raw
GROUP BY
date, dex
HAVING total_daily_volume_usd > 1000000
ORDER BY
QueryRunArchived: QueryRun has been archived