with pricet as (

select

date_trunc('hour', hour) as hours,

avg(price) as prices

from ink.price.ez_prices_hourly

where TOKEN_ADDRESS = '0x4200000000000000000000000000000000000006'

group by 1

select

round(avg(txs),0) as avg_tx,

round(avg(users),0) as avg_users,

round(avg(paid_fee_usd),0) as avg_paid_fee

select

date_trunc('day', l.block_timestamp) as days,

--min(l.block_timestamp) as min_date,

count(distinct l.tx_hash)as txs,

count(distinct case when l.ORIGIN_FUNCTION_SIGNATURE = '0xe884624b' then l.tx_hash end) as "GM",

count(distinct case when l.ORIGIN_FUNCTION_SIGNATURE = '0xc0129d43' then l.tx_hash end) as "GM to Friend",

count(distinct DECODED_LOG:"recipient") as recipients,

count(distinct DECODED_LOG:"user") as users,

round(sum(TX_FEE_PRECISE),2) as paid_fee,

round(sum(TX_FEE_PRECISE*prices),0) as paid_fee_usd

from ink.core.ez_decoded_event_logs l

join ink.core.fact_transactions t using (tx_hash)

left join pricet on hours = date_trunc('hour', l.block_timestamp)

where ORIGIN_TO_ADDRESS='0x9f500d075118272b3564ac6ef2c70a9067fd2d3f'

and l.TX_SUCCEEDED = 'TRUE'

and l.ORIGIN_FUNCTION_SIGNATURE in ('0xe884624b' --to another

,'0xc0129d43' --GM

group by 1

)