Schema Design for User Activity

Tell me about how you would design a schema for a new user-activity tracking feature to ensure low-latency querying at AWS.

For this hands-on exercise, use the provided tables to represent the schema and write the query needed to retrieve a recent activity summary. Restrict activity to the inclusive start and exclusive end timestamps specified in the query.

Output

1. One row per user, including users with no activity in the time range.
2. Columns: user_id, email, activity_count, categories_seen, latest_activity_at, and latest_activity_name.
3. Order by activity count descending, latest activity descending with nulls last, then user ID ascending.

Output details

• Use the inclusive lower bound TIMESTAMP '2026-10-01 00:00:00' and exclusive upper bound TIMESTAMP '2026-10-08 00:00:00'.
• Count activity events, returning 0 for users with no events in the range.
• Count distinct non-NULL activity categories, returning 0 when none exist.
• Break latest-activity ties by event_id descending.
• Preserve NULL latest_activity_name values when the latest event has no activity name.