Coding questions · Meta
Write SQL to compare exclusive category engagement
This question evaluates SQL-based data manipulation and cohort analysis competencies, including cohort definition, session-level filtering and aggregation for metrics such as user counts, median active days, proportions, and per-user average minutes while applying data-quality constraints over a fixed date window.
You are given session-level data and must compare engagement between users who exclusively used the 'social' category versus those who exclusively used the 'game' category in a fixed window. Use the 28-day window from 2025-08-04 to 2025-09-01 inclusive. Define two mutually exclusive cohorts: 'social_only' users had ≥1 'social' session and 0 'game' sessions in the window; 'game_only' users had ≥1 'game' session and 0 'social' sessions. Exclude everyone else (including users with both categories or only 'other'). Compute, for each cohort: (1) users_count; (2) median_active_days (median over users of distinct calendar days with ≥1 session); (3) pct_users_active_10_plus_days (share of users with active_days ≥ 10); and (4) avg_minutes_per_active_day (for each user, total duration_minutes in the window divided by that user's active_days; then average across users). Return one row per cohort with columns: cohort, users_count, median_active_days, pct_users_active_10_plus_days, avg_minutes_per_active_day. Assume standard SQL (e.g., PostgreSQL). Handle data quality by ignoring sessions with NULL category or duration_minutes ≤ 0. Schema and small sample data:
Schema:
users(user_id INT, signup_date DATE)
sessions(session_id INT, user_id INT, session_start TIMESTAMP, session_end TIMESTAMP, category VARCHAR, duration_minutes INT)
Sample tables (ASCII):
users
+---------+-------------+
| user_id | signup_date |
+---------+-------------+
| 1 | 2025-07-15 |
| 2 | 2025-08-10 |
| 3 | 2025-08-20 |
| 4 | 2025-08-25 |
+---------+-------------+
sessions
+------------+---------+---------------------+---------------------+----------+-----------------+
| session_id | user_id | session_start | session_end | category | duration_minutes|
+------------+---------+---------------------+---------------------+----------+-----------------+
| 101 | 1 | 2025-08-05 10:00:00 | 2025-08-05 10:30:00 | social | 30 |
| 102 | 1 | 2025-08-06 10:00:00 | 2025-08-06 10:20:00 | social | 20 |
| 103 | 2 | 2025-08-07 09:00:00 | 2025-08-07 10:00:00 | game | 60 |
| 104 | 2 | 2025-08-08 09:00:00 | 2025-08-08 09:45:00 | game | 45 |
| 105 | 3 | 2025-08-09 12:00:00 | 2025-08-09 12:10:00 | social | 10 |
| 106 | 3 | 2025-08-13 12:00:00 | 2025-08-13 12:10:00 | game | 10 |
| 107 | 4 | 2025-08-11 14:00:00 | 2025-08-11 14:30:00 | game | 30 |
+------------+---------+---------------------+---------------------+----------+-----------------+
Write a single SQL query (CTEs allowed) that produces the required output for the given window, correctly excluding mixed-category users and handling the data quality constraints.
Compare Exclusive Social vs Game Engagement Over 28-Day Window
You are given session-level data and must compare engagement between users who exclusively used the 'social' category versus those who exclusively used the 'game' category in a fixed window.
Use the 28-day window from 2025-08-04 to 2025-09-01 (inclusive, based on session_start date).
Define two mutually exclusive cohorts:
- 'social_only': users who had at least 1 'social' session and 0 'game' sessions in the window.
- 'game_only': users who had at least 1 'game' session and 0 'social' sessions in the window.
Exclude everyone else, including:
- users who used both 'social' and 'game' in the window, and
- users whose valid sessions in the window are only in other categories.
When building cohorts and engagement metrics, ignore sessions where:
- category IS NULL, or
- category is not in ('social', 'game'), or
- duration_minutes <= 0.
For each cohort, compute the following metrics over the window:
- users_count: number of users in the cohort.
- median_active_days: the median, over users in the cohort, of active_days, where active_days for a user is the count of distinct calendar dates (from session_start) on which the user had at least one valid session in the window.
- pct_users_active_10_plus_days: the fraction of users in the cohort with active_days >= 10.
- avg_minutes_per_active_day: for each user, compute total valid duration_minutes in the window divided by that user's active_days (minutes_per_active_day), then average minutes_per_active_day across users in the cohort.
Return one row per cohort with columns:
- cohort (values: 'social_only', 'game_only')
- users_count
- median_active_days
- pct_users_active_10_plus_days
- avg_minutes_per_active_day
Write a single SQL query (CTEs allowed) in standard PostgreSQL-style SQL that produces the required output for the given window, correctly excluding mixed-category users and handling the data quality constraints.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-07-15 | 1 |
| 2025-08-10 | 2 |
| 2025-08-20 | 3 |
| 2025-08-25 | 4 |
| 2025-08-01 | 5 |
| 2025-08-15 | 6 |
Table sessions
| column | type | constraints |
|---|---|---|
| session_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| session_start | TIMESTAMP | NOT NULL |
| session_end | TIMESTAMP | NOT NULL |
| category | VARCHAR(20) | |
| duration_minutes | INT | NOT NULL |
Sample data:
| category | duration_minutes | session_end | session_id | session_start | user_id |
|---|---|---|---|---|---|
| social | 30 | 2025-08-05 10:30:00 | 101 | 2025-08-05 10:00:00 | 1 |
| social | 20 | 2025-08-06 10:20:00 | 102 | 2025-08-06 10:00:00 | 1 |
| social | 40 | 2025-08-10 09:40:00 | 108 | 2025-08-10 09:00:00 | 1 |
| game | 60 | 2025-08-07 10:00:00 | 103 | 2025-08-07 09:00:00 | 2 |
| game | 45 | 2025-08-08 09:45:00 | 104 | 2025-08-08 09:00:00 | 2 |
| game | 30 | 2025-08-09 09:30:00 | 109 | 2025-08-09 09:00:00 | 2 |
| game | 30 | 2025-08-10 09:30:00 | 110 | 2025-08-10 09:00:00 | 2 |
| game | 30 | 2025-08-11 09:30:00 | 111 | 2025-08-11 09:00:00 | 2 |
| game | 30 | 2025-08-12 09:30:00 | 112 | 2025-08-12 09:00:00 | 2 |
| game | 30 | 2025-08-13 09:30:00 | 113 | 2025-08-13 09:00:00 | 2 |
| game | 30 | 2025-08-14 09:30:00 | 114 | 2025-08-14 09:00:00 | 2 |
| game | 30 | 2025-08-15 09:30:00 | 115 | 2025-08-15 09:00:00 | 2 |
| social | 10 | 2025-08-09 12:10:00 | 105 | 2025-08-09 12:00:00 | 3 |
| game | 10 | 2025-08-13 12:10:00 | 106 | 2025-08-13 12:00:00 | 3 |
| game | 30 | 2025-08-11 14:30:00 | 107 | 2025-08-11 14:00:00 | 4 |
| game | 25 | 2025-08-12 14:25:00 | 116 | 2025-08-12 14:00:00 | 4 |
| game | 25 | 2025-08-13 14:25:00 | 117 | 2025-08-13 14:00:00 | 4 |
| game | 25 | 2025-08-14 14:25:00 | 118 | 2025-08-14 14:00:00 | 4 |
| game | 25 | 2025-08-15 14:25:00 | 119 | 2025-08-15 14:00:00 | 4 |
| game | 25 | 2025-08-16 14:25:00 | 120 | 2025-08-16 14:00:00 | 4 |
| game | 25 | 2025-08-17 14:25:00 | 121 | 2025-08-17 14:00:00 | 4 |
| game | 25 | 2025-08-18 14:25:00 | 122 | 2025-08-18 14:00:00 | 4 |
| game | 25 | 2025-08-19 14:25:00 | 123 | 2025-08-19 14:00:00 | 4 |
| game | 25 | 2025-08-20 14:25:00 | 124 | 2025-08-20 14:00:00 | 4 |
| game | 25 | 2025-08-21 14:25:00 | 125 | 2025-08-21 14:00:00 | 4 |
| game | 25 | 2025-08-22 14:25:00 | 126 | 2025-08-22 14:00:00 | 4 |
| social | 15 | 2025-08-04 08:15:00 | 127 | 2025-08-04 08:00:00 | 5 |
| social | 15 | 2025-08-05 08:15:00 | 128 | 2025-08-05 08:00:00 | 5 |
| social | 15 | 2025-08-06 08:15:00 | 129 | 2025-08-06 08:00:00 | 5 |
| social | 15 | 2025-08-07 08:15:00 | 130 | 2025-08-07 08:00:00 | 5 |
| social | 15 | 2025-08-08 08:15:00 | 131 | 2025-08-08 08:00:00 | 5 |
| social | 15 | 2025-08-09 08:15:00 | 132 | 2025-08-09 08:00:00 | 5 |
| social | 15 | 2025-08-10 08:15:00 | 133 | 2025-08-10 08:00:00 | 5 |
| social | 15 | 2025-08-11 08:15:00 | 134 | 2025-08-11 08:00:00 | 5 |
| social | 15 | 2025-08-12 08:15:00 | 135 | 2025-08-12 08:00:00 | 5 |
| social | 15 | 2025-08-13 08:15:00 | 136 | 2025-08-13 08:00:00 | 5 |
| social | 15 | 2025-08-14 08:15:00 | 137 | 2025-08-14 08:00:00 | 5 |
| social | 15 | 2025-09-02 08:15:00 | 140 | 2025-09-02 08:00:00 | 5 |
| NULL | 20 | 2025-08-05 10:20:00 | 138 | 2025-08-05 10:00:00 | 6 |
| other | 30 | 2025-08-06 10:30:00 | 139 | 2025-08-06 10:00:00 | 6 |
Expected output
One row per cohort ('social_only', 'game_only') for the window 2025-08-04 to 2025-09-01, showing number of users, median active days, fraction of users with at least 10 active days, and average minutes per active day. On the sample data, the result is:
| avg_minutes_per_active_day | cohort | median_active_days | pct_users_active_10_plus_days | users_count |
|---|---|---|---|---|
| 30.21 | game_only | 10.5 | 0.5 | 2 |
| 22.5 | social_only | 7 | 0.5 | 2 |
Hints
- First filter sessions by date window and data quality rules, and keep only 'social' and 'game' categories.
- Build per-user cohort flags (social-only vs game-only) before computing per-user active_days and minutes_per_active_day.
- Use PERCENTILE_CONT for the median over users and COUNT(DISTINCT session_date) for active_days; pct_users_active_10_plus_days can be derived as an AVG over a 0/1 indicator.