# Understanding Logic and Intepretation

741 Reputation points
2021-02-11T10:29:20.98+00:00

Hello Community,

As you probably know, I have been practicing my SQL skills.

I'm undertaking a number of SQL exercises from https://www.stratascratch.com/.

I have been trying to compile the SQL query to answer the following logic:

Days At Number One
Calculate the number of days each number one track stayed in the number one position. Output the track name along with the corresponding number of days at number one and order records by the track name alphabetically.

I was wondering how you would approach this question (just with the statement above). I understand in the real world you would ask more questions to understand the logic. However, I just wanted to hear how some of you would approach this question?

If you could provide an answer that would be great. I have included the dataset below:

``````CREATE TABLE spotify_worldwide_daily_song_ranking (
id int,
position int,
trackname nvarchar(100),
artist nvarchar(50),
streams int,
url nvarchar(100),
date date,
region nvarchar(50))

INSERT spotify_worldwide_daily_song_ranking VALUES
(776822,1,N'Bad and Boujee (feat. Lil Uzi Vert)',N'Migos',1823391,N'https://open.spotify.com/track/4Km5HrUvYTaSUfiSGPJeQR',CONVERT(DATETIME, '2017-01-27', 120),N'us'),
(792422,1,N'HUMBLE.',N'Kendrick Lamar',3526246,N'https://open.spotify.com/track/7KXjTSCq5nL1LoYtL7XAwS',CONVERT(DATETIME, '2017-04-15', 120),N'us'),
(818222,1,N'Look What You Made Me Do',N'Taylor Swift',3828478,N'https://open.spotify.com/track/2VjtYe7gpfUi2OkGxR2O2z',CONVERT(DATETIME, '2017-08-25', 120),N'us'),
(793222,1,N'HUMBLE.',N'Kendrick Lamar',3255141,N'https://open.spotify.com/track/7KXjTSCq5nL1LoYtL7XAwS',CONVERT(DATETIME, '2017-04-19', 120),N'us'),
(792822,1,N'HUMBLE.',N'Kendrick Lamar',3337001,N'https://open.spotify.com/track/7KXjTSCq5nL1LoYtL7XAwS',CONVERT(DATETIME, '2017-04-17', 120),N'us'),
(792222,1,N'HUMBLE.',N'Kendrick Lamar',4068152,N'https://open.spotify.com/track/7KXjTSCq5nL1LoYtL7XAwS',CONVERT(DATETIME, '2017-04-14', 120),N'us'),
(793422,1,N'HUMBLE.',N'Kendrick Lamar',3144482,N'https://open.spotify.com/track/7KXjTSCq5nL1LoYtL7XAwS',CONVERT(DATETIME, '2017-04-20', 120),N'us'),
(793622,1,N'HUMBLE.',N'Kendrick Lamar',3172718,N'https://open.spotify.com/track/7KXjTSCq5nL1LoYtL7XAwS',CONVERT(DATETIME, '2017-04-21', 120),N'us'),
(793022,1,N'HUMBLE.',N'Kendrick Lamar',3394456,N'https://open.spotify.com/track/7KXjTSCq5nL1LoYtL7XAwS',CONVERT(DATETIME, '2017-04-18', 120),N'us'),
(696149,2,N'Perfect Duet (Ed Sheeran & Beyonc√©)',N'Ed Sheeran',3815,N'https://open.spotify.com/track/1bhUWB0zJMIKr9yVPrkEuI',CONVERT(DATETIME, '2017-01-12', 120),N'ee'),
(792423,2,N'DNA.',N'Kendrick Lamar',3013496,N'https://open.spotify.com/track/6HZILIRieu8S0iqY8kIKhj',CONVERT(DATETIME, '2017-04-15', 120),N'us'),
(792223,2,N'DNA.',N'Kendrick Lamar',3643231,N'https://open.spotify.com/track/6HZILIRieu8S0iqY8kIKhj',CONVERT(DATETIME, '2017-04-14', 120),N'us'),
(482402,4,N'Versace On The Floor',N'Bruno Mars',144230,N'https://open.spotify.com/track/0kN8xEmgMW9mh7UmDYHlJP',CONVERT(DATETIME, '2017-04-13', 120),N'ph'),
(28007,8,N'Una Lady Como T√∫',N'Manuel Turizo',14464,N'https://open.spotify.com/track/7MHN1aCFtLXjownGhvEQlF',CONVERT(DATETIME, '2017-05-21', 120),N'ec'),
(306207,8,N'Alone',N'Alan Walker',92447,N'https://open.spotify.com/track/0JiVRyTJcJnmlwCZ854K4p',CONVERT(DATETIME, '2017-02-17', 120),N'no'),
(442607,8,N'Perfect Duet (Ed Sheeran & Beyonc√©)',N'Ed Sheeran',90487,N'https://open.spotify.com/track/1bhUWB0zJMIKr9yVPrkEuI',CONVERT(DATETIME, '2017-12-28', 120),N'it'),
(301207,8,N'Make Me (Cry)',N'Noah Cyrus',81932,N'https://open.spotify.com/track/2BrzlUj1u1CtvaJDGIKpsP',CONVERT(DATETIME, '2017-01-23', 120),N'no'),
(496406,8,N'Attention',N'Charlie Puth',113556,N'https://open.spotify.com/track/4iLqG9SeJSnt0cSPICSjxv',CONVERT(DATETIME, '2017-06-25', 120),N'ph'),
(546207,9,N'Castle on the Hill',N'Ed Sheeran',15127,N'https://open.spotify.com/track/6PCUP3dWmTjcTtXY02oFdT',CONVERT(DATETIME, '2017-02-20', 120),N'tw'),
(54008,9,N'Una Lady Como T√∫',N'Manuel Turizo',14966,N'https://open.spotify.com/track/7MHN1aCFtLXjownGhvEQlF',CONVERT(DATETIME, '2017-01-10', 120),N'ec'),
(317408,9,N'Scared to Be Lonely',N'Martin Garrix',71259,N'https://open.spotify.com/track/3ebXMykcMXOcLeJ9xZ17XH',CONVERT(DATETIME, '2017-04-14', 120),N'no'),
(119208,9,N'Mobali',N'Siboy',81790,N'https://open.spotify.com/track/6xcXAVbDuVT1pCsa2rmDHY',CONVERT(DATETIME, '2017-08-17', 120),N'fr'),
(107808,9,N'Chocolat (feat. Awa)',N'Lartiste',93144,N'https://open.spotify.com/track/4fwtP5khM1iEoa6BP1QQsX',CONVERT(DATETIME, '2017-06-21', 120),N'fr'),
(145609,10,N'Bling Bling',N'Kaaris',97760,N'https://open.spotify.com/track/4s7OqcUEyLJ1RkbzBopRGt',CONVERT(DATETIME, '2017-12-27', 120),N'fr'),
(869079,10,N'Ahora Dice',N'Chris Jeday',4543,N'https://open.spotify.com/track/22eADXu8DfOAUEDw4vU8qy',CONVERT(DATETIME, '2017-08-30', 120),N'sv'),
(1044724,10,N'Vacaciones',N'Wisin',86922,N'https://open.spotify.com/track/3dQDid3IUNhZy1OehIfYfE',CONVERT(DATETIME, '2017-02-28', 120),N'cl'),
(972724,10,N'You Don''t Know Me - Radio Edit',N'Jax Jones',255434,N'https://open.spotify.com/track/00lNx0OcTJrS3MKHcB80HY',CONVERT(DATETIME, '2017-11-03', 120),N'de'),
(413409,10,N'El Party',N'Jake La Furia',100715,N'https://open.spotify.com/track/5xyExId5XWZplBvjmbIuy6',CONVERT(DATETIME, '2017-04-08', 120),N'it'),
(337813,14,N'Party On The West Coast (feat. Snoop Dogg)',N'Matoma',73059,N'https://open.spotify.com/track/45OfR7ugJMgbFDuNOVpIq3',CONVERT(DATETIME, '2017-07-28', 120),N'no'),
(462814,16,N'Perfect Strangers',N'Jonas Blue',109998,N'https://open.spotify.com/track/1CUVN2kn7mW5FjkqXTR2W1',CONVERT(DATETIME, '2017-05-01', 120),N'ph'),
(479815,17,N'Touch',N'Little Mix',96248,N'https://open.spotify.com/track/5Ua3GXyHwiSfpNTMjq6m2z',CONVERT(DATETIME, '2017-03-31', 120),N'ph'),
(273818,19,N'Too Good At Goodbyes - Edit',N'Sam Smith',37747,N'https://open.spotify.com/track/0mel2N9Ws9r4yLQn5QE21Y',CONVERT(DATETIME, '2017-09-17', 120),N'fi'),
(542617,19,N'‰Ω†,Â•Ω‰∏çÂ•Ω? - Ending Theme Song of TVBS Series "Life List"',N'Eric Chou',10600,N'https://open.spotify.com/track/2gug6MRv4xQFYi9LA3PJCS',CONVERT(DATETIME, '2017-02-02', 120),N'tw'),
(612220,22,N'Cold Water (feat. Justin Bieber & M√ò)',N'Major Lazer',21442,N'https://open.spotify.com/track/6DNtNfH8hXkqOX1sjqmI7p',CONVERT(DATETIME, '2017-10-01', 120),N'nz'),
(350824,25,N'Unforgettable',N'French Montana',46603,N'https://open.spotify.com/track/3B54sVLJ402zGa6Xm4YGNe',CONVERT(DATETIME, '2017-01-10', 120),N'no'),
(688571,34,N'Location',N'Khalid',1119,N'https://open.spotify.com/track/152lZdxL1OR0ZMW6KquMif',CONVERT(DATETIME, '2017-04-04', 120),N'ee'),
(717258,37,N'Olsun',N'Sertab Erener',15010,N'https://open.spotify.com/track/07Af1vF3LFqNrmLC1Yzs0L',CONVERT(DATETIME, '2017-10-04', 120),N'tr'),
(697054,37,N'Santa Baby',N'Eartha Kitt',1450,N'https://open.spotify.com/track/1foCxQtxBweJtZmdxhEHVO',CONVERT(DATETIME, '2017-12-23', 120),N'ee'),
(538837,39,N'Scars To Your Beautiful',N'Alessia Cara',7661,N'https://open.spotify.com/track/0prNGof3XqfTvNDxHonvdK',CONVERT(DATETIME, '2017-01-14', 120),N'tw'),
(485240,42,N'All We Know',N'The Chainsmokers',65113,N'https://open.spotify.com/track/2rizacJSyD9S1IQUxUxnsK',CONVERT(DATETIME, '2017-04-27', 120),N'ph'),
(722663,42,N'366.G√ºn',N'Sagopa Kajmer',14090,N'https://open.spotify.com/track/1EuFr1JA70TaEzlaSM4ulc',CONVERT(DATETIME, '2017-07-05', 120),N'tr'),
(863110,44,N'Bad Liar',N'Selena Gomez',1857,N'https://open.spotify.com/track/1sCxVKWImDZSZKvG0U9B23',CONVERT(DATETIME, '2017-10-07', 120),N'sv'),
(455547,44,N'First Time',N'Kygo',1224,N'https://open.spotify.com/track/2Gl0FzuLxflY6nPifJp5Dr',CONVERT(DATETIME, '2017-12-07', 120),N'lt'),
(811266,45,N'Young Dumb & Broke',N'Khalid',524715,N'https://open.spotify.com/track/5Z3GHaZ6ec9bsiI5BenrbY',CONVERT(DATETIME, '2017-07-21', 120),N'us'),
(33445,46,N'I''m the One',N'DJ Khaled',5278,N'https://open.spotify.com/track/3DXncPQOG4VBw3QHh3S817',CONVERT(DATETIME, '2017-06-20', 120),N'ec'),
(189647,48,N'Imitadora',N'Romeo Santos',52478,N'https://open.spotify.com/track/6r46lnXFbE9fr2d3KNaGbe',CONVERT(DATETIME, '2017-07-29', 120),N'ar'),
(459929,49,N'Jocelyn Flores',N'XXXTENTACION',1137,N'https://open.spotify.com/track/7m9OqQk4RVRkw9JJdeAw96',CONVERT(DATETIME, '2017-10-27', 120),N'lt'),
(428248,49,N'Galway Girl',N'Ed Sheeran',44892,N'https://open.spotify.com/track/0afhq8XCExXpqazXczTSve',CONVERT(DATETIME, '2017-10-17', 120),N'it'),
(623249,51,N'Solo Dance',N'Martin Jensen',14617,N'https://open.spotify.com/track/6HUnnBwYZqcED1eQztxMBN',CONVERT(DATETIME, '2017-06-03', 120),N'nz'),
(303651,52,N'Heart Won''t Forget',N'Matoma',28047,N'https://open.spotify.com/track/2of2DM5LqTh7ohmmVXUKsH',CONVERT(DATETIME, '2017-04-02', 120),N'no'),
(143853,54,N'Him & I (with Halsey)',N'G-Eazy',52233,N'https://open.spotify.com/track/5k38wzpLb15YgncyWdTZE4',CONVERT(DATETIME, '2017-12-18', 120),N'fr'),
(865127,56,N'Let Me Love You',N'DJ Snake',1526,N'https://open.spotify.com/track/4pdPtRcBmOSQDlJ3Fk945m',CONVERT(DATETIME, '2017-07-27', 120),N'sv'),
(546257,59,N'ÈñãÈñÄÈóúÈñÄ - From ''52Hz, I Love You'' Soundtrack / Theme Song',N'ÂºµÊ¶ïÂÆπ',6390,N'https://open.spotify.com/track/1RikH0jSgDFYXpNHGflMco',CONVERT(DATETIME, '2017-02-20', 120),N'tw'),
(727284,63,N'Affet',N'M√ºsl√ºm G√ºrses',11067,N'https://open.spotify.com/track/0ikRchpmFlaqlzLRgu9qWk',CONVERT(DATETIME, '2017-01-06', 120),N'tr'),
(41662,63,N'Sorry Not Sorry',N'Demi Lovato',3552,N'https://open.spotify.com/track/25C5CowdsfXld2jJanbiex',CONVERT(DATETIME, '2017-07-31', 120),N'ec'),
(237862,63,N'Bad and Boujee (feat. Lil Uzi Vert)',N'Migos',16628,N'https://open.spotify.com/track/4Km5HrUvYTaSUfiSGPJeQR',CONVERT(DATETIME, '2017-03-18', 120),N'fi'),
(716091,70,N'Lush Life',N'Zara Larsson',8068,N'https://open.spotify.com/track/1rIKgCH4H52lrvDcz50hS8',CONVERT(DATETIME, '2017-04-04', 120),N'tr'),
(375672,73,N'Tutto il mondo √® periferia',N'J-AX',18919,N'https://open.spotify.com/track/4OMeKnO7P2taRNhqx00Cio',CONVERT(DATETIME, '2017-01-24', 120),N'it'),
(605675,77,N'I Like Me Better',N'Lauv',5246,N'https://open.spotify.com/track/1wjzFQodRWrPcQ0AnYnvQ9',CONVERT(DATETIME, '2017-12-17', 120),N'tw'),
(194478,79,N'La √öltima Vez',N'Anuel Aa',25955,N'https://open.spotify.com/track/4J1e3hbf0e5YUZjkxyJWD3',CONVERT(DATETIME, '2017-08-22', 120),N'ar'),
(117089,90,N'Sunset Lover',N'Petit Biscuit',27642,N'https://open.spotify.com/track/0hNduWmlWmEmuwEFcYvRu1',CONVERT(DATETIME, '2017-06-08', 120),N'fr'),
(717121,100,N'ƒ∞√ßimdeki Duman',N'ƒ∞lyas Yal√ßƒ±nta≈ü',6363,N'https://open.spotify.com/track/6omqxqWZX4sCmQ7Iz4BxvM',CONVERT(DATETIME, '2017-09-04', 120),N'tr'),
(580298,100,N'All Night',N'Girls'' Generation',3808,N'https://open.spotify.com/track/1UwERmn5UogyCz6qo21BmA',CONVERT(DATETIME, '2017-12-08', 120),N'tw'),
(121902,103,N'Sunset Lover',N'Petit Biscuit',30372,N'https://open.spotify.com/track/0hNduWmlWmEmuwEFcYvRu1',CONVERT(DATETIME, '2017-08-30', 120),N'fr'),
(68304,105,N'Vacaciones',N'Wisin',2803,N'https://open.spotify.com/track/1rXojdsUqqxGj2WCmJGWHP',CONVERT(DATETIME, '2017-11-12', 120),N'ec'),
(610504,106,N'Cake By The Ocean',N'DNCE',4889,N'https://open.spotify.com/track/2aFiaMXmWsM3Vj72F9ksBl',CONVERT(DATETIME, '2017-01-01', 120),N'nz'),
(346306,107,N'Go Flex',N'Post Malone',18047,N'https://open.spotify.com/track/5yuShbu70mtHXY0yLzCQLQ',CONVERT(DATETIME, '2017-08-09', 120),N'no'),
(464107,109,N'Terrified',N'Katharine McPhee',22714,N'https://open.spotify.com/track/6vtOX9riyVzwJxj2SYTuES',CONVERT(DATETIME, '2017-11-01', 120),N'ph'),
(182513,114,N'Mala Mujer',N'C. Tangana',18833,N'https://open.spotify.com/track/6puxHBNwu2Nmm7uD3Rd2MP',CONVERT(DATETIME, '2017-06-23', 120),N'ar'),
(485513,115,N'Paano Ba Ang Magmahal',N'Piolo Pascual',25530,N'https://open.spotify.com/track/2qqte0XT0eFFVLuNP7Qena',CONVERT(DATETIME, '2017-04-28', 120),N'ph'),
(870906,116,N'Reggaet√≥n Lento (Remix)',N'CNCO',1009,N'https://open.spotify.com/track/3zoYkqS4y2BsGVShPL2nTE',CONVERT(DATETIME, '2017-09-13', 120),N'sv'),
(1040243,129,N'The Greatest',N'Sia',10531,N'https://open.spotify.com/track/7xHWNBFm6ObGEQPaUxHuKO',CONVERT(DATETIME, '2017-05-02', 120),N'cl'),
(505727,129,N'Happier',N'Ed Sheeran',25368,N'https://open.spotify.com/track/2RttW7RAu5nOAfq6YFvApB',CONVERT(DATETIME, '2017-10-08', 120),N'ph'),
(791152,131,N'Side To Side',N'Ariana Grande',224324,N'https://open.spotify.com/track/4pLwZjInHj3SimIyN9SnOz',CONVERT(DATETIME, '2017-08-04', 120),N'us'),
(720355,134,N'Panda',N'Desiigner',6048,N'https://open.spotify.com/track/5OOkp4U9P9oL23maHFHL1h',CONVERT(DATETIME, '2017-04-25', 120),N'tr'),
(512733,135,N'Set You Free',N'MYMP',25481,N'https://open.spotify.com/track/6CiuHbascZBxfFqdixSXiW',CONVERT(DATETIME, '2017-09-14', 120),N'ph'),
(895852,138,N'Let Her Go',N'Passenger',2722,N'https://open.spotify.com/track/2jyjhRf6DVbMPU5zxagN2h',CONVERT(DATETIME, '2017-02-25', 120),N'cr'),
(510338,140,N'Beautiful Soul',N'Jesse McCartney',22060,N'https://open.spotify.com/track/1HwpWwa6bnqqRhK8agG4RS',CONVERT(DATETIME, '2017-02-09', 120),N'ph'),
(327340,141,N'Be Mine',N'Ofenbach',12094,N'https://open.spotify.com/track/2KklXplRtxMsBYo474Es0w',CONVERT(DATETIME, '2017-05-06', 120),N'no'),
(46741,142,N'Would You Ever',N'Skrillex',2258,N'https://open.spotify.com/track/57p8CBvPOxrvyCbn6ttl5r',CONVERT(DATETIME, '2017-08-25', 120),N'ec'),
(242543,144,N'Leijonakuningas',N'Ellinoora',6471,N'https://open.spotify.com/track/4MsOdD6nhPuvnK9MyVAniA',CONVERT(DATETIME, '2017-10-04', 120),N'fi'),
(78344,145,N'Work from Home',N'Fifth Harmony',23421,N'https://open.spotify.com/track/4tCtwWceOPWzenK2HAIJSb',CONVERT(DATETIME, '2017-01-21', 120),N'fr'),
(93745,146,N'Lush Life',N'Zara Larsson',26126,N'https://open.spotify.com/track/1rIKgCH4H52lrvDcz50hS8',CONVERT(DATETIME, '2017-08-04', 120),N'fr'),
(137154,155,N'Shine on my Way',N'Diva Faune',24832,N'https://open.spotify.com/track/1LCdHocTe3wvSEPSISoEaQ',CONVERT(DATETIME, '2017-11-14', 120),N'fr'),
(29358,159,N'Sugar',N'Maroon 5',1830,N'https://open.spotify.com/track/494OU6M7NOf4ICYb4zWCf5',CONVERT(DATETIME, '2017-05-27', 120),N'ec'),
(85559,160,N'Someone In The Crowd - From "La La Land" Soundtrack',N'Emma Stone',17134,N'https://open.spotify.com/track/7xE4vKvjqUTtHyJ9zi0k1q',CONVERT(DATETIME, '2017-02-26', 120),N'fr'),
(525362,164,N'More Than Words',N'Extreme',21127,N'https://open.spotify.com/track/1gVgkQFOKa8Wc1HYsJtPdH',CONVERT(DATETIME, '2017-11-16', 120),N'ph'),
(893086,172,N'6 AM (Remix)',N'Enzo Ortiz',2194,N'https://open.spotify.com/track/3WHqVVRvPDWz033iPs04md',CONVERT(DATETIME, '2017-11-02', 120),N'cr'),
(242372,173,N'Laula lujempaa (Show Must Go On)',N'Haloo Helsinki!',5338,N'https://open.spotify.com/track/7twLnN62kZTfE6QaIOpyuN',CONVERT(DATETIME, '2017-09-04', 120),N'fi'),
(1046089,175,N'The Greatest',N'Sia',10060,N'https://open.spotify.com/track/7xHWNBFm6ObGEQPaUxHuKO',CONVERT(DATETIME, '2017-06-03', 120),N'cl'),
(401574,175,N'Vietato morire',N'Ermal Meta',13228,N'https://open.spotify.com/track/681fNvwhF5pF9sYiiOycPp',CONVERT(DATETIME, '2017-05-06', 120),N'it'),
(39577,178,N'Hasta el Amanecer',N'Nicky Jam',1907,N'https://open.spotify.com/track/5Fim1gaXBgsiFfsQAfQSDS',CONVERT(DATETIME, '2017-07-20', 120),N'ec'),
(92977,178,N'Mon ami',N'Still Fresh',19177,N'https://open.spotify.com/track/46P2kfhXGdwp3OJvl3INXu',CONVERT(DATETIME, '2017-04-04', 120),N'fr'),
(43994,195,N'Don''t Let Me Down',N'The Chainsmokers',1818,N'https://open.spotify.com/track/0QsvXIfqM0zZoerQfsI9lm',CONVERT(DATETIME, '2017-11-08', 120),N'ec'),
(726819,198,N'Janti',N'Murat Boz',4608,N'https://open.spotify.com/track/20NWw4dRWT67e82Jrj6x85',CONVERT(DATETIME, '2017-05-27', 120),N'tr'),
(528597,199,N'A Thousand Years',N'Christina Perri',17876,N'https://open.spotify.com/track/6lanRgr6wXibZr8KgzXxBl',CONVERT(DATETIME, '2017-02-12', 120),N'ph'),
(32998,199,N'This Is What You Came For',N'Calvin Harris',1670,N'https://open.spotify.com/track/0azC730Exh71aQlOt9Zj3y',CONVERT(DATETIME, '2017-06-17', 120),N'ec'),
(360998,199,N'Awful Things',N'Lil Peep',9911,N'https://open.spotify.com/track/0bcEHhsuLRDm712CQHomdm',CONVERT(DATETIME, '2017-11-20', 120),N'no')
``````
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,460 questions

1. 741 Reputation points
2021-02-13T13:21:52.853+00:00

Hi All,

Thanks for responding.

The expected output is as follows:

I figured out the query code myself as follows:

``````SELECT
spotify_worldwide_daily_song_ranking.trackname
,COUNT(DATEPART(d, spotify_worldwide_daily_song_ranking.date)) AS n_days_on_n1_position
FROM spotify_worldwide_daily_song_ranking
INNER JOIN spotify_daily_rankings_2017_us
ON spotify_worldwide_daily_song_ranking.trackname = spotify_daily_rankings_2017_us.trackname
AND spotify_worldwide_daily_song_ranking.date = spotify_daily_rankings_2017_us.date
GROUP BY spotify_worldwide_daily_song_ranking.trackname

``````

1. 621 Reputation points
2021-02-11T14:06:45.917+00:00

1) Calculate the number of days each number one track
2) stayed in the number one position.

1) SUM() with an appropriate GROUP BY.
2) WHERE with an appropriate condition.

2. 17,691 Reputation points
2021-02-11T19:54:31.867+00:00

This is not as straight forward as you think because of the data. The data is a stream of day by day rankings. So you need to determine when it is NOT number 1 again, by looking at future days for a new number 1.

There are several ways to do this, but this should get you started:

`````` select sr.trackname, DATEDIFF(DAY,MIN(sr.[date]),srn.[date]) as DaysNo1, MIN(sr.[date]) as [StartDate], srn.[date] as [EndDate]
from spotify_worldwide_daily_song_ranking sr
OUTER APPLY (
SELECT TOP 1 *
FROM spotify_worldwide_daily_song_ranking srn
WHERE srn.date >= DATEADD(DAY,1,sr.[date])
AND srn.position = 1
AND srn.trackname <> sr.trackname
AND srn.region = sr.region
ORDER BY srn.date
) srn
where sr.position = 1
GROUP BY srn.[date], sr.trackname
ORDER BY sr.trackname
``````

3. 24,171 Reputation points
2021-02-12T05:15:31.4+00:00

I used table sf_restaurant_health_violations as a simple example:

``````select trackname,datediff(DAY,date,nextdate) numberofdays
from (
select *,lead(date) over (order by date)  nextdate
from sf_restaurant_health_violations ) a
order by trackname
``````

Output:

``````trackname	numberofdays
Bad and Boujee (feat. Lil Uzi Vert)	101
Bank Account	117
rockstar	NULL
Shape of You	57
``````

Best regards
Melissa