Simplificar consultas, palabra clave WITH

Parte de la sección Fundamentos del Journey de SQL de Coddy. Lección 49 de 72.

Las consultas pueden volverse demasiado confusas al añadir muchas consultas internas. Por ejemplo, aquí hay una consulta que tiene muchas subconsultas:

SELECT * FROM table1
WHERE col2 IN (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN (
		SELECT col5 FROM table3
		WHERE col1 AND col3 OR col2
	)
)

Para facilitarlo, podemos usar la palabra clave WITH query_name AS (...). Esto nos permite guardar una consulta con un nombre y usarla donde queramos:

WITH query1 AS (
	SELECT col5 FROM table3
	WHERE col1 AND col3 OR col2
), query2 AS (
	SELECT col1 FROM table2
	WHERE col3 + col2 > 3 AND col5 LIKE '%test%' AND col6 IN query1
)
SELECT * FROM table1
WHERE col2 IN (SELECT col1 FROM query2) AND col4 IN (SELECT col5 FROM query1)

Aquí reutilizamos query1 en query2 y en la consulta principal.

Desafío

Fácil

Tablas y columnas disponibles:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

escribe una consulta que:

Primero identifique los "dispositivos grandes" (dispositivos con width mayor que 200)
Después calcule la puntuación media de estos dispositivos grandes. Denomina esta columna average_score

Usa la cláusula WITH para resolver este problema.

Pruébalo tú mismo

-- Paso 1: nombra un conjunto de resultados temporal que conserve solo los dispositivos que te importan
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Paso 2: reutiliza ese conjunto de resultados temporal dentro de la consulta principal
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);