i_dan2024-05-28 02:19 PM
Updated 2024-06-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 recent_transactions AS (
SELECT
origin_from_address,
amount_in_usd,
amount_out_usd,
MIN(date_trunc('day', block_timestamp)) AS first_date
FROM
base.defi.ez_dex_swaps
WHERE block_timestamp >= (current_date - INTERVAL '7 DAYS')
GROUP BY 1, 2, 3
),
filtered_addresses AS (
SELECT
origin_from_address
FROM
base.defi.ez_dex_swaps
WHERE block_timestamp < (current_date - INTERVAL '7 DAYS')
GROUP BY origin_from_address
),
new_users AS (
SELECT
COUNT(DISTINCT r.origin_from_address) AS new_users
, MIN(r.first_date) AS first_date
, SUM(ABS(amount_in_usd) + ABS(amount_out_usd)) / 2 AS New_users_transaction_volume
FROM
recent_transactions r
LEFT JOIN filtered_addresses f
ON r.origin_from_address = f.origin_from_address
WHERE
f.origin_from_address IS NULL
--GROUP BY 2
ORDER BY r.first_date;
),
QueryRunArchived: QueryRun has been archived