Intermediate SQL: Conditional Transformation

Source: Intermediate SQL by Data Camp

Enjoying the Weather

So far we have learnt how to create new rows by applying a formula to existing rows — This has been incredibly helpful but what happens when you only apply a formula to certain rows and not all.

Perhaps, As an NFL Draft Analyst — you want to see a different formula applied to the players in different conferences (SEC, Big Ten, Big 12 etc) in a list of all NFL Draft eligible player

What if you want to apply a different tax rate to each citizen in a town depending on their income. How do we do this?

New Case Study: Airline Analytics

Dubai Airport is the busiest airport in the world, over 90 million passengers go through it every year. We work for an airline and we are investigating whether there is a relationship between flights distance & delays.
Question: Are longer flights more likely to be delayed?

This is important to enhance passenger experience and operational scheduling and efficiency

Excellent Data Analyst start with the end in mind — how can I answer this question and how can I present this data.

For our question, we want to produce a summary table with this structure

Source: DataCamp

1. delay_status : This categorise the flight into 3 seperate group based on the time of arrival
2. early : The flight arrived at least 5 minutes before the scheduled time
3. on_time : The flight arrived within 5 minutes of the scheduled time
4. late : The flight arrived more than 5 minutes after the scheduled time
5. avg_distance : The average flight distance for each category of flight

This table will answer our question on whether delayed flight have longer distance.

Here is our input data

Source: DataCamp

This data is what we will be working with let’s break down each column.

1. flight_id : A unique flight identified number
2. distance : Distance from origin airport to destination airport (this is measured in km or miles)
3. distance_unit : whether distance is in km or miles
4. delay : The difference between scheduled and actual arrivale time (in minutes). Negative mean late, positive means early

So how would we solve this problem using this table

1. We would standardise the distance into either KM or miles
2. We would then create an extra column to group each row into either early, on_time or late

So we know we will do two transformations

1. Standardise distance: Convert miles to km using a conversion formula — creating distance_km
2. Categorise delays: Create delay_status based on every thresholds you identified

Here how we will break it down:

1. Data Transformation: Standardise all data to KM in a new distance_km column and create a new column called delay_status that will classify each row as early , on_time or late depending on the delay minutes
2. Data Aggregation: calculate the average distance for each delay_status group

Let’s switch to code:

We will start with the 1st step which is Data Transformation -> converting to KM

What transformation do we need?

if all the distance were in miles — we would simply multiply each row’s distance by 1.60934 but they aren’t. They are a mix of km and miles.

So what do we do here?

Our solution is more complex — we need to check each distance_unit before we apply the transformation:

1. If distance_unit is miles: Multiply the row’s distance by 1.60934
2. if distance_unit is KM: simply just enter the number in the new row distance_km

Note: we are apply a different transformation depending on the condition of the row. This is called conditional transformation.

We apply a different transformation for each row depending on which conditions are met.

This is the code:

SELECT flight_id,
       distance,
       distance_unit,
       CASE
            WHEN distance_unit = 'miles' THEN distance * 1.60934
            ELSE distance
       END AS distance_km,
       delay
FROM flight;

Source: DataCamp

We can see the new column distance_km — it shows all the distance scheduled to KM. We can see from row 0, distance_km is 7902.95 because the distance_unit is km. and for row 1, distance_km is 12624.3399928 because distance_unit is miles. It applied the transformation 7844.42 * 1.60934 = 12624.34

Let’s break down the CASE WHEN statement

• CASE start the conditional transformation
• WHEN distance_unit = 'miles' check the condition for each row
• THEN distance_unit * 1.60934 if the condition is true, it applies the transformation
• ELSE distance if condition is false, distance_km = distance
• END as distance_km — closes the statement and names the new column

For a single transformation like the aforementioned, we simply have a binary choice:

• One value when the condition is TRUE
• Another value when the condition is FALSE

Now, let’s tackle our second transformation need: creating delay_status column.

Get Stephen Ayeni’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Remember our goal is to investigate if flight distance correlates to crucial delays. We have standardized the distance to kilometres. Now we are going to categorise flight by delay status

What transformation logic do we need?

We are creating a new column called delay_status based on the following condition:

1. if delay <= 5minutes — > ‘Early’
2. if delay > — 5 OR delay < 5 -> ‘On Time’
3. If delay > = 5 minutes -> ‘Late’

This is different that earlier transformation as we have multiple conditions which create multiple categories. The earlier transformation was binary (2 categories so 1 condition)

This is called binning. We are grouping numerical values into discrete categories based on thresholds

so how do we implement this?

We create a condition for each category. In SQL, CASE WHEN statements can handle multiple conditions by intentionally stacking the WHEN & THEN clauses.

Let’s see the full code:

SELECT flight_id,
       distance,
       distance_unit,
       CASE
           WHEN distance_unit = 'miles' THEN distance * 1.60934
           ELSE distance
       END AS distance_km,
       delay
       CASE
           WHEN delay < = -5 THEN 'Early'
           WHEN delay < 5 THEN 'On Time'
           ELSE 'Late'
       END AS delay_status
FROM flights;

Source: DataCamp

Let’s break down the second conditional transformation

The delay_status column is correctly classifying each flight into Early, On Time and Late

How?

Multi-conditional CASE WHEN statement on Line 9 -13

• CASE : open conditional statement
• WHEN delay < = -5 THEN ‘Early’ : first condition — if delay is less than -5 assign ‘Early’
• WHEN delay < 5 THEN ‘On Time’ : second condition — if delay is < 5, assign ‘On Time’
• ELSE ‘Late’ : catch all, anything that did not match the above get late
• END AS delay_status : This creates a new column

The key here is that you can stack as much in the WHEN THEN clause as you like, one per category

Also notice, we did not have to write delay > = 5 for ‘Late’ because it just catches all other rows that do not fall withiun the other 2 conditions

But what happens if a row is true for multiple conditions for example the row with flight_id F101. It is true for both delay < = -5 and delay < 5

We can see F101 has a delay of -5 and this holds for delay < = -5 (Early) and delay < 5 (On Time)

In this case, the first matching condition wins and SQL stops evaluating the rest

You can see this in F101 (delay = -5)

1st Condition: is delay < = -5 -> Yes and it assigns Early and stop checking other conditions

For F103 (delay = 0)
1st Condition: is delay < = -5 -> No
Check next
2nd Condition: is delay < 5 -> Yes — assigns ‘On Time’ and stop checking other conditions

This is why we didn’t need to create a long condition for delay > 5
If delay isn’t caught by 1st condition or 2nd condition — It will surely be by the 3rd condition.

This is a 3 step framework that would help simplify Conditional Tranformation

1. Define the output
   What columns are you creating?
   What values should they contain?
   Example: delay_status with ‘Early’, ‘On Time’ or ‘Late’
2. Map condition to outcomes
   For each condition, what condition identifies it and what values does it get?
   e.g. delay < = -5 -> ‘Early’
3. Set the default:
   What happens when no condition matches — this is your ‘ELSE’ Clause and it is often the most popular category
   e.g. anything not on ‘Early’ or ‘On Time’ -> ‘Late’

For example, in your work as an NFL Draft Analyst, you might want to classify recievers in the SEC.
So we define the output (level as): Elite, High, Average, Below Average
1. Map condition to outcomes: Elite = over 1000 yards in 2026 season, High = over 800 yards in 2026 season, Average = over 500 yards in 2026 season, Below Average = ELSE

Now, we can bring everything together: Transformation & Aggregation

There is a 2 step structure that you will constantly use in data work:

1. Tranform 1st: Create an intermediate column
2. Then Aggregat: Group and summarize the aggregate column you just created

This work because Transformation transform each row — row by row transform while aggregation collapses every row to a group or one value. You will need the tranformed value before you can group them

WITH enriched_flight AS (
      SELECT flight_id,
             distance,
             distance_unit,
             CASE
                WHEN distance_unit = 'miles' THEN distance * 1.60934
                ELSE distance
             END AS distance_km,
             delay,
             CASE
                 WHEN delay < = -5 THEN 'Early'
                 WHEN delay < 5 THEN 'On Time'
                 ELSE 'Late'
             END AS delay_status
FROM flights
)

SELECT delay_status,
       AVG(distance_km) AS avg_distance
FROM enriched_flights
GROUP BY delay_status;

Source: DataCamp

Now when we study the result we see that the average distance are:
- Early = 5,887 KM and Late = 5,857 KM and On Time = 5800 KM. These values are very similar so there is no data that tell us delay_status is correlated with distance

But look at our code and see how sweet and simple our aggregation step is because all our complex code is done by the WITH clause

The principle of ‘Tranform First, Aggregate Later’ is very common in SQL

Now, we are exposed to a new concept called ‘Chaining CTE’. In this case, we will have to transformation that connected to each other but we can only do this on CTE.
But how can we reference a new created variable in the same CTE — chaining CTE

Multiple CTE are put in sequence, with each new one referencing the one before it

Here the structure:

• WITH appears only once at the beginning
• each CTE is seperated by a comma
• each CTE can reference any CTE defined above it
• The final SELECT queries from whichever CTE you need

WITH step_one AS (
     SELECT *, 
            <first_calculation> AS intermediate result
     FROM original_table
),   step_two AS (
     SELECT *,
           <calculation_using_intermediate_results> AS final results
     from step_one
)
SELECT *
FROM step_two;