Coding questions · Meta

Find the most-used app

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.”

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).
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.

Most-used app in the last 7 days (by total time spent)

You are given two tables:

user_activity: one row per app session with its duration
apps: app metadata

Define “most used app” as the app with the highest total time spent (sum of duration_minutes) during the last 7 days: FROM 2025-05-26 TO 2025-06-01 (inclusive).

Return the app_id, app_name, and total_duration_minutes for the most-used app. If there is a tie, you may return any of the tied apps.

Table `apps`

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

Sample data:

app_category	app_id	app_name
social	101	ChatSpace
social	102	PicShare
game	201	DragonQuest
game	202	PuzzlePop
fitness	301	FitTrack
productivity	401	TaskPro
video	501	StreamNow

Table `user_activity`

column	type	constraints
session_id	VARCHAR(20)	PRIMARY KEY
user_id	INT	NOT NULL
activity_date	DATE	NOT NULL
app_id	INT	NOT NULL
duration_minutes	INT	NOT NULL

Sample data:

activity_date	app_id	duration_minutes	session_id	user_id
2025-05-26	101	30	s1	1
2025-05-27	101	40	s2	1
2025-05-28	201	10	s3	1
2025-05-29	301	20	s25	1
2025-05-30	101	60	s4	1
2025-06-01	102	20	s5	1
2025-05-15	101	30	s33	1
2025-05-26	201	50	s6	2
2025-05-27	201	70	s7	2
2025-05-28	202	30	s8	2
2025-05-29	101	10	s9	2
2025-05-30	999	15	s24	2
2025-05-31	201	90	s10	2
2025-05-05	201	60	s32	2
2025-05-26	101	40	s11	3
2025-05-27	201	40	s12	3
2025-05-28	102	30	s13	3
2025-05-29	202	30	s14	3
2025-05-30	401	15	s26	3
2025-05-20	202	40	s34	3
2025-05-30	101	25	s15	4
2025-05-31	102	35	s16	4
2025-06-01	501	5	s27	4
2025-05-25	102	15	s35	4
2025-05-26	201	500	s17	5
2025-05-27	201	520	s18	5
2025-05-28	201	510	s19	5
2025-05-29	201	515	s20	5
2025-05-30	201	505	s21	5
2025-05-31	201	530	s22	5
2025-06-01	201	525	s23	5
2025-05-10	201	500	s28	5
2025-05-11	201	510	s29	5
2025-05-12	201	505	s30	5
2025-05-13	201	490	s31	5

Expected output

One row: the app with the highest summed duration_minutes from 2025-05-26 through 2025-06-01.

app_id	app_name	total_duration_minutes
201	DragonQuest	3865

Hints

Filter to the 7-day window first, then aggregate.
Order by the aggregated total duration and pick the top row.

Time-spent percentage by app category (last 7 days)

Compute the percentage of total time spent (duration_minutes) for each app_category during the last 7 days: FROM 2025-05-26 TO 2025-06-01 (inclusive).

Rules:

Join user_activity to apps to get app_category.
Some app_id values in user_activity may not exist in apps; treat those as app_category = 'Unknown'.

Return one row per category with:

app_category
category_duration_minutes
pct_of_total_time (percentage from 0 to 100, rounded to 2 decimals).

Table `apps`

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

Sample data:

app_category	app_id	app_name
social	101	ChatSpace
social	102	PicShare
game	201	DragonQuest
game	202	PuzzlePop
fitness	301	FitTrack
productivity	401	TaskPro
video	501	StreamNow

Table `user_activity`

column	type	constraints
session_id	VARCHAR(20)	PRIMARY KEY
user_id	INT	NOT NULL
activity_date	DATE	NOT NULL
app_id	INT	NOT NULL
duration_minutes	INT	NOT NULL

Sample data:

activity_date	app_id	duration_minutes	session_id	user_id
2025-05-26	101	30	s1	1
2025-05-27	101	40	s2	1
2025-05-28	201	10	s3	1
2025-05-29	301	20	s25	1
2025-05-30	101	60	s4	1
2025-06-01	102	20	s5	1
2025-05-15	101	30	s33	1
2025-05-26	201	50	s6	2
2025-05-27	201	70	s7	2
2025-05-28	202	30	s8	2
2025-05-29	101	10	s9	2
2025-05-30	999	15	s24	2
2025-05-31	201	90	s10	2
2025-05-05	201	60	s32	2
2025-05-26	101	40	s11	3
2025-05-27	201	40	s12	3
2025-05-28	102	30	s13	3
2025-05-29	202	30	s14	3
2025-05-30	401	15	s26	3
2025-05-20	202	40	s34	3
2025-05-30	101	25	s15	4
2025-05-31	102	35	s16	4
2025-06-01	501	5	s27	4
2025-05-25	102	15	s35	4
2025-05-26	201	500	s17	5
2025-05-27	201	520	s18	5
2025-05-28	201	510	s19	5
2025-05-29	201	515	s20	5
2025-05-30	201	505	s21	5
2025-05-31	201	530	s22	5
2025-06-01	201	525	s23	5
2025-05-10	201	500	s28	5
2025-05-11	201	510	s29	5
2025-05-12	201	505	s30	5
2025-05-13	201	490	s31	5

Expected output

Multiple rows (one per app_category, including 'Unknown'), with total time and percentage of total time for 2025-05-26 through 2025-06-01.

app_category	category_duration_minutes	pct_of_total_time
game	3925	91.92
social	290	6.79
fitness	20	0.47
Unknown	15	0.35
productivity	15	0.35
video	5	0.12

Hints

Use a LEFT JOIN so activity with missing app metadata is retained.
Compute category totals in a CTE, then use a window SUM to get the overall denominator.

Compare engagement for social-dominant vs game-dominant users (last 7 days)

A PM hypothesizes that people who use social apps are more engaged than people who use game apps.

Use the last 7 days: FROM 2025-05-26 TO 2025-06-01 (inclusive).

Define:

Engagement metric per user = (number of sessions in the 7-day window) / 7.
User classification based on time spent (duration_minutes) in the 7-day window:
- 'social_dominant' if social time >= 80% of the user's total time
- 'game_dominant' if game time >= 80% of the user's total time
- otherwise 'mixed'
Ignore 'Unknown' and other categories for dominance checks, but they still count toward the user's total time.

Return one row per user_group with:

user_group
user_count
avg_sessions_per_day (average of the per-user engagement metric, rounded to 2 decimals).

Table `apps`

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

Sample data:

app_category	app_id	app_name
social	101	ChatSpace
social	102	PicShare
game	201	DragonQuest
game	202	PuzzlePop
fitness	301	FitTrack
productivity	401	TaskPro
video	501	StreamNow

Table `user_activity`

column	type	constraints
session_id	VARCHAR(20)	PRIMARY KEY
user_id	INT	NOT NULL
activity_date	DATE	NOT NULL
app_id	INT	NOT NULL
duration_minutes	INT	NOT NULL

Sample data:

activity_date	app_id	duration_minutes	session_id	user_id
2025-05-26	101	30	s1	1
2025-05-27	101	40	s2	1
2025-05-28	201	10	s3	1
2025-05-29	301	20	s25	1
2025-05-30	101	60	s4	1
2025-06-01	102	20	s5	1
2025-05-15	101	30	s33	1
2025-05-26	201	50	s6	2
2025-05-27	201	70	s7	2
2025-05-28	202	30	s8	2
2025-05-29	101	10	s9	2
2025-05-30	999	15	s24	2
2025-05-31	201	90	s10	2
2025-05-05	201	60	s32	2
2025-05-26	101	40	s11	3
2025-05-27	201	40	s12	3
2025-05-28	102	30	s13	3
2025-05-29	202	30	s14	3
2025-05-30	401	15	s26	3
2025-05-20	202	40	s34	3
2025-05-30	101	25	s15	4
2025-05-31	102	35	s16	4
2025-06-01	501	5	s27	4
2025-05-25	102	15	s35	4
2025-05-26	201	500	s17	5
2025-05-27	201	520	s18	5
2025-05-28	201	510	s19	5
2025-05-29	201	515	s20	5
2025-05-30	201	505	s21	5
2025-05-31	201	530	s22	5
2025-06-01	201	525	s23	5
2025-05-10	201	500	s28	5
2025-05-11	201	510	s29	5
2025-05-12	201	505	s30	5
2025-05-13	201	490	s31	5

Expected output

One row per user_group with the number of users in the group and their average sessions-per-day (sessions/7) over 2025-05-26 to 2025-06-01.

avg_sessions_per_day	user_count	user_group
0.93	2	game_dominant
0.71	1	mixed
0.64	2	social_dominant

Hints

Aggregate to the user level first (total minutes, social minutes, game minutes, sessions).
Classify users with a CASE statement, then aggregate again to compare groups.

How common are unhealthy users (last 30 days)?

Define an 'unhealthy user' as someone who:

spent more than 8 hours per day (> 480 minutes) on the platform on a given day, AND
had more than 10 such days

Evaluate this in the last 30 days: FROM 2025-05-03 TO 2025-06-01 (inclusive).

Return:

unhealthy_user_count
total_active_users (users with any activity in the window)
unhealthy_user_pct (unhealthy_user_count / total_active_users * 100, rounded to 2 decimals).

Table `apps`

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

Sample data:

app_category	app_id	app_name
social	101	ChatSpace
social	102	PicShare
game	201	DragonQuest
game	202	PuzzlePop
fitness	301	FitTrack
productivity	401	TaskPro
video	501	StreamNow

Table `user_activity`

column	type	constraints
session_id	VARCHAR(20)	PRIMARY KEY
user_id	INT	NOT NULL
activity_date	DATE	NOT NULL
app_id	INT	NOT NULL
duration_minutes	INT	NOT NULL

Sample data:

activity_date	app_id	duration_minutes	session_id	user_id
2025-05-26	101	30	s1	1
2025-05-27	101	40	s2	1
2025-05-28	201	10	s3	1
2025-05-29	301	20	s25	1
2025-05-30	101	60	s4	1
2025-06-01	102	20	s5	1
2025-05-15	101	30	s33	1
2025-05-26	201	50	s6	2
2025-05-27	201	70	s7	2
2025-05-28	202	30	s8	2
2025-05-29	101	10	s9	2
2025-05-30	999	15	s24	2
2025-05-31	201	90	s10	2
2025-05-05	201	60	s32	2
2025-05-26	101	40	s11	3
2025-05-27	201	40	s12	3
2025-05-28	102	30	s13	3
2025-05-29	202	30	s14	3
2025-05-30	401	15	s26	3
2025-05-20	202	40	s34	3
2025-05-30	101	25	s15	4
2025-05-31	102	35	s16	4
2025-06-01	501	5	s27	4
2025-05-25	102	15	s35	4
2025-05-26	201	500	s17	5
2025-05-27	201	520	s18	5
2025-05-28	201	510	s19	5
2025-05-29	201	515	s20	5
2025-05-30	201	505	s21	5
2025-05-31	201	530	s22	5
2025-06-01	201	525	s23	5
2025-05-10	201	500	s28	5
2025-05-11	201	510	s29	5
2025-05-12	201	505	s30	5
2025-05-13	201	490	s31	5

Expected output

One row with the count and percentage of unhealthy users among all active users in 2025-05-03 to 2025-06-01.

total_active_users	unhealthy_user_count	unhealthy_user_pct
5	1	20

Hints

First roll up to user-day totals, then apply the > 480 minutes rule.
Count qualifying days per user and then count users with days_over_8h > 10.

Tables

user_activity

apps

Task

Output

Most-used app in the last 7 days (by total time spent)

Table apps

Table user_activity

Expected output

Hints

Time-spent percentage by app category (last 7 days)

Table apps

Table user_activity

Expected output

Hints

Compare engagement for social-dominant vs game-dominant users (last 7 days)

Table apps

Table user_activity

Expected output

Hints

How common are unhealthy users (last 30 days)?

Table apps

Table user_activity

Expected output

Hints

`user_activity`

`apps`

Table `apps`

Table `user_activity`

Table `apps`

Table `user_activity`

Table `apps`

Table `user_activity`

Table `apps`

Table `user_activity`