[{'Text': '1. CLASS: fundamental concepts (e.g. foaf_organization, cto_network, cto_broadband_service)\n2. OBJECT_PROPERTY: relationships (e.g. cto_uses_network, cto_owns_network, cto_offers_broadband_service)\n3. DATA_PROPERTY: attributes (e.g. cto_is_retail_only, cto_has_subscribers)\n\nCRITICAL: DATE COLUMNS ARE Nullable(String)\nAll date columns stored as strings. Use toDate() when filtering:\n  WHERE toDate(valid_from) <= today()\n  WHERE valid_to IS NULL OR toDate(valid_to) > today()\n\nTEMPORAL VALIDITY:\nAll entities have valid_from/valid_to for historical tracking; valid_to = NULL if still active.\nFilter for current entities:\n  WHERE valid_to IS NULL OR toDate(valid_to) > today()\n\nORGANIZATION TYPES:\n"ISP", "Network Operator", "Non Telco Organization" (exact spelling)\nCountry: "United Kingdom" (exact spelling)\n\nGENERIC MEASUREMENTS:\ncto_measurement_record contains generic measurements describing geo/demographic data.\nTo explore available aspects:\nSELECT DISTINCT ASPECT_NAME FROM cto_measurement_record;\n\nCOLUMN NAMES ARE UPPERCASE in ClickHouse (loaded from Snowflake exports).\nUse uppercase column names: ORGANIZATION_NAME, VALID_FROM, VALID_TO, ENTITY_NAME, etc.\n\nQUERY TIP:\nStart with simple queries to check exact spelling and column names:\nSELECT * FROM foaf_organization LIMIT 5;\nSELECT * FROM cto_network LIMIT 5;\n"""\n\nSQL_EXAMPLES = [\n    {\n        "request": "List all current ISPs",\n        "response": """\n            SELECT o.ORGANIZATION_NAME, o.COUNTRY\n            FROM foaf_organization o\n            JOIN cto_is_organization_type ot\n              ON o.ORGANIZATION_NAME = ot.ORGANIZATION_NAME\n            WHERE ot.ORGANIZATION_TYPE_NAME = \'ISP\'\n              AND (o.VALID_TO IS NULL OR toDate(o.VALID_TO) > today())\n        """,\n    },\n    {\n        "request": "Show current ISP-network relationships",\n        "response": """\n            SELECT\n              un.ISP_NAME,\n              un.NETWORK_NAME,\n              own.NETWORK_OPERATOR_NAME AS network_owner\n            FROM cto_uses_network un\n            JOIN cto_owns_network own\n              ON un.NETWORK_NAME = own.NETWORK_NAME\n            WHERE un.VALID_TO IS NULL OR toDate(un.VALID_TO) > today()\n        """,\n    },\n    {\n        "request": "Percentage of UK premises Virgin Media could potentially sell to",\n        "response": """\n            WITH get_network_footprint_pcd AS (\n              SELECT DISTINCT LOCATION_NAME AS postcode\n              FROM cto_measurement_record\n              WHERE ASPECT_NAME = \'presence\'\n                AND MAGNITUDE_NAME = \'1\'\n                AND VALID_TO IS NULL\n                AND ENTITY_NAME IN (\n                  SELECT NETWORK_NAME\n                  FROM cto_uses_network\n                  WHERE ISP_NAME = \'Virgin Media\'\n                )\n            ),\n            get_prems_to_pcd_lookup AS (\n              SELECT LOCATION_NAME AS postcode, MAGNITUDE_NAME AS prems\n              FROM cto_measurement_record\n              WHERE ENTITY_NAME = \'UPC Premises\'\n                AND VALID_TO IS NULL\n            ),\n            get_prem_count AS (\n              SELECT sum(toFloat64OrZero(prems)) AS prems_sum\n              FROM get_network_footprint_pcd\n              INNER JOIN get_prems_to_pcd_lookup USING (postcode)\n            )\n            SELECT * FROM get_prem_count\n        """,\n    },\n    {\n        "request": "Postcodes which got their very first full fibre connection in the last year",\n        "response": """\n            WITH fttp_networks AS (\n              SELECT NETWORK_NAME\n              FROM cto_uses_link_standard\n              WHERE LINK_STANDARD_NAME = \'FTTP\'\n            ),\n            first_fttp_by_postcode AS (\n              SELECT\n                LOCATION_NAME AS postcode,\n                min(toDate(VALID_FROM)) AS first_fttp_date\n              FROM cto_measurement_record\n              WHERE ASPECT_NAME = \'presence\'\n                AND MAGNITUDE_NAME = \'1\'\n                AND ENTITY_NAME IN (SELECT NETWORK_NAME FROM fttp_networks)\n              GROUP BY LOCATION_NAME\n            )\n            SELECT postcode, first_fttp_date\n            FROM first_fttp_by_postcode\n            WHERE first_fttp_date >= today() - INTERVAL 1 YEAR\n            ORDER BY first_fttp_date DESC\n        """,\n    },\n]\n\n# Schema sourced from cto_table (ontology metadata table)\nDB_SCHEMA = \'\'\'\nTABLE_NAME,ONTOLOGY_VIEW_NAME,ENTITY_TYPE,DESCRIPTION,COLUMN_NAMES\ncto_broadband_service,business,class,Any service specifically related to providing broadband services.,"[""BROADBAND_SERVICE_NAME"",""ISP_NAME"",""TECH"",""FASTEST_DOWN"",""GUARANTEED_DOWN"",""FASTEST_UP"",""GUARANTEED_UP"",""MONTHLY_COST"",""SETUP_COST"",""LOCAL_CURRENCY"",""CONTRACT_LENGTH"",""BUNDLE_HOME_PHONE"",""BUNDLE_MOBILE_PHONE"",""BUNDLE_VIDEO"",""VALID_FROM"",""VALID_TO"",""COMMENT"",""COUNTRY""]"\ncto_concept_entity,business,class,"A conceptual entity. Generally, an entity that does not occupy space in the physical world. Mutually exclusive with cto_physical_entities.","[""CONCEPT_ENTITY_NAME"",""COMMENT""]"\ncto_measurement_record,business,class,"A record containing quantitative measurement data for telecom entities, including aspects, magnitudes, units, and metadata.","[""ENTITY_NAME"",""ASPECT_NAME"",""LOCATION_NAME"",""MAGNITUDE_NAME"",""UNIT_OF_MEASURE_NAME"",""ACCURACY_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT"",""COUNTRY"",""DATA_SOURCE"",""ID""]"\ncto_physical_entities,business,class,"A physical entity. Generally, an entity that occupies space in the physical world. Mutually exclusive with cto_concept_entity.","[""PHYSICAL_ENTITY_NAME"",""COMMENT""]"\ncto_physical_infrastructure,business,class,"Categories of physical infrastructure, which must occupy physical space in/on the ground.","[""PHYSICAL_INFRASTRUCTURE_NAME"",""COMMENT""]"\ncto_service,business,class,Any service provided by a company to a user. Includes broadband/entertainment/mobile services.,"[""SERVICE_NAME"",""COMMENT""]"\ndcterms_location,business,class,"A spatial region or named place. Any spatial thing considered to be a location, e.g. country, street, postcode, NUTS3 ID.","[""LOCATION_NAME"",""LOCATION_TYPE_NAME"",""DATA_SOURCE"",""GEO"",""VALID_FROM"",""VALID_TO"",""COMMENT"",""COUNTRY"",""ID""]"\nwgs84_spatial_thing,business,class,"Anything with spatial extent, i.e. size, shape, or position. e.g. people, places, bowling balls, as well as abstract areas like cubes.","[""SPATIAL_THING_NAME"",""GEO"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_containment_assertion,business,object_property,"Relates spatial entities to express containment relationships (e.g., a city is contained within a region).","[""SPATIAL_SUBJECT"",""SPATIAL_OBJECT"",""SPATIAL_RELATION"",""VALID_FROM"",""VALID_TO"",""COUNTRY"",""ID""]"\ncto_has_coverage_data,business,object_property,"coverage level of a network at a location (percentage, population, or otherwise)","[""NETWORK_NAME"",""LOCATION_NAME"",""VALUE"",""UNIT"",""VALID_FROM"",""VALID_TO"",""COMMENT"",""COUNTRY""]"\ncto_offers_broadband_service,business,object_property,Relates an ISP to the broadband services it offers to customers.,"[""ISP_NAME"",""BROADBAND_SERVICE_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_accuracy,core,class,"A classification of the precision or reliability level of a measurement or data point, ranging from high to low accuracy, or unknown when accuracy cannot be determined.","[""ACCURACY_NAME"",""COMMENT""]"\ncto_country,core,class,A sovereign state or nation used for geographical and administrative classification in telecom data.,"[""COUNTRY_NAME"",""COMMENT""]"\ncto_link,core,class,The physical transmission medium or infrastructure type used to connect a premises or network endpoint to the broadband access network.,"[""LINK_NAME"",""COMMENT""]"\ncto_link_standard,core,class,The data transmission standard or protocol used over the physical transmission medium/infrastructure (cto_link) to deliver broadband connectivity.,"[""LINK_STANDARD_NAME"",""COMMENT""]"\ncto_network,core,class,Any physical infrastructure network that forms a telecom-related network and occupies physical space in/on the ground.,"[""NETWORK_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT"",""COUNTRY""]"\ncto_network_type,core,class,"A classification type for networks based on their technology and service delivery method (e.g., Fixed, Satellite, Mobile, Hybrid).","[""NETWORK_TYPE_NAME"",""COMMENT""]"\ncto_organization_type,core,class,"A classification type for organizations based on their role in the telecom industry (e.g., ISP, Network Operator, Non Telco Organization).","[""ORGANIZATION_TYPE_NAME"",""COMMENT""]"\ncto_unit_of_measure,core,class,"A standardized quantity used to express and compare measurements, such as binary values, household counts, or percentage values.","[""UNIT_OF_MEASUREMENT_NAME"",""COMMENT""]"\nfoaf_organization,core,class,"Any kind of organization. The Organization class represents a kind of Agent corresponding to social instititutions such as companies, societies etc.","[""ORGANIZATION_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT"",""COUNTRY""]"\ngist_aspect,core,class,"A particular feature or dimension of measurement that can be observed or quantified, such as coverage, presence, roadworks location, or subscriber counts.","[""ASPECT_NAME"",""COMMENT""]"\ncto_is_altnet,core,data_property,"UK-specific term: an Altnet is an informal term for non-incumbent network operator usually deploying its own high-speed (fibre optic) network in the UK. Usually understood to represent all network operators except BT/Openreach, Virgin Media, KCOM. While generally referring to network operators, we also label any ISPs related to these network operators as altnets.","[""ORGANIZATION_NAME"",""IS_ALTNET"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_is_business_only,core,data_property,Specifies whether an organization (ISP or Network Operator) is business-only (exclusively sells to business customers).,"[""ISP_NAME"",""IS_BUSINESS_ONLY"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_is_network_segment,core,data_property,"Indicates whether a network represents a segment or part of a larger network infrastructure, rather than a complete network.","[""NETWORK_NAME"",""IS_NETWORK_SEGMENT"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_is_retail_only,core,data_property,Specifies whether an ISP is retail-only (does not own any network infrastructure).,"[""ISP_NAME"",""IS_RETAIL_ONLY"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_is_wholesale_only,core,data_property,Specifies whether a network operator is only wholesale (does not sell services directly to end-users).,"[""NETWORK_OPERATOR_NAME"",""IS_WHOLESALE_ONLY"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_has_corporate_relationship,core,object_property,Indicates that one organization has an ownership/investment stake in another organization.,"[""RELATIONSHIP_FROM"",""RELATIONSHIP_TO"",""RELATIONSHIP_TYPE"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_is_consolidated,core,object_property,Relates a network or organization to the network or organization it is consolidated into.,"[""CONSOLIDATED_FROM"",""CONSOLIDATED_TO"",""ANNOUNCED_AT"",""COMPLETED_AT"",""IS_MERGED"",""IS_ACQUIRED"",""IS_DISSOLVED"",""COMMENT"",""COUNTRY""]"\ncto_is_network_type,core,object_property,"Relates a network to its type classification (e.g., Fixed, Satellite, Mobile, Hybrid).","[""NETWORK_NAME"",""NETWORK_TYPE_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_is_organization_type,core,object_property,"Relates an organization to its type classification (e.g., ISP, Network Operator, Non Telco Organization).","[""ORGANIZATION_NAME"",""ORGANIZATION_TYPE_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_owns_network,core,object_property,Relates a telco organization to the network it owns.,"[""NETWORK_OPERATOR_NAME"",""NETWORK_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ncto_supports_link_standard,core,object_property,Relates a physical transmission medium/infrastructure link to the protocol/standard.,"[""LINK_NAME"",""LINK_STANDARD_NAME"",""COMMENT""]"\ncto_uses_link,core,object_property,Relates a network to the link technology it uses.,"[""NETWORK_NAME"",""LINK_NAME"",""COMMENT""]"\ncto_uses_link_standard,core,object_property,Relates a network to the protocol/standard used by the physical link infrastructure.,"[""NETWORK_NAME"",""LINK_STANDARD_NAME"",""COMMENT""]"\ncto_uses_network,core,object_property,Relates an ISP to the network it uses to provide services.,"[""ISP_NAME"",""NETWORK_NAME"",""VALID_FROM"",""VALID_TO"",""COMMENT""]"\ndcat_catalogue,documents,class,A catalogue or repository that hosts the Datasets or Data Services being described.,"[""TITLE"",""DESCRIPTION"",""PUBLISHER"",""CREATOR"",""SERVICE"",""GEOGRAPHICAL_COVERAGE"",""TEMPORAL_COVERAGE"",""THEMES"",""RELEASE_DATE"",""MODIFICATION_DATE"",""LANGUAGE"",""RIGHTS""]"\n'}]