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