Katkat
Back

Find the most-used app

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.