Coding questions · Amazon
Find returning users from access logs
This question evaluates a candidate's ability to parse and aggregate access logs, handle date/time normalization and deduplication across calendar days, and design memory-efficient, scalable solutions using SQL or Python.
Given a large user access log, parse it and identify which user_ids are returning customers—i.e., they have at least one visit on two or more distinct calendar days. Assume log lines contain an ISO 8601 timestamp, user_id, and URL; if the format is different, state your assumptions. Implement a solution in Python or SQL. Address: time‑zone normalization, deduping multiple hits on the same day, memory for large files (streaming vs batch), and complexity. Provide unit tests and example input/output.
Identify Returning Users from Access Logs
You are given a large user access log stored in a relational database. Each row represents a single HTTP request and includes a UTC timestamp, the user_id, and the URL accessed. A "returning customer" is defined as a user who has at least one visit on two or more distinct calendar days (calendar days are computed in UTC).
Write an SQL query to return all user_ids that are returning customers, along with the count of distinct calendar days on which they visited. Multiple hits from the same user on the same calendar day should be counted only once toward this distinct-day count.
Assumptions:
- All timestamps are already normalized to UTC.
- A calendar day is defined by the date portion of the UTC timestamp.
Your result should include one row per returning user.
Table access_logs
| column | type | constraints |
|---|---|---|
| log_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| access_time_utc | TIMESTAMP | NOT NULL |
| url | VARCHAR(255) | NOT NULL |
Sample data:
| access_time_utc | log_id | url | user_id |
|---|---|---|---|
| 2025-05-01 10:15:00 | 1 | /home | 101 |
| 2025-05-01 11:00:00 | 2 | /product/1 | 101 |
| 2025-05-03 09:00:00 | 3 | /home | 101 |
| 2025-05-02 12:00:00 | 4 | /home | 102 |
| 2025-05-02 13:00:00 | 5 | /contact | 102 |
| 2025-05-01 23:00:00 | 6 | /home | 103 |
| 2025-05-02 01:00:00 | 7 | /product/2 | 103 |
| 2025-05-05 08:00:00 | 8 | /home | 104 |
| 2025-05-05 09:00:00 | 9 | /search | 104 |
| 2025-05-05 10:00:00 | 10 | /product/3 | 104 |
| 2025-05-10 07:00:00 | 11 | /home | 105 |
| 2025-05-15 18:30:00 | 12 | /product/5 | 105 |
| 2025-05-15 19:00:00 | 13 | /cart | 105 |
Expected output
One row per user who visited on at least two distinct UTC calendar days, including the number of distinct visit days.
| distinct_visit_days | user_id |
|---|---|
| 2 | 101 |
| 2 | 103 |
| 2 | 105 |
Hints
- Use the date portion of the timestamp to define a calendar day (e.g., CAST(access_time_utc AS DATE)).
- Group by user_id and count DISTINCT days; filter with HAVING to keep users with at least two distinct days.