Conditional analysis

Intermediate SQL with AI

Jasmin Ludolf

Senior Data Science Content Developer

Conditional aggregates

Prompt: Show the number of films with a budget and without a budget, for each year

SELECT 
    release_year,
    COUNT(CASE WHEN budget IS NOT NULL THEN 1 END) AS films_with_budget,
    COUNT(CASE WHEN budget IS NULL THEN 1 END) AS films_without_budget
FROM films
GROUP BY release_year;

|release_year|films_with_budget|films_without_budget|
|------------|-----------------|--------------------|
|1964        |10               |0                   |
|1969        |9                |1                   |
...

Conditional aggregates

Conditional diagram

Conditional aggregates

Prompt: Show the number of films with a budget and without a budget, for each year

SELECT 
    release_year,
    COUNT(CASE WHEN budget IS NOT NULL THEN 1 END) AS films_with_budget,
    COUNT(CASE WHEN budget IS NULL THEN 1 END) AS films_without_budget
FROM films
GROUP BY release_year;

|release_year|films_with_budget|films_without_budget|
|------------|-----------------|--------------------|
|1964        |10               |0                   |
|1969        |9                |1                   |
...

If a film meets the condition, it counts a 1

Conditional aggregates

Prompt: Show the number of films with a budget and without a budget, for each year

SELECT 
    release_year,
    SUM(CASE WHEN budget IS NOT NULL THEN 1 ELSE 0 END) AS films_with_budget,
    SUM(CASE WHEN budget IS NULL THEN 1 ELSE 0 END) AS films_without_budget
FROM  films
GROUP BY release_year;

|release_year|films_with_budget|films_without_budget|
|------------|-----------------|--------------------|
|1964        |10               |0                   |
|1969        |9                |1                   |
...

Conditional aggregates with NULLs

Prompt: Show the number of films with a budget and without a budget, for each year

SELECT 
    release_year,
    COUNT(CASE WHEN budget IS NOT NULL THEN 1 END) AS films_with_budget,
    COUNT(CASE WHEN budget IS NULL THEN 1 END) AS films_without_budget
FROM films
GROUP BY release_year;

NULLs are in films_without_budget
May need to filter with WHERE

Combining techniques

Prompt: For each year after 2000 with at least 5 films that have budget data, categorize films as High Budget if the budget is over 1 million and Low Budget if it is 1 million or less, and show the counts for each category, ordered by year and showing the top 10 results

SELECT 
    release_year,

    COUNT(CASE WHEN budget > 1000000 THEN 1 END) AS high_budget_count,
    COUNT(CASE WHEN budget <= 1000000 THEN 1 END) AS low_budget_count

FROM films

WHERE release_year > 2000 AND budget IS NOT NULL

GROUP BY release_year

HAVING COUNT(*) >= 5

ORDER BY release_year
LIMIT 10;

Beyond counting

Conditional patterns work with other aggregates too

Experiment with conditions and thresholds

Reveal hidden insights

Discover insights

Let's practice!

Intermediate SQL with AI