mo115active_addresses_daily_through_yesterday
Updated 2023-12-06
999
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
›
⌄
⌄
/* use livequery to get a cache of data before 2023-11-30 */
with historic_dau AS (
select
livequery.live.udf_api (
'https://flipsidecrypto.xyz/api/v1/queries/a0080c98-c99d-4f0c-87f4-23140ac47b07/data/latest'
):data as json_data
),
-- is there a way to not have to know the columns?
dau_data AS (
SELECT
d.value:"CHAIN"::VARCHAR AS chain,
d.value:"DAY_"::date AS day_,
TO_NUMBER(d.value:"N_FIRST_TX") AS N_FIRST_TX,
TO_NUMBER(d.value:"N_UNIQUE_FROM") AS N_UNIQUE_FROM
FROM
historic_dau,
LATERAL FLATTEN(input => historic_dau.json_data::VARIANT) d
),
/* BELOW is Data AFTER 2023-11-30 */
/*
-- THIS QUERY Reads CACHE up to 11-30-2023
-- it then appends daily data since with a 24hr refresh
-- DASHBOARD will take from here and then filter
-- SEE: https://flipsidecrypto.xyz/charliemarketplace/q/IOB40pd1hL6y/active_addresses_daily_through_20231130
*/
eth_dau AS (
select
'ethereum' as chain,
date_trunc('day', block_timestamp) as day_,
sum(case when nonce = 1 then 1 else 0 end) as n_first_tx,
count(distinct from_address) as n_unique_from
Run a query to Download Data