Compute time-spent percentage by app category
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
You work on Oculus app engagement analytics.
## Tables
### `user_activity`
- `user_id` (BIGINT)
- `date` (DATE) — day of activity (assume UTC)
- `app_id` (INT)
- `session_id` (VARCHAR)
- `duration` (INT) — session duration in seconds
### `apps`
- `app_id` (INT, PK)
- `app_name` (VARCHAR)
- `app_category` (VARCHAR) — expected to be one of a small set of categories, but may be NULL
Relationship: `user_activity.app_id` → `apps.app_id`.
## Task
For the **last 7 days** (inclusive) relative to `:as_of_date`, compute the **percentage of total time spent** in each `app_category`.
### Requirements
- Join activity to `apps` to obtain `app_category`.
- If an `app_id` in `user_activity` has no matching row in `apps`, treat the category as `NULL` (or label it as `'Unknown'`, but be consistent).
- Percent should be computed as:
\( \text{pct} = \frac{\text{category_duration}}{\text{total_duration}} \)
### Output
One row per category (including `NULL`/Unknown if applicable) with:
- `app_category`
- `category_duration_seconds`
- `pct_time_spent` (0–1 or 0–100, specify which in your query)
Overview: This question evaluates proficiency in data manipulation and analytics, specifically SQL/Python skills for joining tables, aggregating session durations, handling missing app-category mappings, and computing percentage time spent over a recent seven-day window.