Let's continue with out SQL preppin challenges! This time week 2: https://preppindata.blogspot.com/2024/01/2024-week-2-average-price-analysis.html
Here is what the input files look like:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-111.png)
and here are the tasks:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-112.png)
Let's get to it!
Step 1: Input the Data and take a look
![](https://www.thedataschool.co.uk/content/images/2025/01/image-113.png)
easy peasy.
Step 2: Union the Tables together
![](https://www.thedataschool.co.uk/content/images/2025/01/image-116.png)
Nothing to it.
Step 3: Convert Date Field to a Quarter Number
For this we will need the DATE_PART() function. Don't forget to make the date field into a date format using TO_DATE().
![](https://www.thedataschool.co.uk/content/images/2025/01/image-117.png)
Step 4: Aggregate to Median Price per Quarter/Flow Card/Price.
Time for some GROUP BY goodness.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-121.png)
Step 4: Aggregate to Min Price per Quarter/Flow Card/Price.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-122.png)
Step 5: Aggregate to Max Price per Quarter/Flow Card/Price.
![](https://www.thedataschool.co.uk/content/images/2025/01/image-124.png)
Step 6: Create three separate flows (tables for us) for each aggregated measure, and pivot to have a column per class for each quarter and whether the passenger had a flow card or not.
This one is a biggie. Firstly the tables:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-125.png)
and now for the pivots... one for each table!
![](https://www.thedataschool.co.uk/content/images/2025/01/image-127.png)
We're getting somewhere now!
Step 7: Union the pivoted tables together
![](https://www.thedataschool.co.uk/content/images/2025/01/image-128.png)
Step 8: Rename Economy to First/First Class to Economy/Business Class to Premium/Premium Economy to Business
If you are like me and your field titles ended up with "" marks around the field names you won't be able to select them. All is not lost. Create a table alias when selected the fields, and put your new field titles within the brackets:
![](https://www.thedataschool.co.uk/content/images/2025/01/image-133.png)
Success! One missed step on this challenge (automatic in Tableau Prep) was that I should have added a field for each pivoted table referring to the table name (min median max) to keep track on which number is which upon unioning the set. A simple enough addition to the process:
SELECT *
,'identifier' // in our case 'median', 'min', or 'max
FROM TABLE
Anyhow that is all from me today, bring on week 3!