Simplify queries, WITH keyword

Lesson 2 of 13 in Coddy's SQL for advanced course.

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

A device's quality is measured by (width/height)*num_features.

We want to find all of the underrated devices. Fetch all of the devices where the device's opinion is greater than the average quality and the device's score is less than the average quality.

To solve it, use the WITH clause to create a subquery that calculates the average quality and reuse it in the main query.

Try it yourself

All lessons in SQL for advanced

1Introdcution

IntroductionSimplify queries, WITH keyword

4Summary

Final challenge #1

2Window Functions part 1

ROW_NUMBER function ORDER BY criterion PARTITION BY criterion LEAD & LAG functions Recap challenge #1 Recap challenge #2

3Window Functions part 2

RANK & DENSE_RANK functions NTILE function Aggregation functions ROWS & RANGE criterion