-- Identify the first login date for each user

SELECT

from_address,

MIN(DATE_TRUNC('day', block_timestamp)) AS first_login_date

FROM ronin.core.fact_transactions

WHERE

to_address = '0x02790f32ad7e7eaaecfb0ad21950829932f1a2ee'

AND tx_succeeded = true

GROUP BY from_address

),

daily_logins AS (

-- Get all successful logins

SELECT

DATE_TRUNC('day', block_timestamp) AS login_date,

from_address

FROM ronin.core.fact_transactions

WHERE

to_address = '0x02790f32ad7e7eaaecfb0ad21950829932f1a2ee'

AND tx_succeeded = true

SELECT

dl.login_date,

COUNT(DISTINCT dl.from_address) AS total_users,

COUNT(DISTINCT CASE WHEN dl.login_date = fl.first_login_date THEN dl.from_address END) AS new_users,

COUNT(DISTINCT CASE WHEN dl.login_date > fl.first_login_date THEN dl.from_address END) AS returning_users

FROM daily_logins dl

LEFT JOIN first_login fl

ON dl.from_address = fl.from_address

GROUP BY dl.login_date

ORDER BY dl.login_date DESC;