Coding questions · Amazon
Calculate cross-channel login user proportions
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.
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).
Calculate cross-channel login user proportions
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.
Table mobile_logins
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| login_dt | DATE | NOT NULL |
Sample data:
| login_dt | user_id |
|---|---|
| 2025-08-30 | 1 |
| 2025-08-31 | 1 |
| 2025-08-31 | 1 |
| 2025-08-31 | 2 |
| 2025-08-30 | 3 |
| 2025-08-28 | 5 |
| 2025-09-01 | 6 |
Table desktop_logins
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| login_dt | DATE | NOT NULL |
Sample data:
| login_dt | user_id |
|---|---|
| 2025-08-31 | 1 |
| 2025-08-31 | 4 |
| 2025-08-29 | 3 |
| 2025-08-31 | 3 |
| 2025-08-31 | 3 |
| 2025-08-27 | 5 |
| 2025-09-01 | 7 |
Expected output
One row per segment showing how many distinct users in 2025-08-29 to 2025-08-31 fell into each of the three categories (only_mobile, only_desktop, both), and the proportion of all active users in the window that each segment represents, rounded to two decimal places. Based on the sample data, users {1,2,3,4} are active in the window: user 2 is only_mobile, user 4 is only_desktop, and users 1 and 3 used both channels.
| pct_of_users | segment | user_count |
|---|---|---|
| 0.5 | both | 2 |
| 0.25 | only_desktop | 1 |
| 0.25 | only_mobile | 1 |
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.