Calculate cross-channel login user proportions

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Amazon during technical interviews.

Question

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).

PracHub · Accepted Answer

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.

Quick Overview

Calculate cross-channel login user proportions