Analyze Global Engagement and Impressions with SQL Queries

impressions

+---------+---------+----------+----------------+
| user_id | country | category | impression_cnt |
+---------+---------+----------+----------------+
| 1       | US      | Sports   | 120            |
| 2       | US      | News     | 80             |
| 3       | CN      | Games    | 200            |
+---------+---------+----------+----------------+

user_activity

+---------+-------------+
| user_id | activity_dt |
+---------+-------------+
| 1       | 2023-09-01  |
| 1       | 2023-09-02  |
| 1       | 2023-09-04  |
| 2       | 2023-09-01  |
| 3       | 2023-09-05  |
+---------+-------------+

feature_usage

+---------+-------------+-----------+
| user_id | usage_dt    | feature   |
+---------+-------------+-----------+
| 1       | 2023-09-02  | search    |
| 1       | 2023-09-02  | share     |
| 1       | 2023-09-02  | save      |
| 2       | 2023-09-03  | search    |
| 3       | 2023-09-05  | save      |
+---------+-------------+-----------+

Scenario

A global content platform wants to understand engagement and impressions across countries in the last week.

Question

Write SQL to (a) return, for every country, the content category with the highest total impressions and its count; (b) compute the weekly heavy-user rate, where a heavy user is one who was active on at least 4 distinct days in the last 7 and, on at least one of those days, used 3 or more distinct features.

Hints

Join the three tables, use GROUP BY with conditional aggregation, distinct counts for days/features, and window/CTE for ranking the top category per country.

Top Category by Country

For each country, return the content category with the highest total impressions and its aggregated count. If multiple categories tie for the maximum impressions in a country, break ties by choosing the category with the lexicographically smallest name (ascending).

Table impressions

column	type	constraints
user_id	INTEGER	NOT NULL
country	VARCHAR(2)	NOT NULL
category	VARCHAR(50)	NOT NULL
impression_cnt	INTEGER	NOT NULL

Sample data:

category	country	impression_cnt	user_id
Sports	US	120	1
News	US	80	2
Games	CN	200	3

Expected output

One row per country with the category having the maximum summed impressions; ties are broken deterministically by category name ascending.

country	top_category	total_impressions
CN	Games	200
US	Sports	120

Hints

1. Aggregate impressions by country and category first.
2. Use ROW_NUMBER() partitioned by country and ordered by total_impressions DESC, category ASC to pick the top category per country.

Weekly Heavy-User Rate

Compute the weekly heavy-user rate over the last 7 days ending at the maximum date found across user_activity and feature_usage. A heavy user is one who was active on at least 4 distinct days in that 7-day window and, on at least one of those days, used 3 or more distinct features (on a day that is also an activity day). Return a single row with window_start, window_end, active_user_count (users with at least one activity day in the window), heavy_user_count, and heavy_user_rate = heavy_user_count / active_user_count.

Table user_activity

column	type	constraints
user_id	INTEGER	NOT NULL
activity_dt	DATE	NOT NULL

Sample data:

activity_dt	user_id
2023-09-01	1
2023-09-02	1
2023-09-04	1
2023-09-01	2
2023-09-05	3

Table feature_usage

column	type	constraints
user_id	INTEGER	NOT NULL
usage_dt	DATE	NOT NULL
feature	VARCHAR(20)	NOT NULL

Sample data:

feature	usage_dt	user_id
search	2023-09-02	1
share	2023-09-02	1
save	2023-09-02	1
search	2023-09-03	2
save	2023-09-05	3

Expected output

A single-row summary for the 7-day window ending at the maximum date in the data: active users (had at least one activity day), heavy users (active on 4+ distinct days and with at least one overlapping activity/usage day having 3+ distinct features), and the heavy-user rate.

active_user_count	heavy_user_count	heavy_user_rate	window_end	window_start
3	0	0	2023-09-05	2023-08-30

Hints

1. Define the 7-day window using the maximum date across both tables, then filter both tables to that window.
2. Count distinct activity days per user from user_activity within the window.
3. Per user and day, count distinct features; identify users with at least one day having 3+ distinct features that overlaps an activity day, then intersect with users having 4+ active days.