Analyze User Engagement with SQL Queries
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
events
+----------+---------+---------------------+
| event_id | user_id | event_time |
+----------+---------+---------------------+
| 1 | 101 | 2023-01-01 09:00:00 |
| 2 | 101 | 2023-01-01 10:30:00 |
| 3 | 102 | 2023-01-02 08:15:00 |
| 4 | 103 | 2023-01-03 12:45:00 |
+----------+---------+---------------------+
sessions
+-----------+---------+---------------------+---------------------+
| session_id| user_id | session_start | session_end |
+-----------+---------+---------------------+---------------------+
| 1001 | 101 | 2023-01-01 08:45:00 | 2023-01-01 10:45:00 |
| 1002 | 102 | 2023-01-02 08:00:00 | 2023-01-02 09:00:00 |
| 1003 | 103 | 2023-01-03 08:30:00 | 2023-01-03 13:30:00 |
+-----------+---------+---------------------+---------------------+
##### Scenario
You are a data analyst exploring product-usage logs and session metadata to understand user engagement.
##### Question
Q1. Using table events, write a SQL query that returns the total number of events generated by each user (user_id). Q2. Using table events, return for every user_id the earliest event_time and the latest event_time observed. Q3. Using tables events and sessions, write a SQL query that finds the user_id whose single session has the longest duration (session_end – session_start); return both the user_id and that duration.
##### Hints
Window functions, GROUP BY, TIMESTAMPDIFF; join events e ON e.user_id = s.user_id when needed.
Overview: This question evaluates proficiency in SQL data manipulation, including aggregation, window functions, joins, and temporal calculations for analyzing event and session logs.
Count Events per User
Using the events table, return the total number of events generated by each user_id.
Tables
events(event_id INTEGER, user_id INTEGER, event_time DATETIME)
sessions(session_id INTEGER, user_id INTEGER, session_start DATETIME, session_end DATETIME)
Hints
- Group by user_id to aggregate counts.
- Use COUNT(*) to count events.
First and Last Event Time by User
Using the `events` table, return each user's earliest and latest event timestamps.
Return one row per `user_id` with these columns:
- `user_id`
- `first_event_time`: the earliest `event_time`, formatted as `YYYY-MM-DD HH24:MI:SS`
- `last_event_time`: the latest `event_time`, formatted as `YYYY-MM-DD HH24:MI:SS`
Order the result by `user_id`.
Tables
events(event_id INTEGER, user_id INTEGER, event_time DATETIME)
sessions(session_id INTEGER, user_id INTEGER, session_start DATETIME, session_end DATETIME)
Hints
- Group by user_id.
- Use MIN(event_time) and MAX(event_time).
Longest Single Session Duration
## Longest Single Session Duration
Using the `sessions` table, find the single session with the **longest duration** and report which user it belongs to along with how long it lasted.
**Input table:** `sessions(session_id, user_id, session_start, session_end)` — one row per session, with the session's start and end timestamps.
**Return:** exactly one row with two columns:
- `user_id` — the user who owns the longest session.
- `duration_minutes` — that session's duration in **whole minutes**, computed as `session_end - session_start`.
If two sessions are tied for the longest duration, return the one with the **smaller** `user_id`. Order the result by `duration_minutes` descending, then by `user_id` ascending, and return only the top row.
Tables
events(event_id INTEGER, user_id INTEGER, event_time DATETIME)
sessions(session_id INTEGER, user_id INTEGER, session_start DATETIME, session_end DATETIME)
Hints
- In PostgreSQL, subtracting two timestamps gives an interval, not a number — `TIMESTAMPDIFF` does not exist here.
- Use `EXTRACT(EPOCH FROM (session_end - session_start))` to get seconds, then divide by 60 for minutes.