txns.sender AS user_address,

contr.n_contracts,

COUNT(1) AS n_txn,

COUNT(DISTINCT(DATE_TRUNC('DAY', block_timestamp))) AS n_days_active,

DATEDIFF(DAY, MAX(block_timestamp), '2024-01-04' :: DATE) AS days_since_last_txn,

-- a complex tx is any tx that is NOT a simple APT transfer; i.e., has input data!

count_if(payload:type_arguments <> '[]') AS n_complex_txn

aptos.core.fact_transactions txns

join (

select

sender,

count(distinct value) as n_contracts

from

aptos.core.fact_transactions,

LATERAL FLATTEN(input = > payload:type_arguments)

where

block_timestamp >= '2024-01-04' :: DATE - 9

and length(value) > 3

group by

1

) contr on contr.sender = txns.sender

block_timestamp >= '2024-01-04' :: DATE - 9

GROUP BY

1, 2;