Skip to content
Basic Aggregation: AVG, SUM, COUNT, MAX combined with filtering (WHERE).Joins: JOIN ... USING syntax to connect devices_specs and devices_score.Subqueries: Using scalar subqueries in WHERE clauses (e.g., score > (SELECT AVG...)).CTEs: Common Table Expressions to structure complex queries.Window Functions: Using MAX(score) OVER () to calculate group statistics while preserving individual rows, which was required for the advanced nested puzzles.SQLite Specifics: Handling DROP TABLE IF EXISTS, schema creation, and data insertion.Attempt 1 Failure:Issue: In the "Nested Subqueries" puzzles, I initially failed to account for SQLite's integer division.Context: The query score / max_val involved two integers (e.g., 88 / 95), which SQLite truncates to 0. This caused the filter > 0.9 to exclude valid results (like 0.92).Correction: I applied a fix by casting the denominator to a float: score / (max_val * 1.0).Result: The second attempt produced the correct floating-point ratios and identified all matching devices.
SQL Puzzles Competence Report
I instructed my Coda MCP-aware agent to complete published in and explain them to me as a competency test. I know this goes against your intended hope that we will learn and elevate our personal knowledge, but I'm deep into an AI learning pathway, and your efforts serve me as well. For safety factors, I made a personal copy of your document and performed all tests and evals there.
Here's the experience my agent had with the nine puzzles.
Executive Summary (by the agent)
I have successfully completed all 9 SQL puzzles provided in the Coda document. The solutions were verified against a local SQLite environment replicating the specified schema and data.
Competence Assessment
I demonstrated competence in the following SQL concepts:
Failures and Corrections
Solutions
Section 1: SQL Intro Puzzles
Puzzle 1 (Avg features, width > 200)
SELECT AVG(num_features) AS average_features
FROM devices_specs
WHERE width > 200;
Puzzle 2 (Total score, height >= 120)
SELECT SUM(score) AS total_score
FROM devices_score AS s
JOIN devices_specs AS d USING (device_id)
WHERE height >= 120;
Puzzle 3 (Opinion 'good', score > 80)
SELECT device_id
FROM devices_score AS s
JOIN devices_specs AS d USING (device_id)
WHERE opinion = 'good' AND score > 80;
Puzzle 4 (Count features > 4)
SELECT COUNT(*) AS num_devices
FROM devices_specs
WHERE num_features > 4;
Puzzle 5 (Max score, width < 200)
SELECT MAX(score) AS max_score
FROM devices_score AS s
JOIN devices_specs AS d USING (device_id)
WHERE width < 200;
Puzzle 6 (CTE Avg score, width > 200)
WITH large_devices AS (
SELECT device_id
FROM devices_specs
WHERE width > 200
)
SELECT AVG(score) AS average_score
FROM devices_score AS s
JOIN large_devices AS l USING (device_id);
Puzzle 7 (List sorted by score)
SELECT device_id, score
FROM devices_score AS s
JOIN devices_specs AS d USING (device_id)
WHERE num_features > 5
ORDER BY score DESC;
Section 2: Subqueries
Puzzle 1 (Score > Avg)
SELECT device_id
FROM devices_score
WHERE score > (
SELECT AVG(score)
FROM devices_score
);
Puzzle 2 (Score > Avg AND Width > 200)
SELECT device_id
FROM devices_score AS s
JOIN devices_specs AS d USING (device_id)
WHERE score > (
SELECT AVG(score)
FROM devices_score
) AND width > 200;
Section 3: Nested Subqueries
Puzzle 1 (Ratio to max, features > 4)
SELECT device_id, score / (max_val * 1.0) AS ratio_to_max
FROM (
SELECT device_id, MAX(score) OVER () AS max_val
FROM devices_score AS s
JOIN devices_specs AS d USING (device_id)
WHERE num_features > 4
) AS max_scores
JOIN devices_score AS s2 USING (device_id)
WHERE score / (max_val * 1.0) > 0.9
ORDER BY ratio_to_max DESC;
Puzzle 2 (Ratio to max, features > 5)
SELECT device_id, score / (max_val * 1.0) AS ratio_to_max
FROM (
SELECT device_id, MAX(score) OVER () AS max_val
FROM devices_score AS s
JOIN devices_specs AS d USING (device_id)
WHERE num_features > 5
) AS max_scores
JOIN devices_score AS s2 USING (device_id)
WHERE score / (max_val * 1.0) > 0.85
ORDER BY ratio_to_max DESC;
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.