SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS activity_date,

SENDER AS user_address,

tx_hash AS transactions

FROM

aptos.core.fact_transactions

),

user_classification AS (

SELECT

user_address,

MIN(activity_date) AS first_txn_date

FROM

daily_activity

GROUP BY

user_address

),

categorized_data AS (

SELECT

activity_date,

user_address,

transactions

FROM

daily_activity

WHERE activity_date BETWEEN '2024-01-01' AND '2024-12-31'

),

data_activity AS (

SELECT

COUNT(DISTINCT c.transactions) AS Total_transactions,

COUNT(DISTINCT c.user_address) AS daily_active_users,

COUNT(DISTINCT CASE WHEN c.activity_date = u.first_txn_date THEN c.user_address END) AS new_users,

COUNT(DISTINCT CASE WHEN c.activity_date > u.first_txn_date THEN c.user_address END) AS returning_users