Your question is SQL for 30-Day Inactive Engagers. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
MoEngage wants to identify users who are inactive but recently engaged with content, so Marketing Analytics can build a re-engagement audience.
Assume the report timestamp is 2025-02-01 12:00:00+00. Write a PostgreSQL query that returns qualifying users.
2025-01-02 12:00:00+00, or when they have no login record.2025-01-25 12:00:00+00 through, but not including, the report timestamp.| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique user identifier |
| user_name | VARCHAR(100) | User display name |
| Column | Type | Description |
|---|---|---|
| login_idPK | INTEGER | Unique login event identifier |
| user_id | INTEGER | User associated with the login |
| logged_in_at | TIMESTAMPTZ | Timestamp when the login occurred |
| Column | Type | Description |
|---|---|---|
| interaction_idPK | INTEGER | Unique content interaction identifier |
| user_id | INTEGER | User associated with the interaction |
| interacted_at | TIMESTAMPTZ | Timestamp when the content was interacted with |
| content_type | VARCHAR(50) | Type of content interacted with |