CASE
WHEN boolean_condition1 THEN result1
WHEN boolean_condition2 THEN result2
ELSE result3
END
SELECT
title,
gross,
budget,
gross / budget AS ROI,
CASE
WHEN gross / budget < 1 THEN 'low ROI'
WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
ELSE 'high ROI'
END as ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;
SELECT
CASE
WHEN gross / budget < 1 THEN 'low ROI'
WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
ELSE 'high ROI'
END AS ROI_group,
avg(gross) as avg_gross,
avg(budget) as avg_budget
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015
GROUP BY
CASE
WHEN gross / budget < 1 THEN 'low ROI'
WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
ELSE 'high ROI'
END
ORDER BY avg(gross) DESC
;
SELECT
title,
gross,
budget,
language,
gross / budget AS ROI
FROM cinema.films
WHERE
CASE
WHEN language = 'English' THEN gross / budget > 2 -- English films need ROI > 2
WHEN language = 'French' THEN gross / budget > 1.5 -- French films need ROI > 1.5
ELSE gross / budget > 1.3 -- Default for other languages
END;
SELECT
SUM(CASE WHEN gross / budget > 2 THEN 1 ELSE 0 END) AS high_roi_films,
SUM(CASE WHEN gross / budget BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS medium_roi_films,
SUM(CASE WHEN gross / budget < 1 THEN 1 ELSE 0 END) AS low_roi_films
FROM cinema.films
WHERE budget > 0 AND gross > 0 AND release_year = 2015;
SELECT
title,
language,
gross / budget AS ROI,
CASE
WHEN language = 'English' THEN
CASE
WHEN gross / budget < 2 THEN 'low ROI'
WHEN gross / budget BETWEEN 2 AND 3 THEN 'medium ROI'
ELSE 'high ROI'
END
ELSE
CASE
WHEN gross / budget < 1 THEN 'low ROI'
WHEN gross / budget BETWEEN 1 AND 2 THEN 'medium ROI'
ELSE 'high ROI'
END
END AS ROI_group
FROM cinema.films
WHERE budget > 0 AND gross > 0 and release_year = 2006;