Analyze VR App Usage and Engagement Metrics

vr_usage

+---------+------------+---------+------------+----------+
| user_id | date       | app_id  | session_id | duration |
+---------+------------+---------+------------+----------+
| 10      | 2023-08-01 | 1001    | 555        | 1800     |
| 10      | 2023-08-02 | 1002    | 556        | 2400     |
| 11      | 2023-08-02 | 1001    | 557        | 1200     |
| 12      | 2023-08-03 | 1003    | 558        | 3600     |
| 10      | 2023-08-04 | 1001    | 559        | 900      |
+---------+------------+---------+------------+----------+

apps

+---------+-----------+--------------+
| app_id  | app_name  | app_category |
+---------+-----------+--------------+
| 1001    | SpaceWar  | game         |
| 1002    | VRChat    | social       |
| 1003    | HomeView  | home         |
+---------+-----------+--------------+

Scenario

Oculus VR usage logs and app catalog; product team needs insights into most-used apps and category engagement.

Question

Which app had the greatest total usage duration in the last 30 days? 2. What percentage of total VR time in that period belongs to each app_category? 3. Test the hypothesis: users of “social” apps are more engaged than users of “game” apps. Define an engagement metric, outline the SQL, and describe the statistical test.

Hints

Join usage with apps, filter date >= CURRENT_DATE-30, aggregate durations; for hypothesis you might use active days per user.

Top app by duration (2025-05-03 to 2025-06-01)

Find the single app with the highest total usage duration from 2025-05-03 to 2025-06-01 (inclusive). Return app_id, app_name, and total_duration_seconds.

Table vr_usage

column	type	constraints
user_id	INTEGER	NOT NULL
date	DATE	NOT NULL
app_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES apps(app_id)
session_id	INTEGER	PRIMARY KEY, NOT NULL
duration	INTEGER	NOT NULL, CHECK (duration >= 0)

Sample data:

app_id	date	duration	session_id	user_id
1001	2025-05-10	1800	555	10
1002	2025-05-11	2400	556	10
1001	2025-05-11	1200	557	11
1003	2025-05-12	3600	558	12
1001	2025-05-13	900	559	10

Table apps

column	type	constraints
app_id	INTEGER	PRIMARY KEY, NOT NULL
app_name	VARCHAR(100)	NOT NULL
app_category	VARCHAR(50)	NOT NULL

Sample data:

app_category	app_id	app_name
game	1001	SpaceWar
social	1002	VRChat
home	1003	HomeView

Expected output

The single app with the greatest total duration between 2025-05-03 and 2025-06-01, computed from the provided sample rows.

app_id	app_name	total_duration_seconds
1001	SpaceWar	3900

Hints

1. Filter vr_usage to dates between 2025-05-03 and 2025-06-01
2. Join to apps to get app_name
3. Group by app_id and app_name, order by total duration, and limit 1

Category share of VR time (2025-05-03 to 2025-06-01)

Compute each app_category's total usage duration and its percentage share of total VR time from 2025-05-03 to 2025-06-01 (inclusive).

Table vr_usage

column	type	constraints
user_id	INTEGER	NOT NULL
date	DATE	NOT NULL
app_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES apps(app_id)
session_id	INTEGER	PRIMARY KEY, NOT NULL
duration	INTEGER	NOT NULL, CHECK (duration >= 0)

Sample data:

app_id	date	duration	session_id	user_id
1001	2025-05-10	1800	555	10
1002	2025-05-11	2400	556	10
1001	2025-05-11	1200	557	11
1003	2025-05-12	3600	558	12
1001	2025-05-13	900	559	10

Table apps

column	type	constraints
app_id	INTEGER	PRIMARY KEY, NOT NULL
app_name	VARCHAR(100)	NOT NULL
app_category	VARCHAR(50)	NOT NULL

Sample data:

app_category	app_id	app_name
game	1001	SpaceWar
social	1002	VRChat
home	1003	HomeView

Expected output

Total duration and percentage share by app_category for the period 2025-05-03 to 2025-06-01, computed from the provided sample rows.

app_category	percentage_of_total	total_duration_seconds
game	39.39	3900
home	36.36	3600
social	24.24	2400

Hints

1. Filter vr_usage to dates between 2025-05-03 and 2025-06-01 before aggregating
2. Aggregate duration by app_category
3. Compute a grand total across categories and divide to get percentages

Engagement by category summary (2025-05-03 to 2025-06-01)

Using the inclusive period from 2025-05-03 through 2025-06-01, define engagement as each user's average daily active duration within an app category, where each active day first sums all session duration for that user and category. For the social and game categories, return app_category, n_users, the mean of the per-user engagement, and the sample standard deviation of the per-user engagement. Round the mean and standard deviation to 2 decimal places.

Table apps

column	type	constraints
app_id	INTEGER	PRIMARY KEY, NOT NULL
app_name	VARCHAR(100)	NOT NULL
app_category	VARCHAR(50)	NOT NULL

Sample data:

app_category	app_id	app_name
game	1001	SpaceWar
social	1002	VRChat
home	1003	HomeView

Table vr_usage

column	type	constraints
user_id	INTEGER	NOT NULL
date	DATE	NOT NULL
app_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES apps(app_id)
session_id	INTEGER	PRIMARY KEY, NOT NULL
duration	INTEGER	NOT NULL, CHECK (duration >= 0)

Sample data:

app_id	date	duration	session_id	user_id
1001	2025-05-10	1800	555	10
1002	2025-05-11	2400	556	10
1001	2025-05-11	1200	557	11
1003	2025-05-12	3600	558	12
1001	2025-05-13	900	559	10

Expected output

User-level engagement summary by category for 2025-05-03 to 2025-06-01. Engagement is the mean of per-day total duration on days with activity per user. The resulting summary can be used for a two-sample Welch’s t-test (performed outside SQL) to compare 'social' vs 'game'.

app_category	mean_daily_active_duration_seconds	n_users	stddev_daily_active_duration_seconds
game	1275	2	106.07
social	2400	1	NULL

Hints

1. Filter to dates between 2025-05-03 and 2025-06-01 and join vr_usage with apps to label categories
2. Aggregate to per-user, per-day totals within each app_category, then average per user to define engagement
3. Restrict to app categories 'social' and 'game', and use STDDEV_SAMP for the sample standard deviation