Group Activity

ASKED IN INTERVIEW

4pts

Problem Statement

Meta communities thrive on engagement. To identify high-density communities, the Analytics team wants to find the most active Facebook Group based on the average number of posts created per member.

Rules:

Activity Score is defined as: (Total number of posts in the group) / (Total number of unique members in the group).
Only consider groups that have at least one member and one post.
If multiple groups have the same highest Activity Score, return the one that was created first (earliest created_at date).
Output columns: group_name, total_members, total_posts, activity_score.
Round the activity_score to 2 decimal places.

Table Schema:

Groups: group_id, group_name, created_at.
Group_Members: member_id, group_id, user_id, joined_at.
Posts: post_id, group_id, author_id, content, created_at.

Tests your understanding of

Join, Aggregate Functions, Arithmetic Operations and Ranking

Input Tables

Groups

group_id(INTEGER)	group_name(VARCHAR)	created_at(DATE)
1	SQL Enthusiasts	2024-01-01
2	Meta Engineers	2024-01-05
3	Travel Bloggers	2024-01-10

Group_Members

group_id(INTEGER)	user_id(INTEGER)
1	101
1	102
2	101
2	103
2	104
3	105

Posts

post_id(INTEGER)	group_id(INTEGER)
1	1
2	1
3	1
4	2
5	2
6	3
7	3

Expected Output

group_name(VARCHAR)	total_members(INTEGER)	total_posts(INTEGER)	activity_score(DECIMAL)
Travel Bloggers	1	2	2