ajetiAlternative Stats copy
Updated 2024-07-16
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
WHALES AS (
SELECT
origin_from_address AS user
,sum(amount_in_usd) AS TOTAL
FROM ethereum.defi.ez_dex_swaps
WHERE platform IN ('uniswap-v2', 'uniswap-v3')
AND (NOT platform IN ('uniswap-v2','uniswap-v3') OR tx_hash IN (SELECT DISTINCT tx_hash FROM ethereum.core.fact_transactions WHERE to_address IN('0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad','0xef1c6e67703c7bd7107eed8303fbe6ec2554bf6b')))
GROUP BY user
ORDER BY TOTAL DESC
LIMIT 10000
),
main AS (
SELECT
block_timestamp::date AS dt
,tx_hash
,origin_from_address AS user
,amount_in_usd AS amount_usd
, CASE WHEN user IN (SELECT DISTINCT user FROM WHALES) THEN 'Whale' ELSE 'Small' END AS TYPE
FROM ethereum.defi.ez_dex_swaps
WHERE amount_in_usd<=10000000
AND dt>='2023-01-01'
AND (NOT platform IN ('uniswap-v2','uniswap-v3') OR tx_hash IN (SELECT DISTINCT tx_hash FROM ethereum.core.fact_transactions WHERE to_address IN('0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad','0xef1c6e67703c7bd7107eed8303fbe6ec2554bf6b')))
)
SELECT
Type,
COUNT(DISTINCT(user)) AS "Users"
,COUNT(DISTINCT(tx_hash)) AS "TXNS"
,SUM(amount_usd) AS "Amount Volume($)"
,AVG(amount_usd) AS "AVG Volume($)"
,DATE_TRUNC('month', dt) AS DT
FROM main
GROUP BY TYPE,DT
QueryRunArchived: QueryRun has been archived