D3 TeamAverage Daily New User-QuocBao
Updated 2024-08-04
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
›
⌄
-- -- User: Average Daily New User
-- with tx_user as (
-- select
-- BLOCK_TIMESTAMP,
-- FROM_ADDRESS
-- from blast.core.fact_transactions
-- ),
-- tx_new_user as(
-- select
-- MIN(BLOCK_TIMESTAMP) time,
-- FROM_ADDRESS
-- from tx_user
-- group by 2
-- )
-- select
-- date_trunc('day', time) as daily_time,
-- count(distinct FROM_ADDRESS) as "new_user",
-- avg("new_user") over (order by daily_time) as avg_user
-- from tx_new_user
-- -- where time::date >= current_date - 30
-- group by 1
-- order by 1
WITH daily_new_users AS (
SELECT
DATE_TRUNC('day', block_timestamp) AS date,
COUNT(DISTINCT from_address) AS new_users
FROM blast.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - INTERVAL '30 days'
AND from_address NOT IN (
SELECT DISTINCT from_address
FROM blast.core.fact_transactions
WHERE block_timestamp < DATE_TRUNC('day', CURRENT_DATE - INTERVAL '30 days')
)
GROUP BY 1
)
QueryRunArchived: QueryRun has been archived