Objective

Write a SQL query to output the cumulative play count for each user-song pair up to Feb 6th, 2026.

Table Schema:

• songs_history

• songs_weekly

Task Requirements:

• Combine play counts from both historical and current weekly data
• Include user-song pairs that exist in only one of the two tables (new listeners or songs with no plays this week)
• Calculate the total cumulative play count per user-song pair
• Sort the results by total plays in descending order
  
  

Output columns: user_id, song_id, song_name, total_plays

SELECT
    COALESCE(h.user_id, w.user_id) AS user_id,
    COALESCE(h.song_id, w.song_id) AS song_id,
    COALESCE(h.song_name, w.song_name) AS song_name,
    COALESCE(h.listen_count, 0) + COALESCE(w.listen_count, 0) AS total_plays
FROM songs_history h
FULL OUTER JOIN songs_weekly w
    ON h.user_id = w.user_id
    AND h.song_id = w.song_id
ORDER BY total_plays DESC;

Explanation

Step 1: Understanding the Two Data Sources

The data is split across two tables - songs_history holds cumulative play counts up to the previous week, and songs_weekly holds plays from the current week. A user-song pair may exist in both tables, only in history (no plays this week), or only in weekly (brand new listener or song).

Step 2: Using FULL OUTER JOIN

FROM songs_history h
FULL OUTER JOIN songs_weekly w
    ON h.user_id = w.user_id
    AND h.song_id = w.song_id

A FULL OUTER JOIN ensures no data is lost. Unlike INNER JOIN (which drops non-matching rows) or LEFT/RIGHT JOIN (which drops one side), FULL OUTER JOIN preserves every row from both tables. The join matches on both user_id and song_id to identify the same user-song pair across tables.

Step 3: Handling NULLs with COALESCE

COALESCE(h.user_id, w.user_id) AS user_id
COALESCE(h.song_id, w.song_id) AS song_id
COALESCE(h.song_name, w.song_name) AS song_name

When a pair exists in only one table, columns from the other table are NULL. COALESCE picks the first non-NULL value, ensuring the output always has valid user, song, and name data regardless of which table the record came from.

Step 4: Calculating Total Plays

COALESCE(h.listen_count, 0) + COALESCE(w.listen_count, 0) AS total_plays

For pairs in both tables, this adds historical and weekly counts together. For pairs in only one table, COALESCE converts the missing side's NULL to 0, so the sum equals the existing count. This handles all three scenarios in a single expression.

Step 5: Sorting the Results

ORDER BY total_plays DESC

The final output is sorted by the cumulative play count in descending order, showing the most-played user-song pairs first.