with raw_entity as

(SELECT

t1.block_timestamp,

t1.type,

t1.address,

t2.address_name,

t2.label_type,

t1.tx_hash as hash

from

( (select

block_timestamp,

'send' as type,

from_address as address,

tx_hash

from arbitrum.core.fact_transactions)

union all

(select

block_timestamp,

'receive' as type,

to_address,

tx_hash

from arbitrum.core.fact_transactions)) t1

left join arbitrum.core.dim_labels t2

on t1.address = t2.address

and t1.address not in (select distinct address from arbitrum.core.dim_contracts)),

entity_stats as

(SELECT

address,

case when address_name is null then left(address, 5) else address_name end as address_name,

case when label_type is null then 'normal account' else label_type end as label_type,

count(distinct hash) as no_tx