binhachonNew Addresses - #1
Updated 2022-05-28
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 transactions as (
select
block_timestamp,
from_address
from flipside_prod_db.polygon.transactions
where nonce = 0
),
open_price as (
select
date_trunc('{{timefram}}', hour) as open_time,
price as open_price
from flipside_prod_db.ethereum_core.fact_hourly_token_prices
where symbol = 'MATIC'
qualify row_number() over (partition by open_time order by hour) = 1
),
close_price as (
select
date_trunc('{{timefram}}', hour) as close_time,
price as close_price
from flipside_prod_db.ethereum_core.fact_hourly_token_prices
where symbol = 'MATIC'
qualify row_number() over (partition by close_time order by hour desc) = 1
),
unique_users as (
select
date_trunc('{{timefram}}', block_timestamp) as time,
count(*) as number_of_new_users,
sum(number_of_new_users) over (order by time) as total_users
from transactions
group by 1
)
select
unique_users.*,
case
when coalesce(open_price, 0) > coalesce(close_price, 0) then 'Down trend'
when coalesce(open_price, 0) < coalesce(close_price, 0) then 'Up trend'
Run a query to Download Data