SELECT

MIN(BLOCK_TIMESTAMP) AS First_tx_timestamp,

INITCAP(PROJECT_NAME) AS PROJECTS,

INITCAP(LABEL_TYPE) AS SECTOR,

CASE

WHEN MAX(BLOCK_TIMESTAMP)::DATE >= CURRENT_DATE - INTERVAL '30 DAY' THEN 'Active'

ELSE 'Inactive'

END AS STATUS,

CASE

WHEN DATEDIFF(MONTH, MIN(BLOCK_TIMESTAMP)::DATE, CURRENT_DATE) = 0 THEN 'This Month'

WHEN DATEDIFF(MONTH, MIN(BLOCK_TIMESTAMP)::DATE, CURRENT_DATE) = 1 THEN '1 Month ago'

WHEN DATEDIFF(MONTH, MIN(BLOCK_TIMESTAMP)::DATE, CURRENT_DATE) < 12 THEN

TO_VARCHAR(DATEDIFF(MONTH, MIN(BLOCK_TIMESTAMP)::DATE, CURRENT_DATE)) || ' Months ago'

ELSE

TO_VARCHAR(FLOOR(DATEDIFF(MONTH, MIN(BLOCK_TIMESTAMP)::DATE, CURRENT_DATE) / 12)) || ' Year, ' ||

TO_VARCHAR(MOD(DATEDIFF(MONTH, MIN(BLOCK_TIMESTAMP)::DATE, CURRENT_DATE), 12)) || ' Months ago'

END AS "LIFESPAN ON BASE",

COUNT(DISTINCT FROM_ADDRESS) AS users,

COUNT(DISTINCT TX_HASH) AS transactions,

SUM(TX_FEE) AS TOTAL_TXN_FEE,

AVG(TX_FEE) AS AVG_TXN_FEE

FROM base.core.fact_transactions a

LEFT JOIN base.core.dim_labels b ON a.to_address = b.address

WHERE b.LABEL_TYPE != 'chadmin'

AND b.project_name IS NOT NULL

AND a.status = 'SUCCESS'

AND NOT project_name ilike any ('%Circle%', '%Tether%', '%Weth%')

GROUP BY 2, 3

),

totals AS (

SELECT

COUNT(DISTINCT FROM_ADDRESS) AS total_users,

COUNT(DISTINCT TX_HASH) AS total_transactions

FROM base.core.fact_transactions