lesson 70 Performance & query plans · advanced

Review AI-generated SQL with evidence

Check generated SQL against real results and requirements.

the idea

Use assistance to explain or propose alternatives, then verify against the schema, metric definition, edge cases, and real execution plan. A confident answer can still double-count rows or omit zero-activity users. This lesson is a deterministic review exercise; no live AI service is connected.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

-- Review claim: COUNT(*) after LEFT JOIN counts each account’s requests.
 -- Counterexample: accounts with no requests get one preserved row.

SELECT a.id,COUNT(r.id) AS request_count
FROM ai_accounts a
LEFT JOIN ai_requests r ON r.account_id=a.id
GROUP BY a.id;

Watch for: Inspect plans before claiming an optimization; this app does not invoke ChatGPT or another model.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Read the accounts before trusting a query. Check who is in the table, including accounts that may have no requests. A plan does not prove the metric.

   step-1.sql Run

   sql›

   SELECT id, plan, region
   FROM ai_accounts;

2. 2

   Run the reference. Use assistance to explain or propose alternatives, then verify against the schema, metric definition, edge cases, and real execution plan. A confident answer can still double-count rows or omit zero-activity users. This lesson is a deterministic review exercise; no live AI service is connected.

   step-2.sql Run

   sql›

   -- Review claim: COUNT(*) after LEFT JOIN counts each account’s requests.
    -- Counterexample: accounts with no requests get one preserved row.
   
   SELECT a.id,COUNT(r.id) AS request_count
   FROM ai_accounts a
   LEFT JOIN ai_requests r ON r.account_id=a.id
   GROUP BY a.id;