SELECT

DATE(block_timestamp) AS login_date,

origin_from_address AS user_address

FROM kaia.core.fact_event_logs

WHERE contract_address = '0xa7b118f157ea93d22d0ed1608e5c3116e27de28c' -- IXS Academy (IxSwap)

AND topics[0] = '0xda763d657043a37d5b6727921f1afb11f752742cbbd2590d990a8d3b9ff357ae'

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;