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.
Requirements
- Find each user's most recent login, including users who have never logged in.
- Treat a user as inactive when their latest login is before
2025-01-02 12:00:00+00, or when they have no login record.
- Include users with at least one content interaction from
2025-01-25 12:00:00+00 through, but not including, the report timestamp.
- Return the user ID, name, latest login, recent interaction count, and distinct content types in alphabetical order.
- Sort the final result by recent interaction count descending, then user ID ascending.
