Repair the complete deliberately wrong query and explanation below using
only this complete fictional JOIN-01 packet. Return the entire corrected
SQLite SELECT, the complete seven-row diagnostic for the baseline and
the full three-row per-team result, followed by an explanation tracing
each repaired count to project IDs and check IDs. Do not return just a
suggestion to add DISTINCT or a single corrected result row.

Question: for each team, count projects, individual check events and passed
check events. Include projects with no checks. Use the original team,
project_id, check_id and outcome fields and the supplied keys unchanged.
One project row is one entity; one check_id is one independent event.
C1 and C2 are separate pass events. P4 belongs to West even with no events.
Output one row per team ordered North, South, West for this fixture.

Preserve the original data. Do not count a null right-side placeholder as
an event. Do not discard repeated pass labels, filter the whole result to
pass-only rows or change the question to list membership reconciliation.
Explain where each query stage has one row per event versus per project.

The bad query below was actually run locally on fictional inputs; the bad
prose is an authored counterexample, not captured model output. Do not
claim a model has been run or that your returned query has been executed.
Prepare text only. Any later execution must use a disposable local toy
copy and report the actual engine/results separately. Do not connect to a
production database, upload data, install packages or access an account.

COMPLETE SCHEMA AND ORIGINAL SEED
PRAGMA foreign_keys = ON;

CREATE TABLE projects (
    project_id TEXT PRIMARY KEY NOT NULL,
    team TEXT NOT NULL
);

CREATE TABLE checks (
    check_id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL REFERENCES projects(project_id),
    outcome TEXT NOT NULL
);

INSERT INTO projects (project_id, team) VALUES
    ('P1', 'North'),
    ('P2', 'North'),
    ('P3', 'South'),
    ('P4', 'West');

INSERT INTO checks (check_id, project_id, outcome) VALUES
    ('C1', 'P1', 'pass'),
    ('C2', 'P1', 'pass'),
    ('C3', 'P1', 'fail'),
    ('C4', 'P2', 'pass'),
    ('C5', 'P3', 'fail'),
    ('C6', 'P3', 'pass');

COMPLETE WRONG QUERY
SELECT
    p.team,
    COUNT(*) AS projects,
    COUNT(*) AS checks,
    SUM(CASE WHEN c.outcome = 'pass' THEN 1 ELSE 0 END) AS passed
FROM projects AS p
LEFT JOIN checks AS c ON c.project_id = p.project_id
GROUP BY p.team
ORDER BY p.team;

COMPLETE OBSERVED WRONG BASELINE RESULT
[
  [
    "North",
    4,
    4,
    3
  ],
  [
    "South",
    2,
    2,
    1
  ],
  [
    "West",
    1,
    1,
    0
  ]
]
Columns: team, projects, checks, passed

COMPLETE AUTHORED WRONG EXPLANATION
DELIBERATELY WRONG — authored teaching counterexample

I joined projects to checks and counted the joined rows for both measures.
North has 4 projects, 4 checks and 3 passed checks. South has 2 projects,
2 checks and 1 passed check. West has 1 project, 1 check and 0 passed
checks. The full result therefore contains 7 projects and 7 checks,
with 4 passed checks.