JimMyersTechNew Accounts Created Per Day
Updated 2020-11-25
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
›
⌄
SELECT
date_trunc('day', start_date) AS metric_date,
COUNT(distinct address) AS new_address
FROM (
-- Get the first day an address appeared on the to/from side of a tx
SELECT
address,
event_currency,
min(start_date) AS start_date
FROM (
-- Retrieve the first time an address showed up on the 'to' side
SELECT
event_to AS address,
event_currency,
min(block_timestamp) AS start_date
FROM gold.cosmos_events
WHERE
event_amount > 0
AND event_currency = 'ATOM'
GROUP BY 1,2
UNION
-- Retrieve the first time an address showed up on the 'from' side
SELECT
event_from AS address,
event_currency,
min(block_timestamp) AS start_date
FROM gold.cosmos_events tr
WHERE
event_amount > 0
AND event_currency = 'ATOM'
AND address IS NOT NULL
GROUP BY 1, 2
)sq
Run a query to Download Data