primo_data1. Optimism Open Analytics Bounty (7/31) - Price/Users/Performance
Updated 2023-01-29
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
›
⌄
--- Mega Query
-- Price
with price as (
select date(block_timestamp) dt
, avg( (cast(event_inputs:amount1In as float) / pow(10,6)) / (cast(event_inputs:amount0Out as float) / pow(10,18)) ) op_avg_price
from optimism.core.fact_event_logs
where event_name = 'Swap'
and contract_address = '0x47029bc8f5cbe3b464004e87ef9c9419a48018cd' -- OP/USDC Velodrome Pool
and cast(event_inputs:amount1In as float) > 0
and cast(event_inputs:amount0Out as float) > 0
group by 1
),
-- Users
users as (
select dt, count(distinct addr) addr_ct
from
(
select * from
(
select date(block_timestamp) dt, from_address addr
from optimism.core.fact_transactions
)
union all
(
select date(block_timestamp) dt, to_address addr
from optimism.core.fact_transactions
)
)
group by 1
),
-- Performance
txns as (
select date(block_timestamp) dt
, count(distinct block_hash) block_ct
, count(distinct case when status = 'SUCCESS' then tx_hash else null end) success_tx_ct
, count(distinct tx_hash) tx_ct
Run a query to Download Data