User purchase platform

SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 20 minutes. Part of the Pro drill bank.

user_spending records daily customer transactions across platforms with columns user_id, spend_date, platform ('mobile' or 'desktop'), and amount. Categorize each user on each date into one of three platform groups: 'mobile': bought exclusively on mobile that day 'desktop': bought exclusively on desktop that day 'both': bought on both mobile and desktop on that day For each date and each of the three platform categories, report total_amount spent and total_users. If no user falls into a category on a date, report total_amount as 0 and total_users as 0. Order by spend_date, then platform in the order mobile, desktop, both.

Requirements

  • Output columns: spend_date, platform, total_amount, total_users.
  • Order by spend_date ascending, platform order ('mobile', 'desktop', 'both').

Constraints

  • platform values in input are strictly 'mobile' or 'desktop'.
  • amount is a positive number.
  • Every distinct date present in the input must produce all three platform rows.

Examples

Input: user_spending user_id,spend_date,platform,amount 1,2024-07-01,mobile,100 1,2024-07-01,desktop,100 2,2024-07-01,mobile,100 2,2024-07-02,mobile,100 3,2024-07-01,desktop,100 3,2024-07-02,desktop,100 Output: spend_date | platform | total_amount | total_users 2024-07-01 | mobile | 100 | 1 2024-07-01 | desktop | 100 | 1 2024-07-01 | both | 200 | 1 2024-07-02 | mobile | 100 | 1 2024-07-02 | desktop | 100 | 1 2024-07-02 | both | 0 | 0 Why this passes: On 2024-07-01 user 1 used both platforms (200 total), user 2 only mobile and user 3 only desktop; on 07-02 nobody used both, so the 'both' row is still emitted with 0.

Input: user_spending user_id | spend_date | platform | amount 1 | 2024-08-01 | mobile | 30 Output: spend_date | platform | total_amount | total_users 2024-08-01 | mobile | 30 | 1 2024-08-01 | desktop | 0 | 0 2024-08-01 | both | 0 | 0 Why this passes: Only mobile spend occurred, desktop and both appear with 0.

Topics: lakebench, sql, scaffold, cross-join.

More SQL interview questions · All interview problems · Learn data engineering

advanced

User purchase platform

Interview-style drill: Aggregate spending into mobile-only, desktop-only, and both platforms, preserving all category rows with zero fills.

`user_spending` records daily customer transactions across platforms with columns `user_id`, `spend_date`, `platform` (`'mobile'` or `'desktop'`), and `amount`. Categorize each user on each date into one of three platform groups: - `'mobile'`: bought exclusively on mobile that day - `'desktop'`: bought exclusively on desktop that day - `'both'`: bought on both mobile and desktop on that day For each date and each of the three platform categories, report `total_amount` spent and `total_users`. If no user falls into a category on a date, report `total_amount` as 0 and `total_users` as 0. Order by `spend_date`, then platform in the order mobile, desktop, both.