Preppin Data with SQL (2024Wk2)

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:

and here are the tasks:

Let's get to it!

Step 1: Input the Data and take a look

easy peasy.

Step 2: Union the Tables together

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().

Step 4: Aggregate to Median Price per Quarter/Flow Card/Price.

Time for some GROUP BY goodness.

Step 4: Aggregate to Min Price per Quarter/Flow Card/Price.

Step 5: Aggregate to Max Price per Quarter/Flow Card/Price.

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:

and now for the pivots... one for each table!

We're getting somewhere now!

Step 7: Union the pivoted tables together

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:

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!

Author:
Dominic Brady
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab