lesson 70 Performance & query plans · advanced

Review AI-generated SQL with evidence

Check generated SQL against real results and requirements.

the idea

Treat generated SQL like any other code review: check the grain, the join type, the denominator and NULL handling against a small known case before trusting the output. Typical defects are inner joins that drop zero rows, COUNT(*) after a LEFT JOIN, and integer division.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Spot the bad LEFT JOIN count. Run the assistant’s COUNT(*) version. Workspaces with zero projects can still show 1 because the unmatched join row counts as one row in the group.

   step-1.sql Run

   sql›

   SELECT a.account_id, COUNT(*) AS projects
   FROM accounts a
   LEFT JOIN projects p ON p.account_id = a.account_id
   GROUP BY a.account_id
   HAVING COUNT(p.project_id) = 0
   LIMIT 5;

2. 2

   List workspaces with no projects. HAVING COUNT(p.project_id) = 0 finds accounts that truly have no projects—the corrected metric is COUNT(p.project_id), not COUNT(*).

   step-2.sql Run

   sql›

   SELECT a.account_id, COUNT(p.project_id) AS projects
   FROM accounts a
   LEFT JOIN projects p ON p.account_id = a.account_id
   GROUP BY a.account_id
   HAVING COUNT(p.project_id) = 0
   LIMIT 5;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   -- Proposed by an assistant: count projects per workspace
   
   SELECT a.account_id, COUNT(*) AS projects
   FROM accounts a
   LEFT JOIN projects p ON p.account_id = a.account_id
   GROUP BY a.account_id;

common mistake

A query that runs and returns plausible numbers can still be wrong; compare it with a hand-checked example.