Specternew vs returning
Updated 2025-03-03
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 Ncprice AS (
SELECT
trunc(hour, 'day') AS day,
AVG(price) AS price
FROM
near.price.ez_prices_hourly
WHERE
token_address = 'wrap.near'
GROUP BY
day
),
Deposits AS (
SELECT
trunc(d.block_timestamp, 'day') AS deposit_day,
d.signer_id AS owner
FROM near.core.fact_logs d
WHERE d.predecessor_id = 'wrap.near'
AND d.receiver_id = 'intents.near'
AND d.receipt_succeeded = 1
),
-- Find first deposit date for each user
FirstDeposit AS (
SELECT
owner,
MIN(deposit_day) AS first_deposit_day
FROM Deposits
GROUP BY owner
)
-- Count daily new & returning users
SELECT
d.deposit_day,
COUNT(DISTINCT CASE WHEN d.deposit_day = f.first_deposit_day THEN d.owner END) AS new_users,
COUNT(DISTINCT CASE WHEN d.deposit_day > f.first_deposit_day THEN d.owner END) AS returning_users
QueryRunArchived: QueryRun has been archived