with all_txns as (

SELECT

block_timestamp,

tx_id

FROM cosmos.core.fact_msg_attributes

WHERE msg_type = 'message'

AND ATTRIBUTE_KEY = 'module'

AND

ATTRIBUTE_VALUE = 'bank'

AND TX_SUCCEEDED

AND block_timestamp :: date >= '2024-01-17'

SELECT

ATTRIBUTE_VALUE as user

from cosmos.core.fact_msg_attributes a

JOIN all_txns b ON a.tx_id = b.tx_id

WHERE msg_type = 'tx' and ATTRIBUTE_KEY = 'fee_payer'

AND a.block_timestamp :: date >= '2024-01-17'

,

all_user as (

SELECT

block_timestamp,

tx_id,

tx_from,

row_number() over (partition by tx_from order by block_timestamp) as rank

FROM cosmos.core.fact_transactions

WHERE tx_from in (SELECT user from all_users)

AND TX_SUCCEEDED

SELECT

date_trunc('hour', block_timestamp) as hour,

count(DISTINCT tx_from) as new_user,