Compute User Group Stories and Aggregate Story Engagement

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Snapchat.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Snapchat during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview