SELECT

DATE_TRUNC('year', BLOCK_TIMESTAMP) AS activity_year,

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS activity_date,

origin_from_address AS address

FROM bsc.defi.ez_dex_swaps

WHERE 1=1

AND BLOCK_TIMESTAMP >= '2024-01-01'

AND PLATFORM LIKE '%pancakeswap%'

AND EVENT_NAME = 'Swap'

),

FirstTransactionDates AS (

SELECT

origin_from_address AS address,

DATE_TRUNC('year', MIN(BLOCK_TIMESTAMP)) AS first_transaction_year,

MIN(DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS first_transaction_date

FROM bsc.defi.ez_dex_swaps

WHERE 1=1

AND BLOCK_TIMESTAMP >= '2024-01-01'

AND PLATFORM LIKE '%pancakeswap%'

AND EVENT_NAME = 'Swap'

GROUP BY origin_from_address

UA.activity_date,

COUNT(DISTINCT UA.address) AS "ACTIVE USERS",

COUNT(DISTINCT CASE WHEN FTD.first_transaction_date = UA.activity_date THEN FTD.address END) AS "NEW USERS",

"ACTIVE USERS" - "NEW USERS" AS "RETURNING USERS"

FROM UserActivity UA

LEFT JOIN FirstTransactionDates FTD

ON UA.address = FTD.address AND UA.activity_year = FTD.first_transaction_year

WHERE UA.activity_year >= '2024-01-01'

GROUP BY UA.activity_date

ORDER BY UA.activity_date;