Stories Posted and Total Story Views per User — SQL Problem
Stories disappear after a day, so the growth team keeps a summary of story reach.
- Difficulty: Easy
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #425
Problem statement
Stories disappear after a day, so the growth team keeps a summary of story reach. For every user who has posted at least one story, report how many stories they posted and how many views those stories got in total.
Return the columns user_id, stories and total_views. A story nobody viewed still counts in stories and adds 0 to total_views. Users who never posted a story do not appear. Return the rows in any order.
Tables
Table: stories
| Column | Type |
|---|---|
| story_id | int |
| user_id | int |
| posted_at | datetime |
Primary key: story_id.
One row per story; user_id is the poster.
Table: story_views
| Column | Type |
|---|---|
| story_id | int |
| viewer_id | int |
| viewed_at | datetime |
Primary key: story_id, viewer_id.
One row per (story, viewer) — repeat views by the same viewer are not stored. story_id is always a row of stories.
Examples
Example 1
stories
| story_id | user_id | posted_at |
|---|---|---|
| 1 | 21 | 2025-03-01 08:00:00 |
| 2 | 21 | 2025-03-01 20:15:00 |
| 3 | 22 | 2025-03-02 12:40:00 |
| 4 | 23 | 2025-03-02 19:05:00 |
story_views
| story_id | viewer_id | viewed_at |
|---|---|---|
| 1 | 22 | 2025-03-01 08:10:00 |
| 1 | 23 | 2025-03-01 09:00:00 |
| 1 | 24 | 2025-03-01 13:20:00 |
| 2 | 22 | 2025-03-01 21:00:00 |
| 3 | 21 | 2025-03-02 13:00:00 |
Output
| user_id | stories | total_views |
|---|---|---|
| 21 | 2 | 4 |
| 22 | 1 | 1 |
| 23 | 1 | 0 |
How to solve Stories Posted and Total Story Views per User
Joining stories to story_views multiplies each story by its number of views, so two counts come out of one grouped result. A LEFT JOIN keeps a story with no views as a single row whose view columns are NULL.
Per poster, COUNT(DISTINCT s.story_id) counts stories once each despite the fan-out, and COUNT(v.viewer_id) counts only real view rows — the NULL from an unviewed story is skipped, so it adds 0. Using COUNT(*) for views would wrongly count that NULL-extended row as a view.
An alternative pre-aggregates views per story in a derived table and joins that, which avoids the fan-out entirely: each story is one row, so COUNT(*) counts stories and SUM(COALESCE(cnt, 0)) adds the views. A correlated scalar subquery per story does the same. All are linear with an index on story_views.story_id.
Reference solution (MySQL)
SELECT s.user_id,
COUNT(DISTINCT s.story_id) AS stories,
COUNT(v.viewer_id) AS total_views
FROM stories s
LEFT JOIN story_views v ON v.story_id = s.story_id
GROUP BY s.user_idAnother way
SELECT user_id, COUNT(*) AS stories, SUM(cnt) AS total_views FROM (SELECT s.user_id, (SELECT COUNT(*) FROM story_views v WHERE v.story_id = s.story_id) AS cnt FROM stories s) x GROUP BY user_idAnother way
SELECT s.user_id, COUNT(*) AS stories, SUM(COALESCE(v.cnt, 0)) AS total_views FROM stories s LEFT JOIN (SELECT story_id, COUNT(*) AS cnt FROM story_views GROUP BY story_id) v ON v.story_id = s.story_id GROUP BY s.user_id← Posts With No Likes From Anyone but the Author · Hashtags Used on Three or More Live Posts →