Write SQL to compare social-only vs game-only engagement

You are given two tables capturing Oculus app usage. Define an 'active day' as a UTC date on which a user generates at least one event. Consider only the window 2025-07-01 through 2025-08-31 (inclusive). Define 'social-only' users as those whose every event in this window has category = 'social' (no events in 'game' or any other category during the window). Define 'game-only' analogously. A 'regularly engaged week' is a Monday–Sunday week with active_days >= 3. Write ANSI SQL that outputs one row per cohort with: cohort ('social_only' or 'game_only'), number_of_users, avg_weekly_active_days_per_user (average across all user-weeks in the window for users in the cohort), and pct_regular_weeks (fraction of user-weeks in the cohort with active_days >= 3). Treat weeks that partially fall outside the window by counting only days inside the window; exclude users with zero events in the window; exclude users who have events in multiple categories or in categories outside {'social','game'}. Use the schema and small sample below to illustrate your approach.

Schema:

• users(user_id INT, signup_date DATE)
• events(user_id INT, event_time TIMESTAMP, category STRING, event_type STRING)

Sample (UTC):

users
user_id | signup_date
1 | 2025-06-28
2 | 2025-07-05
3 | 2025-07-10
4 | 2025-07-02
5 | 2025-08-01

events
user_id | event_time | category | event_type
1 | 2025-07-03 10:00:00 | social | view
1 | 2025-07-03 12:00:00 | social | post
1 | 2025-07-04 09:00:00 | social | view
2 | 2025-07-06 14:00:00 | game | launch
2 | 2025-07-07 15:00:00 | game | score
2 | 2025-07-09 19:00:00 | game | launch
3 | 2025-07-12 11:00:00 | social | view
3 | 2025-07-13 11:00:00 | game | launch
4 | 2025-07-15 09:00:00 | social | view
4 | 2025-07-16 09:00:00 | social | message
5 | 2025-08-10 08:00:00 | game | launch
5 | 2025-08-12 08:00:00 | other | purchase

Compare social-only vs game-only engagement by weekly active days

You are given two tables capturing Oculus app usage. Define an 'active day' as a UTC date on which a user generates at least one event.

Consider only events in the window 2025-07-01 through 2025-08-31 (inclusive).

Cohorts:

• A user is 'social_only' if EVERY event they generate in this window has category = 'social'.
• A user is 'game_only' if EVERY event they generate in this window has category = 'game'.

Exclusions:

• Exclude users with zero events in the window.
• Exclude users who have events in multiple categories during the window.
• Exclude users who have events in categories outside {'social','game'} during the window.

Weekly engagement:

• A 'week' is Monday–Sunday.
• A 'regularly engaged week' is a user-week with active_days >= 3.
• For weeks that partially fall outside the window, count only active days inside the window (i.e., only consider events in the window).

Task: Write ANSI SQL that outputs one row per cohort with:

• cohort ('social_only' or 'game_only')
• number_of_users
• avg_weekly_active_days_per_user (average active_days across all user-weeks in the window for users in the cohort)
• pct_regular_weeks (fraction of user-weeks in the cohort with active_days >= 3)

Return only cohorts that exist in the data.

Table users

column	type	constraints
user_id	INT	PRIMARY KEY, NOT NULL
signup_date	DATE	NOT NULL

Sample data:

signup_date	user_id
2025-06-28	1
2025-07-05	2
2025-07-10	3
2025-07-02	4
2025-08-01	5

Table events

column	type	constraints
user_id	INT	NOT NULL
event_time	TIMESTAMP	NOT NULL
category	VARCHAR(20)	NOT NULL
event_type	VARCHAR(30)	NOT NULL

Sample data:

category	event_time	event_type	user_id
social	2025-07-03 10:00:00	view	1
social	2025-07-03 12:00:00	post	1
social	2025-07-04 09:00:00	view	1
game	2025-07-06 14:00:00	launch	2
game	2025-07-07 15:00:00	score	2
game	2025-07-09 19:00:00	launch	2
social	2025-07-12 11:00:00	view	3
game	2025-07-13 11:00:00	launch	3
social	2025-07-15 09:00:00	view	4
social	2025-07-16 09:00:00	message	4
game	2025-08-10 08:00:00	launch	5
other	2025-08-12 08:00:00	purchase	5

Expected output

One row per cohort with user counts and engagement metrics computed over all user-weeks (Monday–Sunday) in 2025-07-01 to 2025-08-31 inclusive. Only users exclusively in one allowed category within the window are included.

avg_weekly_active_days_per_user	cohort	number_of_users	pct_regular_weeks
1.5	game_only	1	0
2	social_only	2	0

Hints

1. First filter events to the date window, then determine eligibility by enforcing exactly one distinct category per user and restricting it to ('social','game').
2. Compute week start as the Monday of the event_date (ISO day-of-week) and then count distinct active dates per user-week.
3. Aggregate over user-week rows to get both avg_weekly_active_days_per_user and pct_regular_weeks.