Santas banging tunes 🎶

Santa was trying to pick the perfect song to play at the big after-Christmas party in the Alps. This was gonna be huge - he was DJing for the first time ever at this awesome party where elves, reindeer, and snowboarders would all hang out together. One of his smart elves helped him out by making a computer program that looked at what songs the elves had been listening to lately. It showed which songs people actually listened to all the way through and played over and over. Santa was super excited to DJ, knowing he'd find just the right banger to get everyone dancing and make the party totally epic!

Example schema

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);
CREATE TABLE songs (
    song_id INT PRIMARY KEY,
    song_title VARCHAR(255) NOT NULL,
    song_duration INT  -- Duration in seconds, can be NULL if unknown
);
CREATE TABLE user_plays (
    play_id INT PRIMARY KEY,
    user_id INT,
    song_id INT,
    play_time DATE,
    duration INT,  -- Duration in seconds, can be NULL
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (song_id) REFERENCES songs(song_id)
);

Example data:

-- Inserting data into users table
INSERT INTO users (user_id, username) VALUES (1, 'alice');
INSERT INTO users (user_id, username) VALUES (2, 'bob');
INSERT INTO users (user_id, username) VALUES (3, 'carol');

-- Inserting data into songs table, including a song with a NULL duration
INSERT INTO songs (song_id, song_title, song_duration) VALUES (1, 'Jingle Bells', 180);
INSERT INTO songs (song_id, song_title, song_duration) VALUES (2, 'Silent Night', NULL); -- NULL duration
INSERT INTO songs (song_id, song_title, song_duration) VALUES (3, 'Deck the Halls', 150);

-- Inserting example play records into user_plays table, including NULL durations
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (1, 1, 1, '2024-12-22', 180);  -- Full play
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (2, 2, 1, '2024-12-22', 100);  -- Skipped
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (3, 3, 2, '2024-12-22', NULL); -- NULL duration (unknown play)
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (4, 1, 2, '2024-12-23', 180);  -- Valid duration, but song duration unknown
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (5, 2, 2, '2024-12-23', NULL); -- NULL duration
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (6, 3, 3, '2024-12-23', 150);  -- Full play

-- Additional plays with NULLs and shorter durations
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (7, 1, 3, '2024-12-23', 150);  -- Full play
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (8, 2, 3, '2024-12-22', 140);  -- Skipped
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (9, 3, 1, '2024-12-23', NULL); -- NULL duration
INSERT INTO user_plays (play_id, user_id, song_id, play_time, duration) VALUES (10, 1, 3, '2024-12-22', NULL); -- NULL duration

Example result:

   song_title   | total_plays | total_skips 
----------------+-------------+-------------
 Deck the Halls |           4 |           2
 Silent Night   |           3 |           2
 Jingle Bells   |           3 |           2

Example to submit:

Deck the Halls

psql postgres;

CREATE DATABASE santa_workshop;

\c santa_workshop

\i ~/downloads/advent_of_sql_day_24.sql