Simplify queries, WITH keyword

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

As consultas podem ficar muito confusas ao adicionar 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 nós reutilizamos a query1 na query2 e na consulta principal (main).

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 média de pontuação para esses dispositivos grandes. Nomeie esta coluna como 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 você se importa
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 (____);

Teste seus conhecimentos

Esta lição inclui um quiz rápido. Comece a lição para respondê-lo e acompanhar seu progresso.

Todas as lições de Fundamentos

3Specific Return Format

Null values Sort Results Part 1 Sort Results Part 2 Recap - Cyber Security Firm Limit number of records Recap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary Election Recap - Police Criminal Arrest Recap - Bar Beverage Container Recap - Engineer new columns

9Multiple tables

Basic Join Part 1 Basic Join Part 2 Recap - Join Self join Recap - Self Join UnionSimplify queries, WITH keywordRecap - With Queries Recap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK Functions Recap - RANK & DENSE_RANK NTILE Function Aggregation Functions ROWS & RANGE Criterion

Pratique por conta própria: Playground de SQL