Quick Overview

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.

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.

Overview: 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.

Most-used app in the last 7 days (by total time spent)

You are given two tables: - user_activity: one row per app session with its duration - apps: app metadata Define “most used app” as the app with the highest total time spent (sum of duration_minutes) during the last 7 days: FROM 2025-05-26 TO 2025-06-01 (inclusive). Return the app_id, app_name, and total_duration_minutes for the most-used app. If there is a tie, you may return any of the tied apps.

Tables

apps(app_id INT, app_name VARCHAR(100), app_category VARCHAR(50))

user_activity(session_id VARCHAR(20), user_id INT, activity_date DATE, app_id INT, duration_minutes INT)

Hints

  1. Filter to the 7-day window first, then aggregate.
  2. Order by the aggregated total duration and pick the top row.

Time-spent percentage by app category (last 7 days)

Compute the percentage of total time spent (duration_minutes) for each app_category during the last 7 days: FROM 2025-05-26 TO 2025-06-01 (inclusive). Rules: - Join user_activity to apps to get app_category. - Some app_id values in user_activity may not exist in apps; treat those as app_category = 'Unknown'. Return one row per category with: - app_category - category_duration_minutes - pct_of_total_time (percentage from 0 to 100, rounded to 2 decimals).

Tables

apps(app_id INT, app_name VARCHAR(100), app_category VARCHAR(50))

user_activity(session_id VARCHAR(20), user_id INT, activity_date DATE, app_id INT, duration_minutes INT)

Hints

  1. Use a LEFT JOIN so activity with missing app metadata is retained.
  2. Compute category totals in a CTE, then use a window SUM to get the overall denominator.

Compare engagement for social-dominant vs game-dominant users (last 7 days)

A PM hypothesizes that people who use social apps are more engaged than people who use game apps. Use the last 7 days: FROM 2025-05-26 TO 2025-06-01 (inclusive). Define: - Engagement metric per user = (number of sessions in the 7-day window) / 7. - User classification based on time spent (duration_minutes) in the 7-day window: - 'social_dominant' if social time >= 80% of the user's total time - 'game_dominant' if game time >= 80% of the user's total time - otherwise 'mixed' - Ignore 'Unknown' and other categories for dominance checks, but they still count toward the user's total time. Return one row per user_group with: - user_group - user_count - avg_sessions_per_day (average of the per-user engagement metric, rounded to 2 decimals).

Tables

apps(app_id INT, app_name VARCHAR(100), app_category VARCHAR(50))

user_activity(session_id VARCHAR(20), user_id INT, activity_date DATE, app_id INT, duration_minutes INT)

Hints

  1. Aggregate to the user level first (total minutes, social minutes, game minutes, sessions).
  2. Classify users with a CASE statement, then aggregate again to compare groups.

How common are unhealthy users (last 30 days)?

Define an 'unhealthy user' as someone who: - spent more than 8 hours per day (> 480 minutes) on the platform on a given day, AND - had more than 10 such days Evaluate this in the last 30 days: FROM 2025-05-03 TO 2025-06-01 (inclusive). Return: - unhealthy_user_count - total_active_users (users with any activity in the window) - unhealthy_user_pct (unhealthy_user_count / total_active_users * 100, rounded to 2 decimals).

Tables

apps(app_id INT, app_name VARCHAR(100), app_category VARCHAR(50))

user_activity(session_id VARCHAR(20), user_id INT, activity_date DATE, app_id INT, duration_minutes INT)

Hints

  1. First roll up to user-day totals, then apply the > 480 minutes rule.
  2. Count qualifying days per user and then count users with days_over_8h > 10.

Loading coding console...