Recap - With Queries

Part of the Fundamentals section of Coddy's SQL journey. Lesson 50 of 72.

Challenge

Easy

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

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

We want to find all of the overrated 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. Return only the device_id.

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

-- Step 1: name a temporary result set that holds one single average value
WITH avg_quality AS (
    SELECT ____ AS avg_q
    FROM devices_specs
)
-- Step 2: bring the two device tables together, then attach the temporary result set
SELECT dsp.device_id
FROM devices_specs dsp
INNER JOIN devices_score dsc ON ____
INNER JOIN avg_quality aq
-- Step 3: keep the devices that sit on opposite sides of that average
WHERE ____

All lessons in Fundamentals

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 Union Simplify queries, WITH keywordRecap - With QueriesRecap - Real Estate Contractor

12Window Functions part 2

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

Practice on your own: SQL playground