SQL Interview Question from Spotify (Step-by-Step Solution)

📊 Table 1: US Daily Rankings This table stores daily Spotify song rankings for the United States in 2024. CREATE TABLE spotify_daily_rankings_2024_us ( position INT, trackname VARCHAR(255), artist VARCHAR(255), streams INT, url VARCHAR(255), date DATETIME ); Insert Sample Data TRUNCATE TABLE spotify_daily_rankings_2024_us; INSERT INTO spotify_daily_rankings_2024_us (position, trackname, artist, streams, url, date) VALUES (1, 'Track A', 'Artist 1', 500000, 'https://url1.com', '2024-01-01'), (2, 'Track B', 'Artist 2', 400000, 'https://url2.com', '2024-01-01'), (1, 'Track A', 'Artist 1', 520000, 'https://url1.com', '2024-01-02'), (3, 'Track C', 'Artist 3', 300000, 'https://url3.com', '2024-01-02'), (1, 'Track D', 'Artist 4', 600000, 'https://url4.com', '2024-01-03'), (2, 'Track E', 'Artist 5', 390000, 'https://url5.com', '2024-01-03'), (3, 'Track F', 'Artist 6', 370000, 'https://url6.com', '2024-01-03'), (1, 'Track G', 'Artist 7', 580000, 'https://url7.com', '2024-01-04'), (2, 'Track A', 'Artist 1', 510000, 'https://url1.com', '2024-01-04'), (3, 'Track C', 'Artist 3', 340000, 'https://url3.com', '2024-01-04'), (1, 'Track A', 'Artist 1', 580000, 'https://url7.com', '2024-01-05'), (2, 'Track G', 'Artist 7', 510000, 'https://url1.com', '2024-01-05'), (3, 'Track C', 'Artist 3', 340000, 'https://url3.com', '2024-01-05'); 🌍 Table 2: Worldwide Daily Song Rankings This table contains Spotify song rankings from multiple regions around the world. CREATE TABLE spotify_worldwide_daily_song_ranking ( id INT, position INT, trackname VARCHAR(255), artist VARCHAR(255), streams INT, url VARCHAR(255), date DATETIME, region VARCHAR(50) ); Insert Sample Data TRUNCATE TABLE spotify_worldwide_daily_song_ranking; INSERT INTO spotify_worldwide_daily_song_ranking (id, position, trackname, artist, streams, url, date, region) VALUES (1, 1, 'Track A', 'Artist 1', 550000, 'https://url1.com', '2024-01-01', 'US'), (2, 2, 'Track B', 'Artist 2', 450000, 'https://url2.com', '2024-01-01', 'US'), (3, 1, 'Track A', 'Artist 1', 530000, 'https://url1.com', '2024-01-02', 'US'), (4, 1, 'Track D', 'Artist 4', 610000, 'https://url4.com', '2024-01-03', 'US'), (5, 3, 'Track C', 'Artist 3', 320000, 'https://url3.com', '2024-01-03', 'US'), (6, 3, 'Track AA', 'Artist 1', 500000, 'https://url1.com', '2024-01-01', 'GB'), (7, 1, 'Track G', 'Artist 7', 480000, 'https://url7.com', '2024-01-04', 'GB'), (8, 2, 'Track AB', 'Artist 1', 520000, 'https://url1.com', '2024-01-02', 'GB'), (9, 4, 'Track F', 'Artist 6', 410000, 'https://url6.com', '2024-01-02', 'GB'), (10, 4, 'Track BB', 'Artist 2', 460000, 'https://url2.com', '2024-01-01', 'CA'), (11, 3, 'Track DD', 'Artist 4', 470000, 'https://url4.com', '2024-01-02', 'CA'), (12, 2, 'Track D', 'Artist 4', 620000, 'https://url4.com', '2024-01-03', 'CA'), (13, 1, 'Track A', 'Artist 1', 530000, 'https://url1.com', '2024-01-05', 'US');