hessUsers Breakdown AURORA Vs. Near
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
with near as (select signer_id as user,'Near' as chain, count(DISTINCT(signer_id)) as users, count(DISTINCT(tx_hash)) as total_tx
from near.core.fact_actions_events_function_call a join near.core.dim_address_labels b on a.receiver_id = b.address
where block_timestamp::date >= '{{Start_Date}}' and block_timestamp::date <= '{{End_Date}}'
group by 1,2
UNION
select from_address as user, 'Aurora' as chain, count(DISTINCT(from_address)) as users, count(DISTINCT(tx_hash)) as total_tx
from aurora.core.fact_transactions
where block_timestamp::date >= '{{Start_Date}}' and block_timestamp::date <= '{{End_Date}}'
group by 1,2 )
select count(DISTINCT(user)) as users, chain,
case when total_tx = 1 then 'Only 1 Tx'
when total_tx <= 5 then '1-5 Tx'
when total_tx <= 10 then '5-10 Tx'
when total_tx <= 15 then '10-15 Tx'
when total_tx <= 20 then '15-20 Tx'
when total_tx <= 25 then '20-25 Tx'
when total_tx <= 50 then '25-50 Tx'
when total_tx > 50 then '+50 Tx' end as cat
from near
group by 2,3
Run a query to Download Data