SELECT COUNT(DISTINCT ACTION_ID) as totals,

MAX(CASE WHEN ACTION_NAME = 'Transfer' THEN 0 ELSE 1 END ) as txn_type,

TX_HASH

from near.core.fact_actions_events

WHERE block_timestamp>='2022-03-01'

GROUP BY tx_hash

),

user_types as (

SELECT

TX_SIGNER,

MAX(totals) as max_actions, MAX(txn_type) as txn_type,

date_trunc('day', block_timestamp) as ddate

from near.core.fact_transactions t

LEFT JOIN txn_types tt ON t.tx_hash = tt.tx_hash

WHERE

block_timestamp>='2022-03-01'

GROUP BY ddate,tx_signer

SELECT

COUNT(DISTINCT TX_SIGNER) as users,

CASE WHEN (max_actions>=3 AND txn_type!=0) THEN 'Sophisticated User' ELSE 'Casual User' END as cat,

ddate

from user_types

GROUP BY cat, ddate