Meta-style Company ChallengeHardVerified answerSQLite live

Story Views Leaderboard

Rank all users who have posted at least one story by their total story views, and show each story's individual view count.

  • Window functions
  • Joins
  • Subqueries
  • Aggregation
  • Sorting

Challenge brief

Understand the request

Stories Product Team is evaluating the Stories feature adoption and wants to see which users' stories attract the most viewers.

Show story details with creator username, view count, total views per user, and a user rank by total views.

Return

  • username
  • story_id
  • story_type
  • views_count
  • user_total_views (sum of all stories for that user)
  • user_rank (RANK by user_total_views desc)

Constraints

  • Return users with at least one story
  • Rank users by their total views before expanding back to individual stories
  • Every story for the same user must retain that user’s rank

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

stories

  • story_idINTEGER
  • user_idINTEGER
  • story_typeVARCHAR(20)
  • views_countINTEGER

users

  • user_idINTEGER
  • usernameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

You need per-story view counts AND per-user totals in the same row. Compute user totals in a CTE first, then join back to individual story rows. RANK() ranks users by their total.

Hint 2

CTE user_story_totals: GROUP BY user_id, SUM(views_count). Main: INNER JOIN stories to users and to the CTE. RANK() OVER (ORDER BY user_total_views DESC).

Hint 3

Start with the tables that establish the result grain for question 22, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH user_story_totals AS (SELECT user_id, SUM(views_count) AS user_total_views FROM stories GROUP BY user_id), ranked_users AS (SELECT user_id, user_total_views, RANK() OVER (ORDER BY user_total_views DESC) AS user_rank FROM user_story_totals) SELECT u.username, s.story_id, s.story_type, s.views_count, ru.user_total_views, ru.user_rank FROM ranked_users ru INNER JOIN users u ON ru.user_id = u.user_id INNER JOIN stories s ON ru.user_id = s.user_id ORDER BY ru.user_rank, s.story_id

Why this works

Aggregate and rank at the user grain first, then join the ranked users back to stories. Ranking after story expansion would incorrectly consume rank positions for users with multiple stories.

Success check

john_doe has 85 total views and both of his stories retain user rank 1; jane_smith and sarah_jones rank 2 and 3.

Expected result

Use this output to verify values, aliases, ordering, and row count.

usernamestory_idstory_typeviews_countuser_total_viewsuser_rank
john_doe1photo45851
john_doe4photo40851
jane_smith2video78782
sarah_jones3photo62623

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.