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…