Analyze Oculus App Engagement with SQL Queries

AppUsage

+---------+--------+-----------+--------------+------------+
| user_id | app_id | category  | minutes_spent| usage_date |
+---------+--------+-----------+--------------+------------+
| 101     | 12     | Social    | 45           | 2023-04-15 |
| 102     | 17     | Game      | 30           | 2023-04-16 |
| 101     | 17     | Game      | 20           | 2023-04-17 |
| 103     | 22     | Fitness   | 25           | 2023-04-17 |
| 104     | 12     | Social    | 60           | 2023-04-18 |
+---------+--------+-----------+--------------+------------+

Scenario

Analyzing Oculus app engagement over the last 30 days

Question

Write SQL to find the most used app (total minutes) in the past 30 days. Write SQL to compute the percentage of time spent in each app category in the past 30 days. How would you test the hypothesis that users of the "Social" category are more regularly engaged than "Game" users?

Hints

Use date filters, aggregates, window functions; propose a statistical test.

Most used app by minutes

Using the AppUsage table, find the single most used app by total minutes for usage between 2023-04-01 and 2023-04-30. Return one row with app_id and total_minutes.

Table AppUsage

column	type	constraints
user_id	INTEGER
app_id	INTEGER
category	VARCHAR
minutes_spent	INTEGER
usage_date	DATE

Sample data:

app_id	category	minutes_spent	usage_date	user_id
12	Social	45	2023-04-15	101
17	Game	30	2023-04-16	102
17	Game	20	2023-04-17	101
22	Fitness	25	2023-04-17	103
12	Social	60	2023-04-18	104

Expected output

A single-row result with the app_id and total_minutes for the most used app between 2023-04-01 and 2023-04-30.

app_id	total_minutes
12	105

Hints

1. Filter to the required date range before aggregating.
2. Aggregate minutes_spent by app_id, then use a window function to pick the top app (break ties by app_id).

Category time percentages

Using the AppUsage table, compute the percentage of total minutes spent in each app category for usage between 2023-04-01 and 2023-04-30. Return category, minutes_spent, and pct_of_total_minutes rounded to two decimals.

Table AppUsage

column	type	constraints
user_id	INTEGER
app_id	INTEGER
category	VARCHAR
minutes_spent	INTEGER
usage_date	DATE

Sample data:

app_id	category	minutes_spent	usage_date	user_id
12	Social	45	2023-04-15	101
17	Game	30	2023-04-16	102
17	Game	20	2023-04-17	101
22	Fitness	25	2023-04-17	103
12	Social	60	2023-04-18	104

Expected output

Category-level minutes and each category's share of total minutes between 2023-04-01 and 2023-04-30.

category	minutes_spent	pct_of_total_minutes
Social	105	58.33
Game	50	27.78
Fitness	25	13.89

Hints

1. First compute total minutes per category within the date window.
2. Compute the grand total of minutes and divide each category's minutes by this total; multiply by 100 and round to 2 decimals.

Prep test: Social vs Game engagement

Using the AppUsage table, prepare per-user engagement metrics for the Social and Game categories for usage between 2023-04-01 and 2023-04-30, to support testing the hypothesis that Social users are more regularly engaged than Game users. Output user_id, category, active_days, total_minutes, and avg_minutes_per_active_day.

Table AppUsage

column	type	constraints
user_id	INTEGER
app_id	INTEGER
category	VARCHAR
minutes_spent	INTEGER
usage_date	DATE

Sample data:

app_id	category	minutes_spent	usage_date	user_id
12	Social	45	2023-04-15	101
17	Game	30	2023-04-16	102
17	Game	20	2023-04-17	101
22	Fitness	25	2023-04-17	103
12	Social	60	2023-04-18	104

Expected output

Per-user metrics to export for statistical testing (e.g., Welch t-test or Mann–Whitney U) comparing Social vs Game regular engagement.

active_days	avg_minutes_per_active_day	category	total_minutes	user_id
1	20	Game	20	101
1	30	Game	30	102
1	45	Social	45	101
1	60	Social	60	104

Hints

1. Compute per-user active days as the count of distinct usage_date values per user and category within the date window.
2. Also compute total minutes per user and category, then average minutes per active day as total_minutes / active_days.
3. Use the resulting per-user metrics outside SQL to run a statistical test (e.g., Welch t-test or Mann–Whitney U) comparing Social vs Game engagement.