Pivot daily users and revenue by platform
Company: Intuit
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are given transaction-level data and need daily aggregates by platform.
## Input (Pandas DataFrame)
`df` with columns:
- `file_date` (DATE or string parseable to date) — the activity date
- `id` (INT/STRING) — user id
- `sku` (STRING) — product identifier
- `price` (NUMERIC) — revenue for the row (assume already in a single currency)
- `channel` (STRING)
- `customer_segment` (STRING)
- `platform` (STRING) — e.g., `'web'`, `'mobile'`
Example rows:
- `01-01-2024, 123, A, 57, seo, alpha, web`
- `01-07-2024, 943, C, 100, tv, alpha, mobile`
## Task
Compute, for each `file_date` and `platform`:
- `total_users` = number of **distinct** `id`
- `total_revenue` = sum of `price`
Then reshape the result into a **pivoted/wide** daily report with one row per day and separate columns per platform:
- `file_date`
- `web_total_users`, `web_total_revenue`
- `mobile_total_users`, `mobile_total_revenue`
(If additional platforms exist, include them similarly.)
Overview: This question evaluates data manipulation and aggregation skills, specifically computing distinct user counts and revenue aggregates and reshaping results by platform; it is in the Data Manipulation (SQL/Python) domain and assesses practical application rather than purely conceptual understanding.
Compute daily distinct users and total revenue by platform, then pivot to a wide format with one row per day and separate columns per platform (e.g., web_total_users, web_total_revenue, mobile_total_users, mobile_total_revenue).
Tables
df(file_date DATE, id INTEGER, sku VARCHAR, price DECIMAL(10,2), channel VARCHAR, customer_segment VARCHAR, platform VARCHAR)
Hints
- First aggregate by file_date and platform to compute COUNT(DISTINCT id) and SUM(price).
- Use conditional aggregation (CASE WHEN platform = 'web' THEN ... END) to pivot into wide columns.