berg2
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
›
⌄
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')
AND YEAR(BLOCK_TIMESTAMP)>=2023
)
SELECT
DATE_TRUNC('month', BLOCK_TIMESTAMP) AS MONTH,
TimePeriod,
COUNT(DISTINCT TX_HASH) AS Txns,
COUNT(DISTINCT UserAddress) AS Users,
SUM(AmountUsd) AS VolumeUsd,
AVG(AmountUsd) AS AverageAmountUsd,
SUM(VolumeUsd) OVER (PARTITION BY TimePeriod ORDER BY MONTH) AS TOTALVolumeUsd
FROM
uniswap
GROUP BY TimePeriod , MONTH
QueryRunArchived: QueryRun has been archived