Cohort Day | Days Since Cohort | Cohort Size | Active Users | Total Transactions | Retention Rate (%) | |
|---|---|---|---|---|---|---|
1 | 2025-02-05 00:00:00.000 | 0 | 74484 | 74484 | 114349 | 100 |
2 | 2025-02-05 00:00:00.000 | 1 | 0 | 28355 | 49670 | |
3 | 2025-02-05 00:00:00.000 | 2 | 0 | 58343 | 76344 | |
4 | 2025-02-05 00:00:00.000 | 3 | 0 | 57311 | 76291 | |
5 | 2025-02-05 00:00:00.000 | 4 | 0 | 62479 | 83124 | |
6 | 2025-02-05 00:00:00.000 | 5 | 0 | 62535 | 82863 | |
7 | 2025-02-05 00:00:00.000 | 6 | 0 | 38802 | 72316 | |
8 | 2025-02-05 00:00:00.000 | 7 | 0 | 44430 | 68306 | |
9 | 2025-02-06 00:00:00.000 | 0 | 23001 | 23001 | 32510 | 100 |
10 | 2025-02-06 00:00:00.000 | 1 | 0 | 12310 | 15080 | |
11 | 2025-02-06 00:00:00.000 | 2 | 0 | 15680 | 18862 | |
12 | 2025-02-06 00:00:00.000 | 3 | 0 | 11127 | 14219 | |
13 | 2025-02-06 00:00:00.000 | 4 | 0 | 17421 | 21342 | |
14 | 2025-02-06 00:00:00.000 | 5 | 0 | 8603 | 14774 | |
15 | 2025-02-06 00:00:00.000 | 6 | 0 | 7893 | 12035 | |
16 | 2025-02-06 00:00:00.000 | 7 | 0 | 11383 | 15633 | |
17 | 2025-02-07 00:00:00.000 | 0 | 4137 | 4137 | 10868 | 100 |
18 | 2025-02-07 00:00:00.000 | 1 | 0 | 1220 | 3175 | |
19 | 2025-02-07 00:00:00.000 | 2 | 0 | 1189 | 2995 | |
20 | 2025-02-07 00:00:00.000 | 3 | 0 | 1158 | 2343 |
m3jiproductive-yellow
Updated 8 days ago
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 first_trade AS (
-- Get the first-ever trade date for each trader on Ref Finance
SELECT
trader,
MIN(DATE(block_timestamp)) AS first_trade_date
FROM near.defi.ez_dex_swaps
WHERE platform = 'v2.ref-finance.near'
AND block_timestamp::DATE > '2025-01-01'
GROUP BY trader
),
daily_trades AS (
-- Get daily swap volumes and trader activity
SELECT
DATE(block_timestamp) AS trade_date,
trader,
SUM(CASE WHEN amount_in_usd IS NULL THEN amount_out_usd ELSE 0 END) AS swap_volume_usd
FROM near.defi.ez_dex_swaps
WHERE platform = 'v2.ref-finance.near'
AND block_timestamp::DATE > '2025-01-01'
GROUP BY 1, 2
),
classified_trades AS (
-- Categorize traders as 'New' or 'Returning'
SELECT
d.trade_date,
d.trader,
d.swap_volume_usd,
CASE
WHEN d.trade_date = f.first_trade_date THEN 'New Trader'
ELSE 'Returning Trader'
END AS trader_type
FROM daily_trades d
JOIN first_trade f ON d.trader = f.trader
)
SELECT
trade_date,
Last run: 8 days ago
...
220
10KB
4s