Simplifique consultas com a palavra-chave WITH

Parte da seção Fundamentos do Journey de SQL da Coddy. Lição 49 de 72.

As consultas podem ficar muito confusas com a adição de muitas consultas internas. Por exemplo, aqui está uma consulta que possui muitas 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 facilitar, podemos usar a palavra-chave WITH query_name AS (...). Ela nos permite salvar uma consulta com um nome e usá-la onde quisermos:

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)

Aqui, reutilizamos query1 em query2 e na consulta principal.

Desafio

Fácil

Tabelas e colunas disponíveis:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

escreva uma consulta que:

Primeiro identifique os "dispositivos grandes" (dispositivos com width maior que 200)
Em seguida, calcule a pontuação média desses dispositivos grandes. Dê a esta coluna o nome de average_score

Use a cláusula WITH para resolver este problema.

Experimente você mesmo

-- Passo 1: nomeie um conjunto de resultados temporário que mantém apenas os dispositivos que interessam a você
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Passo 2: reutilize esse conjunto de resultados temporário dentro da consulta principal
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);