Objective

You are given a table of user login records. Each row represents a single login event with the user ID and the date they logged in.

Table Schema:

The user_logins table:

Task Requirements:

• A reactivated user is one who logged in during the current month, did not log in during the previous month, but had logged in during some earlier month
• For each month, count the number of reactivated users
• Do not count brand new users who have no login history before the current month
• Months with zero reactivated users should not appear in the output
• Order the results by month in ascending order

Output columns: month, reactivated_users

WITH monthly_users AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', login_date)::date AS month
    FROM user_logins
)
SELECT
    curr.month,
    COUNT(*) AS reactivated_users
FROM monthly_users curr
LEFT JOIN monthly_users prev
    ON curr.user_id = prev.user_id
    AND prev.month = (curr.month - INTERVAL '1 month')::date
WHERE prev.user_id IS NULL
AND EXISTS (
    SELECT 1 FROM monthly_users earlier
    WHERE earlier.user_id = curr.user_id
    AND earlier.month < curr.month
)
GROUP BY curr.month
ORDER BY curr.month;

Explanation

Step 1: Build distinct user-month pairs

The CTE extracts distinct user_id and month combinations from the login data, deduplicating multiple logins by the same user within a month.

SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', login_date)::date AS month
FROM user_logins

Step 2: LEFT JOIN to check previous month activity

The LEFT JOIN matches each user-month pair with the same user in the previous month. When prev.user_id IS NULL, the user was not active in the previous month.

LEFT JOIN monthly_users prev
    ON curr.user_id = prev.user_id
    AND prev.month = (curr.month - INTERVAL '1 month')::date
WHERE prev.user_id IS NULL

Step 3: Exclude new users with EXISTS

The EXISTS subquery ensures the user had at least one login in an earlier month, distinguishing reactivated users from brand new users.

AND EXISTS (
    SELECT 1 FROM monthly_users earlier
    WHERE earlier.user_id = curr.user_id
    AND earlier.month < curr.month
)