Calculate cross-channel login user proportions
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Write SQL to compute, for 2025-08-29 through 2025-08-31, the proportion of users who logged in only via mobile, only via desktop, and via both, where the denominator is distinct users who used at least one channel in the window. Tables and sample data:
Schemas:
mobile_logins(user_id INT, login_dt DATE)
desktop_logins(user_id INT, login_dt DATE)
Sample rows:
mobile_logins
user_id | login_dt
1 | 2025-08-30
1 | 2025-08-31
2 | 2025-08-31
3 | 2025-08-30
desktop_logins
user_id | login_dt
1 | 2025-08-31
4 | 2025-08-31
3 | 2025-08-29
3 | 2025-08-31
Requirements: (a) deduplicate users within each channel; (b) avoid double-counting users in the denominator; (c) return counts and percentages with two decimals for each segment {only_mobile, only_desktop, both}; (d) handle very large tables efficiently (no full cross joins), and be portable to ANSI SQL. Explain corner cases you handled (users appearing in neither channel; users with multiple same-day logins; users whose activity spans outside the window).
Overview: This question evaluates data manipulation (SQL/Python) skills, focusing on cross-channel user attribution, deduplication, distinct user counting, date-window filtering, join logic, efficient aggregation for large tables, and formatted output of counts and percentages in ANSI SQL-compatible queries.
Read the full Amazon Data Scientist interview experience this question came from
Using the tables below, write an ANSI SQL query to compute, for the date range 2025-08-29 through 2025-08-31 (inclusive), the proportion of users who logged in only via mobile, only via desktop, and via both channels. The denominator should be the number of distinct users who used at least one channel in this window.
Requirements:
(a) Deduplicate users within each channel within the date window.
(b) Avoid double-counting users in the denominator (count each user at most once, even if they used multiple channels).
(c) Return one row per segment with the following columns: segment (one of {"only_mobile", "only_desktop", "both"}), user_count, and pct_of_users, where pct_of_users is the proportion of users in that segment with two decimal places.
(d) Handle very large tables efficiently (no full cross joins of the raw login tables), and keep the solution portable ANSI SQL.
(e) Briefly explain how your query handles these corner cases: users appearing in neither channel, users with multiple same-day logins, and users whose activity falls outside the 2025-08-29 to 2025-08-31 window.
Tables
mobile_logins(user_id INT, login_dt DATE)
desktop_logins(user_id INT, login_dt DATE)
Hints
- First, filter to the date range and SELECT DISTINCT user_id in each channel in separate CTEs.
- Build the three segments by taking the UNION of user_ids from both channels and left joining back to the per-channel CTEs, then aggregate and divide by the total distinct user count to get proportions.