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'

,

all_user as (

SELECT

a.block_timestamp,

a.tx_id,

tx_from,

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

FROM cosmos.core.fact_transactions a

JOIN all_txns b on a.tx_id = b.tx_id

AND TX_SUCCEEDED

,

all_minter as (

SELECT

date_trunc('hour', block_timestamp) as hour,

count(DISTINCT tx_from) as n_user

FROM all_user

GROUP BY 1

),

new as (

SELECT

date_trunc('hour', block_timestamp) as date,

count(DISTINCT tx_from) as new

FROM all_user