Saltar al contenido principal

Actualizado 16 ene 2025  · 7 min de lectura

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;

Conclusión


Eugenia Anello's photo
Author
Eugenia Anello
LinkedIn
Temas

programa

SQL Fundamentals

26hrs hr
Master the SQL fundamentals needed for business, learn how to write SQL queries, and start analyzing your data using this powerful language.
Ver detallesRight Arrow
Comienza el curso
Ver másRight Arrow