Quick Overview

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.

Find returning users from access logs

Company: Amazon

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

Overview: 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.

Read the full Amazon Software Engineer interview experience this question came from

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.

Tables

access_logs(log_id INT, user_id INT, access_time_utc TIMESTAMP, url VARCHAR(255))

Hints

  1. Use the date portion of the timestamp to define a calendar day (e.g., CAST(access_time_utc AS DATE)).
  2. Group by user_id and count DISTINCT days; filter with HAVING to keep users with at least two distinct days.

Loading coding console...