base_stats AS (

SELECT DISTINCT

tx_from as user_address,

count(*) as n_txn,

count(DISTINCT date_trunc('DAY', t.block_timestamp)) as n_days_active,

MIN(

DATEDIFF('days', t.block_timestamp, CURRENT_TIMESTAMP)

) as days_since_last_txn,

count(DISTINCT attribute_value) as n_contracts

FROM

osmosis.core.fact_transactions t

LEFT OUTER JOIN osmosis.core.fact_msg_attributes m ON t.tx_id = m.tx_id

WHERE

t.block_timestamp::date >= CURRENT_DATE - 90

AND m.block_timestamp::date >= CURRENT_DATE - 90

AND attribute_key = 'module'

GROUP BY

tx_from

),

complex_txs AS (

SELECT

tx_from as user_address,

count(DISTINCT tx_id) as n_complex_txn

FROM

osmosis.core.fact_transactions

WHERE

block_timestamp::date >= CURRENT_DATE - 90

AND tx_id NOT IN (

SELECT

tx_id

FROM

osmosis.core.fact_transfers

WHERE

transfer_type = 'OSMOSIS'

)