Simplify queries, WITH keyword

Part of the Fundamentals section of Coddy's SQL journey — lesson 49 of 72.

Queries can get too messy by adding many inner queries. For example here is a query that has many sub-queries:

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

To make it easier we can use the WITH query_name AS (...) keyword. It allows us to save a query with a name and use it wherever we want:

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 query2 AND col4 IN query1

Here we reused query1 in query2 and in the main query.

Challenge

Easy

Available tables and columns:
devices_specs: device_id, width, height, num_features, opinion
devices_score: device_id, score

write a query that:

First identifies "large devices" (devices with width greater than 200)
Then calculates the average score for these large devices. Call this column as average_score

Use the WITH clause to solve this problem.

Cheat sheet

Use WITH query_name AS (...) to create named subqueries that can be reused:

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 query2 AND col4 IN query1

This approach makes complex queries with multiple subqueries more readable and allows reusing the same subquery multiple times.

Try it yourself