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.