datavortexwhich Category realy contributed to the Volume
Updated 2024-10-20
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
›
⌄
WITH TraderVolumes AS (
SELECT
origin_from_address AS SwapperAddress,
SUM(amount_in_usd) AS TotalVolumeUSD
FROM
polygon.defi.ez_dex_swaps
WHERE
block_timestamp >= DATEADD(MONTH, -3, CURRENT_DATE)
AND platform = 'quickswap-v3'
GROUP BY
origin_from_address
),
CategorizedTraders AS (
SELECT
SwapperAddress,
TotalVolumeUSD,
CASE
WHEN TotalVolumeUSD BETWEEN 0 AND 500 THEN 'Crabs'
WHEN TotalVolumeUSD BETWEEN 501 AND 1000 THEN 'Small Fish'
WHEN TotalVolumeUSD BETWEEN 1001 AND 10000 THEN 'Medium Fish'
WHEN TotalVolumeUSD BETWEEN 10001 AND 100000 THEN 'Sharks'
WHEN TotalVolumeUSD > 100000 THEN 'Whales'
ELSE 'Unknown'
END AS TraderCategory
FROM
TraderVolumes
)
SELECT
TraderCategory,
COUNT(DISTINCT SwapperAddress) AS TotalTraders,
COALESCE(SUM(TotalVolumeUSD), 0) AS TotalVolumeUSD,
COALESCE(SUM(TotalVolumeUSD) / NULLIF(COUNT(DISTINCT SwapperAddress), 0), 0) AS AvgVolumePerTrader,
COALESCE(SUM(TotalVolumeUSD) / NULLIF(COUNT(DISTINCT SwapperAddress), 0), 0) AS VolumeContributionRatio
FROM
CategorizedTraders
GROUP BY
QueryRunArchived: QueryRun has been archived