← Findings and charts · SQL library and setup

SQL with and without definitions

T93: use the five questions, reference SQL, and checked answers below as the ground-truth example. T100: add the A/B prompts, generated SQL, results, and error analysis. The recorded scores are example results, not a required model-accuracy target.

A receives the question and schema. B also receives the SQL library. Neither receives the reference answer.

  1. Verify the reference SQL and answer.
  2. Send separate GPT API calls with the same question, schema, and output format.
  3. Add the definition library only to B. Save the first responses without repairs.
  4. Execute both, compare the returned rows, and review differences in the SQL.

Reference matches: A 3/5 · B 5/5

Separate GPT-5.2 API calls, low reasoning effort. Local reference notebook; billing-disabled BigQuery. SQL results are compared exactly after the requested three-decimal rounding, including columns, duplicates, NULLs, and requested order. Question 5 accepts equivalent month representations; strict formatting scored B 4/5, while answer correctness scored B 5/5.

1. Gear shares

In January through March 2023, north of or on the equator, show apparent fishing hours and percentage of the selected total by gear for the supplied document IDs. Round both numbers to three decimals. Sort hours descending then gear. Use only positive fishing hours.

Schema supplied to both requests
cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels:
  mmsi STRING
  year INTEGER
  flag_ais STRING
  flag_registry STRING
  flag_gfw STRING
  vessel_class_inferred STRING
  vessel_class_inferred_score FLOAT
  vessel_class_registry STRING
  vessel_class_gfw STRING
  self_reported_fishing_vessel BOOLEAN
  length_m_inferred FLOAT
  length_m_registry FLOAT
  length_m_gfw FLOAT
  engine_power_kw_inferred FLOAT
  engine_power_kw_registry FLOAT
  engine_power_kw_gfw FLOAT
  tonnage_gt_inferred FLOAT
  tonnage_gt_registry FLOAT
  tonnage_gt_gfw FLOAT
  registries_listed STRING
  active_hours FLOAT
  fishing_hours FLOAT

cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort:
  date DATE
  cell_ll_lat FLOAT
  cell_ll_lon FLOAT
  mmsi STRING
  hours FLOAT
  fishing_hours FLOAT

cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort:
  date DATE
  year INTEGER
  month INTEGER
  cell_ll_lat FLOAT
  cell_ll_lon FLOAT
  flag STRING
  geartype STRING
  hours FLOAT
  fishing_hours FLOAT
  mmsi_present INTEGER

cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map:
  document_id STRING
  title STRING
  url STRING
  geartype STRING
  family STRING
  evidence STRING
Additional definitions supplied to B
{
  "domain_rules": "",
  "definitions": {
    "gear_share": {
      "description": "Apparent fishing hours and percent of selected hours by gear; only document-mapped gears.",
      "parameters": {
        "start": "DATE",
        "end": "DATE",
        "min_lat": "FLOAT64",
        "max_lat": "FLOAT64"
      },
      "output_columns": [
        "geartype",
        "fishing_hours",
        "share_pct"
      ],
      "domain_rules": "Use DISTINCT mapped geartypes from candidate document IDs to avoid multiplying activity. Half-open dates, inclusive lower and exclusive upper latitude. Denominator is only the selected mapped activity. Exclude nonpositive fishing hours.",
      "sql": "WITH mapped AS (SELECT DISTINCT geartype FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE document_id IN UNNEST(@candidate_ids)), totals AS (\nSELECT m.geartype,SUM(m.fishing_hours) h FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` m JOIN mapped g USING(geartype)\nWHERE date>=@start AND date<@end AND cell_ll_lat>=@min_lat AND cell_ll_lat<@max_lat AND fishing_hours>0 GROUP BY 1)\nSELECT geartype,ROUND(h,3) fishing_hours,ROUND(100*SAFE_DIVIDE(h,SUM(h) OVER()),3) share_pct FROM totals ORDER BY fishing_hours DESC,geartype"
    },
    "monthly_method": {
      "description": "Monthly apparent fishing hours by verified hook/net family.",
      "parameters": {
        "start": "DATE",
        "end": "DATE"
      },
      "output_columns": [
        "month",
        "family",
        "fishing_hours"
      ],
      "domain_rules": "Use the reviewed gear taxonomy in document_map. DISTINCT geartype,family avoids repeated evidence. Coarse fishing/fixed_gear labels remain unclassified, not assigned to hooks or nets.",
      "sql": "WITH mapped AS (SELECT DISTINCT geartype,family FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE family IN ('hooks','nets'))\nSELECT EXTRACT(MONTH FROM m.date) month,g.family,ROUND(SUM(m.fishing_hours),3) fishing_hours FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` m JOIN mapped g USING(geartype)\nWHERE date>=@start AND date<@end AND fishing_hours>0 GROUP BY 1,2 ORDER BY 1,2"
    },
    "hotspots": {
      "description": "Top one-degree bins of apparent fishing activity for retrieved document categories.",
      "parameters": {
        "start": "DATE",
        "end": "DATE",
        "min_lat": "FLOAT64",
        "max_lat": "FLOAT64",
        "n": "INT64"
      },
      "output_columns": [
        "latitude",
        "longitude",
        "fishing_hours"
      ],
      "domain_rules": "FLOOR latitude/longitude including negative coordinates. Bin coordinates are lower-left corners, not centroids. Rank hours descending then coordinates. Candidate mapping must be deduplicated.",
      "sql": "WITH mapped AS (SELECT DISTINCT geartype FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE document_id IN UNNEST(@candidate_ids))\nSELECT CAST(FLOOR(cell_ll_lat) AS INT64) latitude,CAST(FLOOR(cell_ll_lon) AS INT64) longitude,ROUND(SUM(fishing_hours),3) fishing_hours\nFROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` JOIN mapped USING(geartype)\nWHERE date>=@start AND date<@end AND cell_ll_lat>=@min_lat AND cell_ll_lat<@max_lat AND fishing_hours>0 GROUP BY 1,2 ORDER BY fishing_hours DESC,latitude,longitude LIMIT @n"
    },
    "fleet_activity": {
      "description": "Q1 observed active fishing vessels and hours by gear, retaining unmatched metadata.",
      "parameters": {
        "start": "DATE",
        "end": "DATE"
      },
      "output_columns": [
        "geartype",
        "vessels",
        "fishing_hours",
        "hours_per_vessel"
      ],
      "domain_rules": "Join on MMSI AND year. Count distinct vessel identifiers across grid cells and dates. Include unmatched daily records under unknown. One vessel-year metadata row per key is a checked prerequisite. Only positive fishing hours. Vessel IDs are AIS identifiers, not guaranteed physical-vessel identities.",
      "sql": "SELECT COALESCE(v.vessel_class_gfw,'unknown') geartype,COUNT(DISTINCT d.mmsi) vessels,ROUND(SUM(d.fishing_hours),3) fishing_hours,\nROUND(SAFE_DIVIDE(SUM(d.fishing_hours),COUNT(DISTINCT d.mmsi)),3) hours_per_vessel FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` d LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` v ON d.mmsi=v.mmsi AND EXTRACT(YEAR FROM d.date)=v.year\nWHERE d.date>=@start AND d.date<@end AND d.fishing_hours>0 GROUP BY 1 ORDER BY fishing_hours DESC,geartype"
    },
    "metadata_audit": {
      "description": "Count metadata availability and associated activity, keeping unmatched rows.",
      "parameters": {
        "start": "DATE",
        "end": "DATE"
      },
      "output_columns": [
        "metadata_status",
        "vessels",
        "fishing_hours"
      ],
      "domain_rules": "Priority: unmatched MMSI-year -> no_metadata; matched with NULL registry gear -> no_registry_gear; otherwise registry_gear_available. Count DISTINCT MMSI, never sum grid-cell vessel counts. Preserve left join.",
      "sql": "SELECT CASE WHEN v.mmsi IS NULL THEN 'no_metadata' WHEN v.vessel_class_registry IS NULL THEN 'no_registry_gear' ELSE 'registry_gear_available' END metadata_status,\nCOUNT(DISTINCT d.mmsi) vessels,ROUND(SUM(d.fishing_hours),3) fishing_hours FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` d LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` v ON d.mmsi=v.mmsi AND EXTRACT(YEAR FROM d.date)=v.year\nWHERE d.date>=@start AND d.date<@end AND d.fishing_hours>0 GROUP BY 1 ORDER BY 1"
    }
  }
}

Verified reference

Checked before either GPT request.

Reference SQL

WITH mapped AS (
  SELECT DISTINCT
    geartype
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE
    document_id IN UNNEST(@candidate_ids)
), totals AS (
  SELECT
    m.geartype,
    SUM(m.fishing_hours) AS h
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` AS m
  JOIN mapped AS g
    USING (geartype)
  WHERE
    date >= @start
    AND date < @end
    AND cell_ll_lat >= @min_lat
    AND cell_ll_lat < @max_lat
    AND fishing_hours > 0
  GROUP BY
    1
)
SELECT
  geartype,
  ROUND(h, 3) AS fishing_hours,
  ROUND(100 * SAFE_DIVIDE(h, SUM(h) OVER ()), 3) AS share_pct
FROM totals
ORDER BY
  fishing_hours DESC,
  geartype
Bound parameter values
{
  "start": "2023-01-01",
  "end": "2023-04-01",
  "min_lat": 0.0,
  "max_lat": 90.0,
  "candidate_ids": [
    "1003338",
    "1513400",
    "335840",
    "338407",
    "764476"
  ]
}

Correct answer

geartypefishing_hoursshare_pct
trawlers13,427,973.90677.958
drifting_longlines1,434,673.1988.329
set_gillnets1,103,141.3266.404
set_longlines843,942.1404.900
other_purse_seines176,151.5101.023
squid_jigger117,771.8840.684
tuna_purse_seines44,103.1220.256
seiners31,786.8470.185
other_seines25,650.2630.149
purse_seines19,535.7350.113

A · Schema only

Matches the reference

WITH selected_gears AS (
  SELECT DISTINCT geartype
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE document_id IN UNNEST(@candidate_ids)
),
filtered_effort AS (
  SELECT
    me.geartype,
    me.fishing_hours
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` me
  JOIN selected_gears sg
    USING (geartype)
  WHERE me.year = 2023
    AND me.month IN (1, 2, 3)
    AND me.cell_ll_lat >= 0
    AND me.fishing_hours > 0
),
by_gear AS (
  SELECT
    geartype,
    SUM(fishing_hours) AS fishing_hours
  FROM filtered_effort
  GROUP BY geartype
),
tot AS (
  SELECT SUM(fishing_hours) AS total_fishing_hours
  FROM by_gear
)
SELECT
  bg.geartype AS geartype,
  ROUND(bg.fishing_hours, 3) AS fishing_hours,
  ROUND(100 * SAFE_DIVIDE(bg.fishing_hours, t.total_fishing_hours), 3) AS share_pct
FROM by_gear bg
CROSS JOIN tot t
ORDER BY fishing_hours DESC, geartype;
Returned answer
geartypefishing_hoursshare_pct
trawlers13,427,973.90677.958
drifting_longlines1,434,673.1988.329
set_gillnets1,103,141.3266.404
set_longlines843,942.1404.900
other_purse_seines176,151.5101.023
squid_jigger117,771.8840.684
tuna_purse_seines44,103.1220.256
seiners31,786.8470.185
other_seines25,650.2630.149
purse_seines19,535.7350.113
Exact API prompt
SYSTEM
Write one BigQuery SELECT query for the question using only the supplied tables. Return only SQL with the requested output-column names. Use @candidate_ids when filtering by supplied entity IDs; the notebook binds them. Use concrete values from the question for other filters. When governed context is provided, follow its definitions and adapt or combine its SQL examples as needed.

USER
{"question": "In January through March 2023, north of or on the equator, show apparent fishing hours and percentage of the selected total by gear for the supplied document IDs. Round both numbers to three decimals. Sort hours descending then gear. Use only positive fishing hours.", "schema": "cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels: mmsi STRING, year INTEGER, flag_ais STRING, flag_registry STRING, flag_gfw STRING, vessel_class_inferred STRING, vessel_class_inferred_score FLOAT, vessel_class_registry STRING, vessel_class_gfw STRING, self_reported_fishing_vessel BOOLEAN, length_m_inferred FLOAT, length_m_registry FLOAT, length_m_gfw FLOAT, engine_power_kw_inferred FLOAT, engine_power_kw_registry FLOAT, engine_power_kw_gfw FLOAT, tonnage_gt_inferred FLOAT, tonnage_gt_registry FLOAT, tonnage_gt_gfw FLOAT, registries_listed STRING, active_hours FLOAT, fishing_hours FLOAT\ncs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort: date DATE, cell_ll_lat FLOAT, cell_ll_lon FLOAT, mmsi STRING, hours FLOAT, fishing_hours FLOAT\ncs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort: date DATE, year INTEGER, month INTEGER, cell_ll_lat FLOAT, cell_ll_lon FLOAT, flag STRING, geartype STRING, hours FLOAT, fishing_hours FLOAT, mmsi_present INTEGER\ncs145-p2-sandbox-20261011.cs145_fishing_reference.document_map: document_id STRING, title STRING, url STRING, geartype STRING, family STRING, evidence STRING", "candidate_ids": ["1003338", "1513400", "335840", "338407", "764476"], "output_columns": ["geartype", "fishing_hours", "share_pct"]}

gpt-5.2-2025-12-11 · SQL execution: 1.48s

B · Schema + definitions

Matches the reference

WITH mapped AS (
  SELECT DISTINCT geartype
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map`
  WHERE document_id IN UNNEST(@candidate_ids)
),
totals AS (
  SELECT
    m.geartype,
    SUM(m.fishing_hours) AS h
  FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` m
  JOIN mapped g USING (geartype)
  WHERE m.date >= DATE '2023-01-01'
    AND m.date < DATE '2023-04-01'
    AND m.cell_ll_lat >= 0
    AND m.cell_ll_lat < 90
    AND m.fishing_hours > 0
  GROUP BY 1
)
SELECT
  geartype,
  ROUND(h, 3) AS fishing_hours,
  ROUND(100 * SAFE_DIVIDE(h, SUM(h) OVER ()), 3) AS share_pct
FROM totals
ORDER BY fishing_hours DESC, geartype;
Returned answer
geartypefishing_hoursshare_pct
trawlers13,427,973.90677.958
drifting_longlines1,434,673.1988.329
set_gillnets1,103,141.3266.404
set_longlines843,942.1404.900
other_purse_seines176,151.5101.023
squid_jigger117,771.8840.684
tuna_purse_seines44,103.1220.256
seiners31,786.8470.185
other_seines25,650.2630.149
purse_seines19,535.7350.113
Exact API prompt
SYSTEM
Write one BigQuery SELECT query for the question using only the supplied tables. Return only SQL with the requested output-column names. Use @candidate_ids when filtering by supplied entity IDs; the notebook binds them. Use concrete values from the question for other filters. When governed context is provided, follow its definitions and adapt or combine its SQL examples as needed.

USER
{"question": "In January through March 2023, north of or on the equator, show apparent fishing hours and percentage of the selected total by gear for the supplied document IDs. Round both numbers to three decimals. Sort hours descending then gear. Use only positive fishing hours.", "schema": "cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels: mmsi STRING, year INTEGER, flag_ais STRING, flag_registry STRING, flag_gfw STRING, vessel_class_inferred STRING, vessel_class_inferred_score FLOAT, vessel_class_registry STRING, vessel_class_gfw STRING, self_reported_fishing_vessel BOOLEAN, length_m_inferred FLOAT, length_m_registry FLOAT, length_m_gfw FLOAT, engine_power_kw_inferred FLOAT, engine_power_kw_registry FLOAT, engine_power_kw_gfw FLOAT, tonnage_gt_inferred FLOAT, tonnage_gt_registry FLOAT, tonnage_gt_gfw FLOAT, registries_listed STRING, active_hours FLOAT, fishing_hours FLOAT\ncs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort: date DATE, cell_ll_lat FLOAT, cell_ll_lon FLOAT, mmsi STRING, hours FLOAT, fishing_hours FLOAT\ncs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort: date DATE, year INTEGER, month INTEGER, cell_ll_lat FLOAT, cell_ll_lon FLOAT, flag STRING, geartype STRING, hours FLOAT, fishing_hours FLOAT, mmsi_present INTEGER\ncs145-p2-sandbox-20261011.cs145_fishing_reference.document_map: document_id STRING, title STRING, url STRING, geartype STRING, family STRING, evidence STRING", "candidate_ids": ["1003338", "1513400", "335840", "338407", "764476"], "output_columns": ["geartype", "fishing_hours", "share_pct"], "governed_context": {"domain_rules": "", "definitions": {"gear_share": {"description": "Apparent fishing hours and percent of selected hours by gear; only document-mapped gears.", "parameters": {"start": "DATE", "end": "DATE", "min_lat": "FLOAT64", "max_lat": "FLOAT64"}, "output_columns": ["geartype", "fishing_hours", "share_pct"], "domain_rules": "Use DISTINCT mapped geartypes from candidate document IDs to avoid multiplying activity. Half-open dates, inclusive lower and exclusive upper latitude. Denominator is only the selected mapped activity. Exclude nonpositive fishing hours.", "sql": "WITH mapped AS (SELECT DISTINCT geartype FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE document_id IN UNNEST(@candidate_ids)), totals AS (\nSELECT m.geartype,SUM(m.fishing_hours) h FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` m JOIN mapped g USING(geartype)\nWHERE date>=@start AND date<@end AND cell_ll_lat>=@min_lat AND cell_ll_lat<@max_lat AND fishing_hours>0 GROUP BY 1)\nSELECT geartype,ROUND(h,3) fishing_hours,ROUND(100*SAFE_DIVIDE(h,SUM(h) OVER()),3) share_pct FROM totals ORDER BY fishing_hours DESC,geartype"}, "monthly_method": {"description": "Monthly apparent fishing hours by verified hook/net family.", "parameters": {"start": "DATE", "end": "DATE"}, "output_columns": ["month", "family", "fishing_hours"], "domain_rules": "Use the reviewed gear taxonomy in document_map. DISTINCT geartype,family avoids repeated evidence. Coarse fishing/fixed_gear labels remain unclassified, not assigned to hooks or nets.", "sql": "WITH mapped AS (SELECT DISTINCT geartype,family FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE family IN ('hooks','nets'))\nSELECT EXTRACT(MONTH FROM m.date) month,g.family,ROUND(SUM(m.fishing_hours),3) fishing_hours FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` m JOIN mapped g USING(geartype)\nWHERE date>=@start AND date<@end AND fishing_hours>0 GROUP BY 1,2 ORDER BY 1,2"}, "hotspots": {"description": "Top one-degree bins of apparent fishing activity for retrieved document categories.", "parameters": {"start": "DATE", "end": "DATE", "min_lat": "FLOAT64", "max_lat": "FLOAT64", "n": "INT64"}, "output_columns": ["latitude", "longitude", "fishing_hours"], "domain_rules": "FLOOR latitude/longitude including negative coordinates. Bin coordinates are lower-left corners, not centroids. Rank hours descending then coordinates. Candidate mapping must be deduplicated.", "sql": "WITH mapped AS (SELECT DISTINCT geartype FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.document_map` WHERE document_id IN UNNEST(@candidate_ids))\nSELECT CAST(FLOOR(cell_ll_lat) AS INT64) latitude,CAST(FLOOR(cell_ll_lon) AS INT64) longitude,ROUND(SUM(fishing_hours),3) fishing_hours\nFROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.monthly_effort` JOIN mapped USING(geartype)\nWHERE date>=@start AND date<@end AND cell_ll_lat>=@min_lat AND cell_ll_lat<@max_lat AND fishing_hours>0 GROUP BY 1,2 ORDER BY fishing_hours DESC,latitude,longitude LIMIT @n"}, "fleet_activity": {"description": "Q1 observed active fishing vessels and hours by gear, retaining unmatched metadata.", "parameters": {"start": "DATE", "end": "DATE"}, "output_columns": ["geartype", "vessels", "fishing_hours", "hours_per_vessel"], "domain_rules": "Join on MMSI AND year. Count distinct vessel identifiers across grid cells and dates. Include unmatched daily records under unknown. One vessel-year metadata row per key is a checked prerequisite. Only positive fishing hours. Vessel IDs are AIS identifiers, not guaranteed physical-vessel identities.", "sql": "SELECT COALESCE(v.vessel_class_gfw,'unknown') geartype,COUNT(DISTINCT d.mmsi) vessels,ROUND(SUM(d.fishing_hours),3) fishing_hours,\nROUND(SAFE_DIVIDE(SUM(d.fishing_hours),COUNT(DISTINCT d.mmsi)),3) hours_per_vessel FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` d LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` v ON d.mmsi=v.mmsi AND EXTRACT(YEAR FROM d.date)=v.year\nWHERE d.date>=@start AND d.date<@end AND d.fishing_hours>0 GROUP BY 1 ORDER BY fishing_hours DESC,geartype"}, "metadata_audit": {"description": "Count metadata availability and associated activity, keeping unmatched rows.", "parameters": {"start": "DATE", "end": "DATE"}, "output_columns": ["metadata_status", "vessels", "fishing_hours"], "domain_rules": "Priority: unmatched MMSI-year -> no_metadata; matched with NULL registry gear -> no_registry_gear; otherwise registry_gear_available. Count DISTINCT MMSI, never sum grid-cell vessel counts. Preserve left join.", "sql": "SELECT CASE WHEN v.mmsi IS NULL THEN 'no_metadata' WHEN v.vessel_class_registry IS NULL THEN 'no_registry_gear' ELSE 'registry_gear_available' END metadata_status,\nCOUNT(DISTINCT d.mmsi) vessels,ROUND(SUM(d.fishing_hours),3) fishing_hours FROM `cs145-p2-sandbox-20261011.cs145_fishing_reference.daily_effort` d LEFT JOIN `cs145-p2-sandbox-20261011.cs145_fishing_reference.vessels` v ON d.mmsi=v.mmsi AND EXTRACT(YEAR FROM d.date)=v.year\nWHERE d.date>=@start AND d.date<@end AND d.fishing_hours>0 GROUP BY 1 ORDER BY 1"}}}}

gpt-5.2-2025-12-11 · SQL execution: 1.23s