Hosein-maleki-9555Daily total user and Volume on overtime markets in past two weeks
Updated 2022-08-24Copy Reference Fork
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
›
⌄
with main_tb as (
with tokens_tb as (
select distinct token_ADDRESS,SYMBOL,DECIMALS
from optimism.core.fact_hourly_token_prices
where token_ADDRESS in ('0x7f5c764cbc14f9669b88837ca1490cca17c31607','0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9','0xda10009cbd5d07dd0cecc66161fc93d7c9000da1','0x94b008aa00579c1307b0ef2c499ad98a8ce58e58')
and HOUR::DATE >=CURRENT_DATE-14
)
select date_trunc('day',block_timestamp)as date ,tokens_tb.SYMBOL as tokens_tb , sum(RAW_AMOUNT/pow(10,tokens_tb.DECIMALS)) as volume
from optimism.core.fact_token_transfers join tokens_tb on optimism.core.fact_token_transfers.CONTRACT_ADDRESS=tokens_tb.token_ADDRESS
where ORIGIN_FROM_ADDRESS=FROM_ADDRESS
and ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
and block_timestamp::date >=CURRENT_DATE-14
group by date,tokens_tb.symbol
order by date asc
),
daily_users_tb as (
select date_trunc('day',block_timestamp)as date ,count( distinct from_address ) as total_users
from optimism.core.fact_token_transfers
where BLOCK_TIMESTAMP::date >= CURRENT_DATE-14
and ORIGIN_TO_ADDRESS='0x170a5714112daeff20e798b6e92e25b86ea603c1'
group by date
order by date asc
)
select date,sum(volume) as total_volume,daily_users_tb.total_users
from main_tb
join daily_users_tb on date = date
group by date,total_users
order by date asc
Run a query to Download Data