Amazon·Data Scientist·Technical Phone Screen·Senior

Senior

Aug 2025Remote

Summary

SQL-heavy technical screen for a Data Scientist role at Amazon. One question, but it had enough layers to keep me busy for the whole session.

Questions Asked (1)

Write a SQL query that computes, for a specific three-day window, the proportion of users who logged in only via mobile, only via desktop, and via both channels. The denominator should be distinct users who used at least one channel during that period. Tables are mobile_logins and desktop_logins, each with user_id and login_dt. Return counts and percentages rounded to two decimals, and explain corner cases you handled.

Data ModelingProduct Analytics & MetricsTechnical Trade-offs

Author's notes

This looked like a clean aggregation problem until I started thinking about the denominator.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the exact three-day window and the definition of 'only mobile', 'only desktop', and 'both' using set logic on distinct user IDs. Then write a single SQL query that aggregates distinct users per channel, computes the intersections and differences, and calculates counts and percentages with proper rounding. Finally, discuss corner cases such as users with multiple logins, timezone handling, and nulls.

Pro tip: Explicitly state your assumptions about the time window boundaries (inclusive/exclusive) and timezone, and show how you'd validate the query with edge cases like users appearing in both tables or having multiple logins—this demonstrates production-level rigor.

1. Clarify requirements and assumptions

Confirm the exact three-day window (e.g., '2023-01-01' to '2023-01-03'), whether boundaries are inclusive, and the timezone. Define 'only mobile' as users in mobile_logins but not in desktop_logins, 'only desktop' as the reverse, and 'both' as the intersection.

2. Extract distinct users per channel

Use subqueries or CTEs to get distinct user_ids from mobile_logins and desktop_logins within the window. This handles duplicate logins and ensures each user is counted once per channel.

3. Compute set intersections and differences

Use INNER JOIN for 'both', LEFT JOIN with NULL check for 'only mobile', and RIGHT JOIN or equivalent for 'only desktop'. Alternatively, use set operators like INTERSECT and EXCEPT if supported.

4. Calculate counts and percentages

Count users in each category and compute percentages relative to the total distinct users across both channels. Round percentages to two decimals using ROUND().

5. Address corner cases and validate

Handle NULL user_ids (exclude them), timezone conversions (use AT TIME ZONE if needed), and users with multiple logins (already handled by DISTINCT). Validate by manually checking a small sample or using a temporary table.

Key Points to Mention

Use DISTINCT to deduplicate users per channel before set operations.
Define the denominator as the count of distinct users who appear in either mobile_logins or desktop_logins within the window.
Use LEFT JOIN / RIGHT JOIN or set operators (INTERSECT, EXCEPT) to compute the three segments.
Round percentages to two decimals with ROUND(..., 2) and ensure counts are integers.
Handle timezone differences by converting login_dt to a consistent timezone (e.g., UTC) before filtering.
Exclude NULL user_ids and consider users with no logins (they are not in the denominator).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.