Classify each user active this month as new or returning in one query
Table activity(user_id, activity_date) has one row per active user-day. For every user who was active in the current calendar month, label them new if their first-ever activity is in this month, or returning if they were active before it. Return one row per such user.
-- per active-this-month user: 'new' or 'returning'Write the query.
GROUP BY user_id over all activity. A user is new when MIN(activity_date) lands in the current month, else returning. Restrict output to users active this month with HAVING MAX(activity_date) >= date_trunc('month', CURRENT_DATE). Their first-ever activity decides the label.
- ✗Deciding new vs returning by visit count this month
- ✗Using MAX(activity_date) instead of MIN for the first-seen check
- ✗Filtering to this month before computing the all-time first activity
- →Why must the first-seen check run over all history, not just this month?
- →How would you add a third
resurrectedlabel for lapsed users?
Group once over the user's whole history so the first-ever activity is available, then decide the label from it and keep only users seen this month:
SELECT user_id,
CASE WHEN MIN(activity_date) >= date_trunc('month', CURRENT_DATE)::date
THEN 'new' ELSE 'returning' END AS user_type
FROM activity
GROUP BY user_id
HAVING MAX(activity_date) >= date_trunc('month', CURRENT_DATE)::date;MIN(activity_date) is the user's first-ever active day; if that day is in the current month, this month is their debut → new. HAVING MAX(...) keeps only users with at least one activity this month. The subtle trap: filtering to the current month in WHERE before aggregating would make every user look brand-new, because the pre-month history it needs would already be gone.
