Kruys-CollinsUser's First Interaction DApp Engagement
Updated 2025-01-20
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 FirstTransactionDates AS (
SELECT
from_address AS address,
MIN(block_timestamp) AS first_transaction_date
FROM avalanche.core.fact_transactions
GROUP BY from_address
)
,FirstTransactions AS (
SELECT
ft.address,
ft.first_transaction_date,
t.block_hash,
t.tx_hash,
t.from_address,
t.to_address,
t.value
FROM FirstTransactionDates ft
JOIN avalanche.core.fact_transactions t
ON (ft.address = t.from_address )
AND ft.first_transaction_date = t.block_timestamp
),
LabeledTransactions AS (
SELECT
ft.address,
ft.first_transaction_date,
dl.project_name,
dl.label_type,
dl.label_subtype
FROM FirstTransactions ft
LEFT JOIN avalanche.core.dim_labels dl
ON ft.to_address = dl.address
WHERE dl.label_type ='dapp'
)
SELECT
project_name as "Project Name",
QueryRunArchived: QueryRun has been archived