Challenge #338: Let's Summarize!

A solution to last week’s challenge can be found here.

Are you wondering which artists produced the top songs? Do you want some insights? This challenge is for you.

The following dataset contains information about the top 500 songs. Using the dataset, find the top 50 artists with the most albums and the highest average rating. Include the following columns in your list :

- Artist(s)
- Number of albums (Descending)
- Average rating based on all albums (Descending)
- Album title(s)
- Average number of reviews based on all albums (Descending)
- Genre(s)
- Year the first album was released
- Year the last album was released

Note: If in your solution you have null records for the Release Year, do not include them.

This challenge was updated to propose 2 start files and 2 solutions.

challenge_338_start_file.yxzp

challenge_338_solution.yxmd

challenge_338_start_file_updated.yxzp

challenge_338_solution_updated.yxzp

Parse

Preparation

Intermediate

Core

Transform

Accepted answers

All comments

RolandSchubert

Spoiler

challenge_338_solution_rsc.yxzp

peter_gb

I was unable to locate any records with a Null release year using the following:

Spoiler

Year: Right([rel_date],4)

This obviously means my solution doesn't match but I'm happy with it I think - please correct me if I'm mistaken!

Spoiler

Still, a fantastic challenge for learning some alternative configs of the Summarise tool.

challenge_338_pgb.yxmd

Kenda

Spoiler

Agree expectations after reading prompt versus given output don't match, but here is my attempt...

challenge_338_start_file.yxmd

patrick_digan

Spoiler

I didn't like how the genres were repeated so many times, so I cleaned that field up a little bit with some extra summarizes.

challenge_338_start_file.yxmd

MilindG

Spoiler

Solution 338.yxmd

binu_acs

Spoiler

challenge_338_solution_BV.yxmd

ahsanaali

Spoiler

Removing duplicate Genre(s)

challenge_338_Solution_AAA.yxmd

mmontgomery

Challenge #338

Spoiler

c_338.yxzp

Sebastian_Chaieb2

My Solution in attachment.

However! I have to disagreed wth the presented results (or I did not understand the objectivse) but in example of Artist "", in the expected output for this artst wa no Max date (last release date) and min was also wrong year.

In my solution I user RegEx for year parsing.

Spoiler

Thanks,

Sebastian

challenge_338_solution_SC.yxzp

AkimasaKajitani

My solution.

Spoiler

The results did not match the sample output
.

challenge_338_start_file_AK.yxzp

gabrielpereira

Solution

challenge_338_Gabriel.yxmd

Ruban

Spoiler

challenge_338_completed.yxmd

Pete_Rodgers

Spoiler

controversy... i didn't get the answer, but i think i'm right. As others have said, i didn't get any null years as i took the last 4characters from the date string as the year, so that may have affected the data. Poor John Coltrane was ignored, but i think he should be joint 3rd.

338 - PR complete - 260922.yxmd

ggruccio

I may have handled some of the elements differently than the solution if I were performing the analysis --- for instance I probably would have calculated an average for the artist using a weighted average of all reviews and...

Spoiler

...I also used the min max year the albums were released for each artist instead of which came first and last in the list....

challenge_338_gg_finish.yxzp

acarter881

Here's my solution.

Spoiler

challenge_338_start_file.yxmd

allwynthomas24

Thank You @AYXAcademy for this lovely challenge. Enjoyed working on this.

Spoiler

challenge_338_solution_AllwynThomas.yxmd

joelmiller66

Screenshot 2022-09-26 091000.png

Spoiler

challenge_338_JM_ABDC.yxmd

alexnajm

Spoiler

I think like most, there were a) no null records (a RIGHT function did the trick) which resulted in b) a differing output. I also removed Various Artists (not particularly clear in the instructions). I realized later that I didn't need the second Summarize but moving forward 😊
Challenge 338.PNG

challenge_338_start_file_Alex Abi-Najm.yxmd

Sharod100

Here is my solution

challenge_338_Sharod_Hinton.yxmd

Michael_Dooley

I didn't match results but think I have the right answer...

Why no love for John Coltrane and his 12 albums?

Album years differ as well but reconcile to data. Solution has odd results like first year = 1996 and last year = 1983?

Spoiler

leebria3

Here is my solution.

My result doesn't exactly match with the suggested output due to 'John Coltrane', but wanted to share what I think the result should be.

Spoiler

challenge_338_start_file_bria03.yxzp

Clifford_Coon

My Solution:

The First/Last year in the provide result seem to be in the row order not year order.

Spoiler

challenge_338_solution_ccc.yxmd

TurboToad

Not sure what the note was supposed to mean since I didn't see any results without a year value. Output didn't exactly match but captured the ask.

Spoiler

TT_challenge_338_solution_file.yxzp

Pawel_Paleczny

Quite different result

challenge_338_solution_PP.yxzp

rachel_lynch

I do not return any null release years using this formula:

IIF(REGEX_Match(Right([rel_date],4),"\d{4}"),Right([rel_date],4),Null())

Spoiler

challenge_338_rl.yxzp

Qiu

I also noticed discrepancies with the provided resutls.

Spoiler

challenge_338_start_file-Qiu.yxzp

balajilolla1

Spoiler

Solution Attached

challenge_338_start_file.yxmd

lugesy

My outputs are also not matched exactly with the start file's outputs.

DanFlint

Spoiler

lugesy

My outputs don't match with start file's outputs. 3rd record for John Coltrane is missing in the start file's output. (Updated)

challenge_338_solution_ADC.yxmd

sergejs_kutkovics

As have been mentioned in many replies, dataset result doesn't match the output provided. I believe First and Last year mean oldest and newest album respectively. I used min and max year functionin Summarize, not just First and Last one.
Also made a list of distinct genres for each artist.

Spoiler

challenge_338_SK.yxmd

JokeFun

Me too, there's no Null year records. And I have a different result. For example, the year of the first album for Miles Davis is 1957, but the result has 1959. I think my result is correct.

JokeFun

I don't think num-rat can be directly converted from string to number by using the select tool just once. It will omit the numbers after the comma. So in my solution, I used the cleansing tool first to remove the comma and then convert the data format. It seems many people missed this point.

AdwaitTarudkar

Thanks for sharing!

I also got "John Coltrane" in my output but that artist is not present in the desired output even though this record satisfies the condition.

AdwaitTarudkar

Will skipping 1st work if the data is live or needs an update?

ramesh_neel

Here is my solution.

challenge_338_RN_Solution.yxmd

bpiscoya

I think that if we SORT by release date (ascending ) BEFORE summarizing the albums concatenation will match. This is not stated in the problem, but might be inferred based on the answer output.

Moreover, the "Various Artists" is ruled out implicitly I guess

Tawab

it was intresting that you have used summerize for min and max date

3I_ATLAS

The message body contains b-u-m-s, which is not permitted in this community. Please remove this content before sending your post.

Exactly! As @Sebastian_Chaieb2 , @alexnajm ; @ggruccio and @peter_gb and others have mentioned, this challenge could have had much clearer better instructions.

"The following dataset contains information about the top 500 songs." - The dataset only has Alb-u-m-s data! There are no songs there!

" with the most al-b-u-m-s and the highest average rating." - these are two distinct groups that cannot be made into one coherent dataset.

So, It becomes a consulting exercise in which we ignore the instructions to some degree and divine for ourselves the metric the client is trying to determine. If you arrived at a different answer than the one provided, you are probably better off!

I personally will go quality over quantity. Bowie has only 7 albums (still in the top 50 alb-u-m count) but with a beats both John Coltrane (12) and Miles Davis (17), with a 4.015714 average rating.

Spoiler

challenge_338_Solution.yxzp

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177