Compute time-spent percentage by app category

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)