Compute User Group Stories and Aggregate Story Engagement
Company: Snapchat
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: This question evaluates data wrangling and aggregation competency in pandas, focusing on group-level aggregation and filtering of engagement metrics across users and story types.
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.
Tables
user_story_engagement(user_id INTEGER, story_id INTEGER, story_type VARCHAR(10), created_at DATE, views INTEGER, likes INTEGER)
Hints
- Filter rows where story_type = 'group'.
- 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.
Tables
user_story_engagement(user_id INTEGER, story_id INTEGER, story_type VARCHAR(10), created_at DATE, views INTEGER, likes INTEGER)
Hints
- GROUP BY story_type.
- Use SUM on views and likes.