Skip to content
Which column holds the number of features?What condition filters devices wider than 200?
Which column holds the score to sum?Which column connects the two tables?Which column represents height for filtering?
Which column identifies devices?Which column connects the tables?Which columns represent opinion and score?
Which column counts features?Which column provides the filtering condition?
Which column has the scores to maximise?Which column joins the tables?Which column represents width?
Which column identifies the devices?Which column filters for width > 200?Which column provides the scores for AVG?Which column is used for joining?
Which columns to select?Which column joins the tables?Which column provides filtering by number of features?Which column determines ordering?
Which aggregation function computes the average?Which column is being averaged?What does the outer query compare against?
Which column identifies devices?Which column joins the tables?Which column is averaged in the subquery?Which column filters width?
Identify which column is being aggregated.Decide which column the outer query needs from the subquery.Determine the correct join column to connect subquery results to the outer query.Recognise where the ratio computation goes and which value is numerator/denominator.Fill in the blanks:MAX function determines the highest score to compare.Group by column ensures aggregation per device.Join column carries the device_id to the outer query.Numerator is the device’s own score, denominator is the maximum from subquery.
Determine which score column to aggregate.Choose correct grouping column.Identify join column for outer query.Determine numerator and denominator for ratio computation.Confirm filtering condition for num_features > 5.
SQL Function Puzzles
Fill-in-the-Blank
First go through to upload the schema, then you can solve the puzzles by making real SQL queries.
1. Intro Puzzles
Use the tables devices_specs and devices_score. Each puzzle has a goal: write a query to produce it. Some parts are left blank for you to fill in to practice inference.
Tables
Table: devices_specs
device_id
width
height
num_features
opinion
1
210
120
5
good
2
180
100
3
ok
3
220
150
6
excellent
4
190
110
4
good
There are no rows in this table
Table: devices_score
device_id
score
1
88
2
75
3
95
4
80
There are no rows in this table
Puzzle 1
Goal: Find the average number of features for devices with width > 200.
Fill in the blanks:
Thinking scaffolding:
Puzzle 2
Goal: Find the total score of devices with height >= 120.
Fill in the blanks:
Thinking scaffolding:
Puzzle 3
Goal: Find the device_id(s) of devices where opinion = ‘good’ and score > 80.
Fill in the blanks:
Thinking scaffolding:
Puzzle 4
Goal: Count how many devices have more than 4 features.
Fill in the blanks:
Thinking scaffolding:
Puzzle 5
Goal: Find the maximum score among devices with width < 200.
Fill in the blanks:
Thinking scaffolding:
Puzzle 6
Goal: Find the average score of devices with width > 200.
Fill in the blanks:
Thinking scaffolding:
Puzzle 7
Goal: List device_id and score of devices with num_features > 5, sorted by score descending.
Fill in the blanks:
Thinking scaffolding:
2. Subqueries
Concept: A subquery is a query inside another query. It lets you compute intermediate results for the outer query.
Goal: Find the device_id of devices whose score is above the average score.
Fill in the blanks:
Thinking scaffolding:
Puzzle 2 Example
Goal: Find devices with score above average and width > 200.
Thinking scaffolding:
3. Nested Subqueries
Concept: You can put subqueries inside subqueries — each “box” prepares data for the next.
Goal: For each device, compute the maximum score of devices with num_features > 4, then compare it to the device’s own score.
Steps:
Additional thinking scaffolding:
Puzzle 2
Goal: Find each device’s score relative to the maximum score among devices with num_features > 5.
Thinking scaffolding:
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.