Compute User Group Stories and Aggregate Story Engagement

user_story_engagement

+---------+----------+------------+------------+-------+-------+
| user_id | story_id | story_type | created_at | views | likes |
+---------+----------+------------+------------+-------+-------+
| 101     | 555      | regular    | 2023-07-01 | 8     | 2     |
| 102     | 556      | group      | 2023-07-02 | 15    | 4     |
| 101     | 557      | group      | 2023-07-03 | 5     | 1     |
| 103     | 558      | regular    | 2023-07-03 | 20    | 10    |
+---------+----------+------------+------------+-------+-------+

Scenario

Python (pandas) task on story-engagement data to assess data wrangling skills.

Question

Using pandas, compute the number of group stories each user has posted and return only users with at least three group stories. Aggregate total views and likes per story_type (regular vs group).

Hints

Use groupby, size/count, sum, reset_index, filtering with query or boolean masks.

Users with Three or More Group Stories

Using the user_story_engagement table, compute the number of group stories each user has posted and return only users with at least three group stories. Output columns: user_id, group_story_count, ordered by user_id.

Table user_story_engagement

column	type	constraints
user_id	INTEGER	NOT NULL
story_id	INTEGER	PRIMARY KEY, NOT NULL
story_type	VARCHAR(10)	NOT NULL, CHECK (story_type IN ('regular','group'))
created_at	DATE	NOT NULL
views	INTEGER	NOT NULL, CHECK (views >= 0)
likes	INTEGER	NOT NULL, CHECK (likes >= 0)

Sample data:

created_at	likes	story_id	story_type	user_id	views
2023-07-01	2	555	regular	101	8
2023-07-02	4	556	group	102	15
2023-07-03	1	557	group	101	5
2023-07-03	10	558	regular	103	20

Expected output

Users with at least three group stories. With the sample data, no user qualifies, so the result is empty.

Hints

1. Filter rows where story_type = 'group'.
2. GROUP BY user_id and use HAVING COUNT(*) >= 3.

Story Type Engagement Totals

Using the user_story_engagement table, aggregate total views and total likes for each story_type and return one row per type. Output columns: story_type, total_views, total_likes, ordered by story_type.

Table user_story_engagement

column	type	constraints
user_id	INTEGER	NOT NULL
story_id	INTEGER	PRIMARY KEY, NOT NULL
story_type	VARCHAR(10)	NOT NULL, CHECK (story_type IN ('regular','group'))
created_at	DATE	NOT NULL
views	INTEGER	NOT NULL, CHECK (views >= 0)
likes	INTEGER	NOT NULL, CHECK (likes >= 0)

Sample data:

created_at	likes	story_id	story_type	user_id	views
2023-07-01	2	555	regular	101	8
2023-07-02	4	556	group	102	15
2023-07-03	1	557	group	101	5
2023-07-03	10	558	regular	103	20

Expected output

Total views and likes aggregated by story_type.

story_type	total_likes	total_views
group	5	20
regular	12	28

Hints

1. GROUP BY story_type.
2. Use SUM on views and likes.