cswork / libraryOpen practice workspace

Coding questions · Amazon

Analyze User Engagement with SQL Queries

This question evaluates proficiency in SQL data manipulation, including aggregation, window functions, joins, and temporal calculations for analyzing event and session logs.

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.

Count Events per User

Using the events table, return the total number of events generated by each user_id.

Table events

columntypeconstraints
event_idINTEGERPRIMARY KEY, NOT NULL
user_idINTEGERNOT NULL
event_timeDATETIMENOT NULL

Sample data:

event_idevent_timeuser_id
12023-01-01 09:00:00101
22023-01-01 10:30:00101
32023-01-02 08:15:00102
42023-01-03 12:45:00103

Table sessions

columntypeconstraints
session_idINTEGERPRIMARY KEY, NOT NULL
user_idINTEGERNOT NULL
session_startDATETIMENOT NULL
session_endDATETIMENOT NULL

Sample data:

session_endsession_idsession_startuser_id
2023-01-01 10:45:0010012023-01-01 08:45:00101
2023-01-02 09:00:0010022023-01-02 08:00:00102
2023-01-03 13:30:0010032023-01-03 08:30:00103

Expected output

One row per user with the total number of events they generated.

event_countuser_id
2101
1102
1103

Hints

  1. Group by user_id to aggregate counts.
  2. 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.

Table events

columntypeconstraints
event_idINTEGERPRIMARY KEY, NOT NULL
user_idINTEGERNOT NULL
event_timeDATETIMENOT NULL

Sample data:

event_idevent_timeuser_id
12023-01-01 09:00:00101
22023-01-01 10:30:00101
32023-01-02 08:15:00102
42023-01-03 12:45:00103

Table sessions

columntypeconstraints
session_idINTEGERPRIMARY KEY, NOT NULL
user_idINTEGERNOT NULL
session_startDATETIMENOT NULL
session_endDATETIMENOT NULL

Sample data:

session_endsession_idsession_startuser_id
2023-01-01 10:45:0010012023-01-01 08:45:00101
2023-01-02 09:00:0010022023-01-02 08:00:00102
2023-01-03 13:30:0010032023-01-03 08:30:00103

Expected output

For each user, the earliest and latest event_time values in the events table.

first_event_timelast_event_timeuser_id
2023-01-01 09:00:002023-01-01 10:30:00101
2023-01-02 08:15:002023-01-02 08:15:00102
2023-01-03 12:45:002023-01-03 12:45:00103

Hints

  1. Group by user_id.
  2. Use MIN(event_time) and MAX(event_time).
  3. Use TO_CHAR for PostgreSQL timestamp formatting.

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.

Table events

columntypeconstraints
event_idINTEGERPRIMARY KEY, NOT NULL
user_idINTEGERNOT NULL
event_timeDATETIMENOT NULL

Sample data:

event_idevent_timeuser_id
12023-01-01 09:00:00101
22023-01-01 10:30:00101
32023-01-02 08:15:00102
42023-01-03 12:45:00103

Table sessions

columntypeconstraints
session_idINTEGERPRIMARY KEY, NOT NULL
user_idINTEGERNOT NULL
session_startDATETIMENOT NULL
session_endDATETIMENOT NULL

Sample data:

session_endsession_idsession_startuser_id
2023-01-01 10:45:0010012023-01-01 08:45:00101
2023-01-02 09:00:0010022023-01-02 08:00:00102
2023-01-03 13:30:0010032023-01-03 08:30:00103

Expected output

One row: the user with the longest single session and its duration in whole minutes.

duration_minutesuser_id
300103

Hints

  1. In PostgreSQL, subtracting two timestamps gives an interval, not a number — TIMESTAMPDIFF does not exist here.
  2. Use EXTRACT(EPOCH FROM (session_end - session_start)) to get seconds, then divide by 60 for minutes.
  3. Order by duration descending and use LIMIT 1; add a tie-breaker on user_id to make the result deterministic.