Drill 1 of 2 in Music streaming questions
0

Interview Question Bank · UNION ALL · SUM per key · LIMIT

1. All-time streams from history plus today

MediumMusic streamingQuestion #270

Concept: UNION ALL · SUM per key · LIMITshow

When the same measure lives in two tables (a running total and today's increment), stack them with UNION ALL and SUM per key. A JOIN drops keys missing from one side and multiplies rows when one side has several per key. Plain UNION is a trap too: it removes duplicate rows, so two equal counts for the same key collapse into one.

Example (different data):

WITH all_points AS (
  SELECT member_id, points FROM points_archive
  UNION ALL
  SELECT member_id, points FROM points_this_week
)
SELECT member_id, SUM(points) AS points
FROM all_points
GROUP BY member_id
ORDER BY points DESC
LIMIT 10;

Your task

Echolane, a music streaming app, keeps each song's all-time stream count through yesterday (2025-06-14) in fm_song_totals, and today's streams, one row per song and region, in fm_streams_today. A song released today has no total yet, and a song nobody played today has no row today.

Combine the two tables into each song's all-time streams and return the top 5 songs: title, artist and total_streams. Sort by total_streams descending, then title.

Starting Postgres in your browser…

Checking your access…