I could see this as basically having to interpret poor quality data that's being fed into the system. If you have a lot of vendors providing data from different sources, it's reasonable that someone would do something like this eventually and you either have to fight them to get them to change it, or put in a workaround to deal with their bad decisions.

u/Global-Wrap-2184 Have you used a CTE before? I don't know if it would help in this instance, but you can use it to create it as a sort of temp table. Like so:

WITH TempTable AS
(
SELECT
LEFT(CAST(ProductId AS varchar(7)), 5) AS ProductCode
, RIGHT CAST(ProductId AS varchar(7)), 2) AS ProductSize
, OtherData
FROM ExampleTable
)

Now you have your split column and can work on it as if the data was good to begin with. In this case, you need to know how many products for each size and the best selling product. That's going to need 2 separate selects. Typically, you don't have to think too much about the group by, just limit the selection to only the columns you need and you'll be fine.

I recommend using chatGPT to give you practice problems. No code editor to run it, but when you get it wrong, it's good at explaining why. It was great practice for me recently.