[{'Text': '\ndef get_dataset_summary():\n    """This will be visible to the agent at all times, so keep it short, but let the agent know if the dataset can answer the question of the user."""\n    return """\n    UK broadband infrastructure availability data at postcode level. \n    Monthly snapshots showing operator footprint and premises coverage (not subscriber numbers). \n    Also contains the whole UK at postcode granularity, with geographic and demographic data.\n    """\n\n\ndef get_db_info():\n    return f"""\n    {DB_INFO}\n\n    {DB_SCHEMA}\n\n    {SQL_EXAMPLES}\n    """\n\nDB_INFO = """\nUPC = unit post code. All UPC tables at postcode granularity, monthly snapshots.\nreported_at field: first day of each month.\n\nCRITICAL DISTINCTIONS:\n- Footprint/premises passed ≠ market share (footprint = availability, market share = paying subscribers)\n- This is AVAILABILITY data, not subscriber counts\n\nSANITY CHECK VALUES:\n- UK premises: ~33M | households: ~30M | population: ~67M\n- Always verify totals against these values\n\nOPERATOR DEFINITIONS:\n- Virgin Media ISPs: \'Virgin Media RFOG\', \'Virgin Cable\' (NOT altnet)\n- \'nexfibre Virgin Media\': considered altnet in our systems\n- CityFibre variants: \'CityFibre Vodafone\', \'CityFibre TalkTalk\', etc. (wholesale relationships)\n- Use get_distinct_value_list(\'upc\',\'operator\') to see full operator list\n\nALTNET FILTERING:\nExclude: BT, Sky, TalkTalk, Vodafone, Virgin Cable, Virgin Media RFOG, KCOM Lightstream\nExample: where operator not in (\'BT\',\'Sky\',\'TalkTalk\',\'Vodafone\',\'Virgin Cable\',\'Virgin Media RFOG\') and operator not like \'%nexfibre%\'\n\nCRITICAL: CITYFIBRE CANONICALIZATION\nCityFibre appears as multiple variants due to wholesale relationships.\nMUST canonicalize before counting distinct operators or calculating overbuild:\n  case when operator like \'%CityFibre%\' then \'CityFibre\' else operator end\n\nWRONG: count(distinct operator) where operator not in (...)\nRIGHT: use canonicalized operator_canonical THEN count distinct\n\nCRITICAL: MARKET SHARE CALCULATIONS\nfact_operator is unique per (postcode, operator, tech, reported_at).\nJoining to upc_output creates duplicates when operators have multiple techs in same postcode.\n\nRULES:\n1. Individual operator footprint: distinct postcodes from fact_operator joined to upc_output\n2. Total UK premises: SUM(premises) FROM upc_output directly (NEVER derive from fact_operator joins)\n3. For homes passed by operator: use households metric from upc_output\n\nCRITICAL: TIME SERIES VS NON-TIME-SERIES\nCheck latest dates with get_dataset_status() tool.\n\n- Current/latest/specific date = latest snapshot: use upc_output, fact_operator (simpler, no date filter)\n- Historical trends/comparisons/dates before latest: use upc_output_time_series, fact_operator_time_series\n\nPREFER fact_altnet/fact_altnet_time_series FOR ALTNET QUERIES:\n- CityFibre canonicalization already applied\n- Openreach/VM exclusions already applied\n- Still filter nexfibre if using strict altnet definition: where operator != \'nexfibre Virgin Media\'\n\nTIPS:\n1. FTTP queries: tech like \'%fttp%\' (value is \'fttponly\')\n2. Current data only: use non-time-series tables directly\n"""\n\nDB_SCHEMA = """\nupc_core.reports.fact_operator_time_series (\n\tpostcode varchar(16777216) comment \'name of postcode\',\n\toperator varchar(16777216) comment \'name of operator\',\n\ttech varchar(16777216) comment \'technology\',\n\tfastest_up number(38,2) comment \'fastest upload speed\',\n\tfastest_down number(38,0) comment \'fastest download speed\',\n\tactivated_date date comment \'date when this footprint happenws\',\n\treported_at date comment \'represent for version of postcode (vtable-tbb)\'\n)\n\nupc_core.reports.fact_operator (\n    # this is the same as upc_core.reports.fact_operator_time_series but with only the most recent snapshot of data\n)\n\nupc_core.reports.fact_altnet_time_series (\n\tpostcode varchar(16777216) comment \'name of postcode\',\n\toperator varchar(16777216) comment \'canonicalized altnet operator name (CityFibre variants collapsed)\',\n\ttech varchar(16777216) comment \'technology\',\n\treported_at date comment \'represent for version of postcode (vtable-tbb)\'\n)\n\nupc_core.reports.fact_altnet (\n    # this is the same as upc_core.reports.fact_altnet_time_series but with only the most recent snapshot of data\n    # CRITICAL: Use this table for altnet queries - it has CityFibre canonicalization and openreach/VM exclusions already applied\n    # Excluded operators: BT, Sky, TalkTalk, Vodafone, Virgin Cable, Virgin Media RFOG, KCOM Lightstream\n    # NOTE: Currently includes \'nexfibre Virgin Media\' which may need filtering depending on your definition of altnet\n)\n\nupc_core.reports.upc_output_time_series (\n\tpostcode varchar(16777216) comment \'key for upc_output, unique per reporting month\',\n\tmapinfo_id number(18,0) comment \'map information identification code\',\n\tpost_sector varchar(16777216) comment \'higher level postcode grouping\',\n\tnorthings number(38,0) comment \'distance in metres north of national grid origin\',\n\teastings number(38,0) comment \'distance in metres east of national grid origin\',\n\tcoa_code varchar(16777216) comment \'ons-defined code for the census output area in which the upc is located\',\n\tlsoa varchar(16777216) comment \'ons-defined code for the lower super output area in which the upc is located\',\n\tmsoa_and_im varchar(16777216) comment \'ons-defined code for the middle super output area or intermediate zone in scotland in which the upc is located\',\n\tla_code varchar(16777216) comment \'local authority area code\',\n\tla_name varchar(16777216) comment \'local authority area name\',\n\tgovernment_region varchar(16777216) comment \'government region\',\n\tcountry varchar(16777216) comment \'name of the nation in which the upc is located\',\n\tpopulation number(38,2) comment \'estimated population of the upc\',\n\tpremises number(38,2) comment \'total number of households and business premises (sites or workplaces) in the upc\',\n\thouseholds number(38,0) comment \'estimated number of households in the upc\',\n\tbus_sites_total number(38,2) comment \'estimated number of business premises (sites or workplaces) in the upc\',\n\tmdfcode varchar(16777216) comment \'identifier for bt/openreach exchange serving the upc\',\n\texchange_name varchar(16777216) comment \'name of exchange serving the upc\',\n\tcityfibre_postcode_passed varchar(1) comment \'whether the upc is within cityfibre halo (200m-500m)\',\n)\n\nupc_core.reports.upc_output (\n    # this is the same as upc_core.reports.upc_output_time_series but with only the most recent snapshot of data\n)\n\n"""\n\nSQL_EXAMPLES = [\n    {\n        \'request\': \'What is the current FTTP footprint of the top 10 operators?\',\n        \'response\': """\n-- Essential pattern: distinct postcodes from fact_operator joined to upc_output for premise counts\nselect\n  case when f.operator like \'%CityFibre%\' then \'CityFibre\' else f.operator end as operator_group,\n  round(sum(u.premises)) as total_premises_passed\nfrom (\n  select distinct operator, postcode\n  from upc_core.reports.fact_operator\n  where tech like \'%fttp%\'\n) as f\njoin upc_core.reports.upc_output as u on f.postcode = u.postcode\ngroup by operator_group\norder by total_premises_passed desc\nlimit 10"""\n    },\n    {\n        \'request\': \'Show me altnet FTTP network overbuild: how many premises were passed by 1, 2, 3, 4, 5+ altnet FTTP networks\',\n        \'response\': """\n-- PREFERRED: Use fact_altnet (CityFibre canonicalization and exclusions already applied)\nwith operator_count_per_postcode as (\n  select postcode, count(distinct operator) as altnet_count\n  from upc_core.reports.fact_altnet\n  where tech like \'%fttp%\' and operator != \'nexfibre Virgin Media\'\n  group by postcode\n)\nselect \n  altnet_count as number_of_altnet_fttp_networks,\n  round(sum(u.premises), 0) as premises_passed\nfrom operator_count_per_postcode o\njoin upc_core.reports.upc_output u using (postcode)\ngroup by altnet_count\norder by altnet_count asc"""\n    }\n]\n'}]