Creating categories with conditional logic

Intermediate SQL with AI

Jasmin Ludolf

Senior Data Science Content Developer

The CASE for conditional logic

|duration|
|--------|
|123     |
|110     |
|151     |
...

Categorize as "Short", "Standard", "Epic"
duration > 90: yes or no
Use conditional logic
Easier to read and communicate

Bucket

Poor prompting for CASE statements

Prompt: Categorize films as Short if the duration is under 90 minutes, Standard if under 150, and Epic otherwise

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 90 THEN 'Short'
        WHEN duration < 150 THEN 'Standard'
        ELSE 'Epic'
    END AS film_category
FROM films;

Poor prompting for CASE statements

Prompt: Categorize films as Short if the duration is under 90 minutes, Standard if under 150, Epic otherwise

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 150 THEN 'Standard'
        WHEN duration < 90 THEN 'Short'
        ELSE 'Epic'
    END AS film_category
FROM films;

Risk for errors
Incorrect category if a film meets both criteria
- e.g. both under 90 and under 150

Better prompting for CASE statements

Prompt: Categorize films as Short if under 90 minutes, Standard if between 90 and 149 minutes, Epic if 150 minutes or more, and unknown otherwise

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 90 THEN 'Short'
        WHEN duration BETWEEN 90 AND 149 THEN 'Standard'
        WHEN duration >= 150 THEN 'Epic'
        ELSE 'Unknown'
    END AS category
FROM films;

Verify the logic

|id|title                                           |duration|category|
|--|------------------------------------------------|--------|--------|
|1 |Intolerance: Love's Struggle Throughout the Ages|123     |Standard|
|2 |Over the Hill to the Poorhouse                  |110     |Standard|
|3 |The Big Parade                                  |151     |Epic    |
|4 |Metropolis                                      |145     |Standard|
...

|id  |title  |duration|category|
|----|-------|--------|--------|
|4396|Destiny|        |Unknown |
...

Filtering NULLs

Prompt: Remove any NULL durations

SELECT 
    id, 
    title, 
    duration,
    CASE 
        WHEN duration < 90 THEN 'Short'
        WHEN duration BETWEEN 90 AND 149 THEN 'Standard'
        WHEN duration >= 150 THEN 'Epic'
        ELSE 'Unknown'
    END AS category
FROM films

WHERE duration IS NOT NULL;

The benefits of categories

Clarify our results with meaningful labels

Improve communication

Enable cleaner visualizations

Star

Let's practice!

Intermediate SQL with AI