Find the most-used app

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.