Understanding Logic and Intepretation

Question

Understanding Logic and Intepretation

Carlton Patterson 741

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'),
(139245,46,N'Django',N'Dadju',77111,N'https://open.spotify.com/track/51PPDB9v8QOMkiUe06eYfc',CONVERT(DATETIME, '2017-11-25', 120),N'fr'),
(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'),
(13789,190,N'Hello',N'Adele',1537,N'https://open.spotify.com/track/4sPmO7WMQUAf45kwMOtONw',CONVERT(DATETIME, '2017-10-03', 120),N'ec'),
(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')

Carlton Patterson 741 Reputation points

2021-02-11T10:41:34.107+00:00

I forgot add another dataset, but I'm being prevented to modify the question
Carlton Patterson 741 Reputation points

2021-02-11T10:51:50.083+00:00

CREATE TABLE sf_restaurant_health_violations (
position int,
trackname varchar(50),
artist varchar(50),
streams int,
url varchar(100),
date date)

INSERT sf_restaurant_health_violations VALUES
(1,'Shape of You','Ed Sheeran',1467892,'https://open.spotify.com/track/7qiZfU4dY1lWllzX7mPBI3',CONVERT(DATETIME, '2017-09-02', 120)),
(1,'Bank Account','21 Savage',1176086,'https://open.spotify.com/track/5eqK0tbzUPo2SoeZsov04s',CONVERT(DATETIME, '2017-05-08', 120)),
(1,'rockstar','Post Malone',2072302,'https://open.spotify.com/track/7wGoVu4Dady5GV0Sv4UIsx',CONVERT(DATETIME, '2017-10-29', 120)),
(1,'Bad and Boujee (feat. Lil Uzi Vert)','Migos',1823391,'https://open.spotify.com/track/4Km5HrUvYTaSUfiSGPJeQR',CONVERT(DATETIME, '2017-01-27', 120))

Carlton Patterson 741 Reputation points

2021-02-11T10:41:34.107+00:00

I forgot add another dataset, but I'm being prevented to modify the question
Carlton Patterson 741 Reputation points

2021-02-11T10:51:50.083+00:00

CREATE TABLE sf_restaurant_health_violations (
position int,
trackname varchar(50),
artist varchar(50),
streams int,
url varchar(100),
date date)

INSERT sf_restaurant_health_violations VALUES
(1,'Shape of You','Ed Sheeran',1467892,'https://open.spotify.com/track/7qiZfU4dY1lWllzX7mPBI3',CONVERT(DATETIME, '2017-09-02', 120)),
(1,'Bank Account','21 Savage',1176086,'https://open.spotify.com/track/5eqK0tbzUPo2SoeZsov04s',CONVERT(DATETIME, '2017-05-08', 120)),
(1,'rockstar','Post Malone',2072302,'https://open.spotify.com/track/7wGoVu4Dady5GV0Sv4UIsx',CONVERT(DATETIME, '2017-10-29', 120)),
(1,'Bad and Boujee (feat. Lil Uzi Vert)','Migos',1823391,'https://open.spotify.com/track/4Km5HrUvYTaSUfiSGPJeQR',CONVERT(DATETIME, '2017-01-27', 120))

Answer 1

Carlton Patterson 741

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

MelissaMa-MSFT 24,171 Reputation points

2021-02-16T06:49:05.157+00:00

Hi @Carlton Patterson ,

Thanks for your update.

It seems that you already found out the query with expected output, you could accept your own answer or other answers which is helpful.

In addition, according to your DDL and sample data provided, your query is not working.

It is recommended to provide the correct DDL and sample data together with the expected output so that all of us could work on it. Thanks.

Best regards
Melissa

Answer 2

Stefan Hoffmann 621

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.

Answer 3

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

Answer 4

Hi @Carlton Patterson ,

Please help provide your expected output so that we could proceed.

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

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Understanding Logic and Intepretation

3 additional answers

Activity