Semplificare le query, parola chiave WITH

Fa parte della sezione Fondamenti del percorso SQL di Coddy. Lezione 49 di 72.

Le query possono diventare troppo complicate aggiungendo molte query interne. Ad esempio, ecco una query con molte sottoquery:

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
	)
)

Per semplificare, possiamo usare la parola chiave WITH query_name AS (...). Ci permette di salvare una query con un nome e usarla dove vogliamo:

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)

Qui abbiamo riutilizzato query1 in query2 e nella query principale.

Sfida

Facile

Tabelle e colonne disponibili:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

scrivi una query che:

Per prima cosa identifica i "dispositivi grandi" (dispositivi con width maggiore di 200)
Poi calcola il punteggio medio per questi dispositivi grandi. Chiama questa colonna average_score

Usa la clausola WITH per risolvere questo problema.

Provalo tu

-- Passaggio 1: assegna un nome a un insieme di risultati temporaneo che contiene solo i dispositivi che ti interessano
WITH large_devices AS (
    SELECT ____
    FROM devices_specs
    WHERE ____
)
-- Passaggio 2: riutilizza quell'insieme di risultati temporaneo nella query principale
SELECT ____ AS average_score
FROM devices_score
WHERE device_id IN (____);