flyingfishOre hourly presence
Updated 2024-04-07
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
›
⌄
-- forked from Ore hourly tx @ https://flipsidecrypto.xyz/edit/queries/f854f58d-74c6-4705-8f84-52d994e4d36c
with hourly_txs as (
select
date_trunc(hour, block_timestamp) as timestamp
, count(1) as hourly_txs
, count_if(succeeded) as hourly_succeeded_txs
from solana.core.fact_transactions
where block_timestamp::date = '2024-04-05'
group by 1
)
-- select * from hourly_txs
, ore_events as (
select
date_trunc(hour,block_timestamp) as timestamp
--, address_name
-- , case
-- when program_id = 'mineRHF5r6S7HyD9SppBfVMXMavDkJsxwGesEvxZr2A' then 'ORE'
-- else 'Not ORE'
-- end as label_
--, program_id
, count(tx_id) as ore_txs
-- , count(distinct tx_id) as distinct_txs
, count_if(succeeded) as ore_successful_txs
--, count_if(program_id = 'mineRHF5r6S7HyD9SppBfVMXMavDkJsxwGesEvxZr2A' as ore_events
--, round(count_if(program_id = 'mineRHF5r6S7HyD9SppBfVMXMavDkJsxwGesEvxZr2A') / txs, 3) as ore_dominance
-- , count_if(succeeded) as successful_txs
-- , count_if(not succeeded) as failed_txs
-- , round( 100 * failed_txs / txs, 2) as fail_percent
from solana.core.fact_events
--left join hourly_txs using(timestamp)
-- left join solana.core.dim_labels on address = program_id
where 1 = 1
--and succeeded
QueryRunArchived: QueryRun has been archived