Kruys-CollinsNew and Returning Users NEAR copy copy
Updated 2024-12-23
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 AllUserActivity AS (
SELECT DISTINCT
DATE_TRUNC('QUARTER', block_timestamp) AS activity_date,
TX_SIGNER AS address
FROM near.core.fact_transactions ft
LEFT JOIN near.core.dim_address_labels c
ON ft.TX_SIGNER = c.address
WHERE c.address IS NULL
AND TX_SIGNER IS NOT NULL
AND tx_succeeded = TRUE -- Added success filter
UNION ALL
SELECT DISTINCT
DATE_TRUNC('QUARTER', block_timestamp) AS activity_date,
TX_RECEIVER AS address
FROM near.core.fact_transactions ft
LEFT JOIN near.core.dim_address_labels c
ON ft.TX_RECEIVER = c.address
WHERE c.address IS NULL
AND TX_RECEIVER IS NOT NULL
AND tx_succeeded = TRUE
),
FirstTransactionDates AS (
SELECT
address,
MIN(activity_date) AS first_transaction_date
FROM AllUserActivity
GROUP BY address
),
Activity2024 AS (
SELECT DISTINCT
DATE_TRUNC('QUARTER', block_timestamp) AS activity_date,
TX_SIGNER AS address
FROM near.core.fact_transactions ft
LEFT JOIN near.core.dim_address_labels c
QueryRunArchived: QueryRun has been archived