Top 25 Songs at Number 1, Billboard Hot 100SQL

Editor's note: do you like looking at data around the top music, including when an album debuted and when it appeared on the Billboard Hot 100 list? David has you covered with some simple analytical SQL, along with a MotherDuck share of the data.

Attach and select MotherDuck database
Data shared/available on MotherDuck

ATTACH 'md:_share/music/af2a4a73-d4ef-4c82-98ef-e49f60adccd2' AS music;
USE music;
Copy code

Execute this SQL

-- Switch to the attached music database
use music;

-- Top 25 songs at number 1, Billboard Hot 100
SELECT song, performer, 
  COUNT(*) AS weeks_at_no_1,
  chart_debut,
  MIN(chart_date) AS first_chart_date_as_no_1, 
  MAX(chart_date) AS last_chart_date_as_no_1
FROM billboard_hot_100
WHERE chart_position = 1
GROUP BY ALL
ORDER BY weeks_at_no_1 DESC, chart_debut
LIMIT 25;
Copy code

David Neal

Edited 02/23/24

Top 25 Songs at Number 1, Billboard Hot 100SQL

Attach and select MotherDuck databaseData shared/available on MotherDuck

Execute this SQL

Attach and select MotherDuck database
Data shared/available on MotherDuck