SWAP_DATE | New Swappers | Returning Swappers | Total Swappers | |
|---|---|---|---|---|
1 | 2025-02-06 00:00:00.000 | 4483 | 2728 | 7211 |
2 | 2025-02-07 00:00:00.000 | 4064 | 2441 | 6505 |
3 | 2025-02-08 00:00:00.000 | 3594 | 2208 | 5802 |
4 | 2025-02-09 00:00:00.000 | 3381 | 2121 | 5502 |
5 | 2025-02-10 00:00:00.000 | 4658 | 2388 | 7046 |
6 | 2025-02-11 00:00:00.000 | 5235 | 2475 | 7710 |
7 | 2025-02-12 00:00:00.000 | 6690 | 2808 | 9498 |
8 | 2025-02-13 00:00:00.000 | 4594 | 1922 | 6516 |
datavortex1:3
Updated 2025-02-13
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 swap_data AS (
SELECT
tx_hash,
contract_name,
contract_address,
origin_from_address,
block_timestamp,
DATE_TRUNC('day', block_timestamp) AS swap_date
FROM ronin.core.ez_decoded_event_logs
WHERE event_name = 'Swap'
AND block_timestamp >= CURRENT_DATE - INTERVAL '7 days'
),
historical_swappers AS (
SELECT DISTINCT origin_from_address
FROM ronin.core.ez_decoded_event_logs
WHERE event_name = 'Swap'
AND block_timestamp < CURRENT_DATE - INTERVAL '7 days'
),
classified_swappers AS (
SELECT
s.swap_date,
s.origin_from_address,
CASE
WHEN h.origin_from_address IS NULL THEN 'New'
ELSE 'Returning'
END AS swapper_type
FROM swap_data s
LEFT JOIN historical_swappers h
ON s.origin_from_address = h.origin_from_address
)
SELECT
swap_date,
COUNT(DISTINCT CASE WHEN swapper_type = 'New' THEN origin_from_address END) AS "New Swappers",
Last run: 3 months ago
8
345B
2s