Self-Join Time Period Comparison | Dataford Interview Questions

How would you perform a self-join to compare user activity across different time periods?

Use the user_activity table to compare each user's activity in one month against the previous month. Return only users who were active in both periods.

Output

One row per user and month pair
Columns: user_id, current_month, current_activity, previous_month_activity, activity_change
Sort by user_id, then current_month

Schema

user_activity

Column	Type	Description
activity_idPK	INT	Unique row identifier
user_id	INT	User identifier
activity_month	DATE	Month bucket for activity, stored as the first day of the month
activity_count	INT	Number of active days or actions in the month

Your question is Self-Join Time Period Comparison. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem