This blog will talk through my process in solving the 2021 wk1 preppin' data challenge in SQL.
I started with this dataset:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-129.png)
Task 1:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-130.png)
To do this I decided to use the function split_part(), which splits a selected string and generates a new column. I also used a RTRIM and LTRIM to remove an whitespaces. In this instance I split based on where a "-" occurred. Because I knew that I wanted to reference these columns later I made this a CTE.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-134.png)
Task 2:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-132.png)
I decided the best way to clean up the bike column was to make use of the first letter. I therefore used a case statement, where I set a predetermined value based on the first letter of a word.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-135.png)
Task 3:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-136.png)
To do this I used the QUATER() and DAYOFMONTH() function.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-137.png)
Task 4:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-138.png)
To remove the first 10 rows I used a WHERE statement.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-143.png)
CHALLENGE COMPLETE!