Stories Posted and Total Story Views per User

Easy
425.

Stories Posted and Total Story Views per User

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

ColumnType
story_idint
user_idint
posted_atdatetime

Primary key: story_id.

One row per story; user_id is the poster.

Table: story_views

ColumnType
story_idint
viewer_idint
viewed_atdatetime

Primary key (together): 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

stories

story_iduser_idposted_at
1212025-03-01 08:00:00
2212025-03-01 20:15:00
3222025-03-02 12:40:00
4232025-03-02 19:05:00

story_views

story_idviewer_idviewed_at
1222025-03-01 08:10:00
1232025-03-01 09:00:00
1242025-03-01 13:20:00
2222025-03-01 21:00:00
3212025-03-02 13:00:00

Output

user_idstoriestotal_views
2124
2211
2310
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
story_idint
user_idint
posted_atdatetime

Primary key: story_id.

One row per story; user_id is the poster.

Table: story_views

ColumnType
story_idint
viewer_idint
viewed_atdatetime

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_iduser_idposted_at
1212025-03-01 08:00:00
2212025-03-01 20:15:00
3222025-03-02 12:40:00
4232025-03-02 19:05:00

story_views

story_idviewer_idviewed_at
1222025-03-01 08:10:00
1232025-03-01 09:00:00
1242025-03-01 13:20:00
2222025-03-01 21:00:00
3212025-03-02 13:00:00

Output

user_idstoriestotal_views
2124
2211
2310

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_id

Another 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_id

Another 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 →