-- forked from Aurora Base @ https://flipsidecrypto.xyz/edit/queries/430db199-f894-42e3-96c0-950babe16171

with base as (

SELECT

COUNT(tx_hash) as t_txns

, COUNT(DISTINCT TX_SIGNER) as t_users

, SUM(TRANSACTION_FEE/1e24) as t_fee

, date_trunc(day, BLOCK_TIMESTAMP) as ddate

, CASE WHEN (tx_receiver ilike '%.kaiching' or tx_signer ilike '%.kaiching') THEN 'CosmosAI' ELSE 'other' END as cat

FROM near.core.fact_transactions

WHERE tx_status='Success'

AND block_timestamp >= '2023-01-01'

GROUP BY ddate, cat

SELECT * from base