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'

,

atom_price as (

SELECT

date_trunc('hour',date) AS hour,

avg(price) as usd_price

FROM (

TO_TIMESTAMP(value[0]::string) as date,

value[1] as price

FROM (

SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/cosmos/market_chart?vs_currency=usd&days=30') as response

),LATERAL FLATTEN (input => response:data:prices)

WHERE date >= '2023-01-17'

GROUP BY 1

,

fee as (

SELECT

date_trunc('hour', a.block_timestamp) as date,

sum(fee/1e6) as fee_atom,

sum(fee_atom) over (ORDER BY date) as cum_fee_atom,