berg2024-07-15 05:10 PM copy
Updated 2024-09-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
›
⌄
WITH uniswap AS (
SELECT
ORIGIN_FROM_ADDRESS AS UserAddress,
TX_HASH,
BLOCK_TIMESTAMP,
COALESCE(AMOUNT_IN_USD,AMOUNT_OUT_USD) AS AmountUsd,
CASE WHEN BLOCK_TIMESTAMP>='2023-10-17' THEN 'After' ELSE 'Before' END AS TimePeriod
FROM
ethereum.defi.ez_dex_swaps
JOIN
ethereum.core.fact_transactions USING (TX_HASH)
WHERE
AmountUsd BETWEEN 0 AND 100000000
AND TO_ADDRESS IN ('0x3fc91a3afd70395cd496c647d5a6cc9d4b2b7fad','0xef1c6e67703c7bd7107eed8303fbe6ec2554bf6b')
)
, TopUsers AS (
SELECT
UserAddress
FROM (
SELECT
SUM(AmountUsd) AS VolumeUsd,
UserAddress
FROM
uniswap
GROUP BY UserAddress
)
WHERE VolumeUsd>=5000000
)
SELECT
DATE_TRUNC('WEEK',BLOCK_TIMESTAMP) AS DATE,
TimePeriod,
COUNT(DISTINCT TX_HASH) AS Txns,
COUNT(DISTINCT UserAddress) AS Users,
SUM(AmountUsd) AS VolumeUsd,
AVG(AmountUsd) AS AverageAmountUsd
QueryRunArchived: QueryRun has been archived