SELECT

DATE(block_timestamp) AS login_date,

CONCAT('0x', SUBSTR(data :: STRING, 27, 40)) AS user_address

FROM kaia.core.fact_event_logs

WHERE contract_address = '0xbefbbc4b4938577a9c9052c1e08767428bd0a8af' -- Axolt's Escape: Daily Bonus

AND topics[0] = '0x719e060b9f422388acb7301042bcf38cc86a8986c5660ce6b7ba57445ff5ce8c'

AND tx_succeeded = TRUE

GROUP BY 1,2

),

First_login AS (

SELECT

user_address,

MIN(login_date) AS first_login_date

FROM Daily_login

GROUP BY user_address

SELECT

d.login_date,

COUNT(DISTINCT CASE WHEN d.login_date = f.first_login_date THEN d.user_address END) AS new_users,

COUNT(DISTINCT CASE WHEN d.login_date > f.first_login_date THEN d.user_address END) AS returning_users

FROM Daily_login d

LEFT JOIN First_login f ON d.user_address = f.user_address

GROUP BY d.login_date

ORDER BY d.login_date;