datavortexnew vs returning
Updated 2025-02-18
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
swap_from_mint,
tx_id,
swapper,
swap_from_amount_usd,
DATE_TRUNC('WEEK', block_timestamp) AS week_
FROM
solana.marinade.ez_swaps
WHERE
swap_from_mint IN ('MNDEFzGvMt87ueuHvVU9VcTqsAP5b3fTGPsHuuPA5ey', 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So')
),
first_swap_week AS (
SELECT
swapper,
MIN(week_) AS first_swap_week
FROM
swap_data
GROUP BY
swapper
)
SELECT
sd.week_ AS "Week",
COUNT(DISTINCT CASE WHEN sd.swap_from_mint = 'MNDEFzGvMt87ueuHvVU9VcTqsAP5b3fTGPsHuuPA5ey'
AND sd.week_ = fs.first_swap_week THEN sd.swapper END) AS "New MNDE Swappers",
COUNT(DISTINCT CASE WHEN sd.swap_from_mint = 'MNDEFzGvMt87ueuHvVU9VcTqsAP5b3fTGPsHuuPA5ey'
AND sd.week_ > fs.first_swap_week THEN sd.swapper END) AS "Returning MNDE Swappers",
COUNT(DISTINCT CASE WHEN sd.swap_from_mint = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So'
AND sd.week_ = fs.first_swap_week THEN sd.swapper END) AS "New MSOL Swappers",
COUNT(DISTINCT CASE WHEN sd.swap_from_mint = 'mSoLzYCxHdYgdzU16g5QSh3i5K3z3KZK7ytfqcJm7So'
AND sd.week_ > fs.first_swap_week THEN sd.swapper END) AS "Returning MSOL Swappers",
FROM
QueryRunArchived: QueryRun has been archived