Find the most-used app
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 unless otherwise specified)
- `app_id` (INT)
- `session_id` (VARCHAR)
- `duration` (INT) — session duration in seconds
### `apps`
- `app_id` (INT, PK)
- `app_name` (VARCHAR)
- `app_category` (VARCHAR)
Relationship: `user_activity.app_id` → `apps.app_id`.
## Task
For the **last 7 days** (inclusive) relative to a provided `:as_of_date`, identify the **“most used app.”**
1) Write a SQL query that returns the most-used app using **one clear definition** of “most used” (e.g., **number of sessions** or **total time spent**).
2) In 2–3 sentences, explain the pros/cons of at least **two** plausible definitions (e.g., sessions vs. total time vs. unique users).
### Output
Return exactly one row with at least:
- `app_id`
- `app_name`
- your chosen usage metric (e.g., `session_cnt` or `total_duration_seconds`)
Assume ties do **not** need a deterministic tie-breaker unless you choose to add one.
Quick Answer: This question evaluates data manipulation and metrics-definition skills, emphasizing SQL and Python proficiency for aggregating user activity and joining app metadata within analytics workflows.