DSIE 6.3.2 music actions

We need to use consider two cases.

  • (client_id, music_id) pairs exist in the summary but not in the new table
  • (client_id, music_id) pairs exist in the new table but not in the summary

Therefore, full outer join is used here.

WITH new AS (
SELECT client_id,
COUNT(*) AS num_play,
MAX(snapshot_day) AS end_date
FROM music_action
GROUP BY client_id, music_id
SELECT CASE WHEN new.client_id IS NULL THEN summary_table.client_id
ELSE new.client_id END AS client_id,
COALESCE(new.num_play, 0) + COALESCE(summary_table.num_play, 0) AS num_play,
CASE WHEN new.music_id IS NULL THEN summary_table.music_id
ELSE new.music_id END AS music_id,
CASE WHEN new.end_date IS NULL THEN summary_table.end_date
ELSE new.end_date END AS end_date
FROM new
FULL OUTER JOIN summary_table
ON new.client_id = summary_table.client_id
AND new.music_id = summary_table.music_id;