保留零订单客户

{
  "actual_digest": "cd40a6e44569e7a66556bfeecb9d72bca881dab5f1ec79061a728a991eacb475",
  "assumptions": [],
  "attempt": 1,
  "category": "join_semantics",
  "comparison": {
    "abs_tolerance": "0",
    "decimal_scale": 0,
    "duplicate_policy": "multiset",
    "max_rows": 10000,
    "rel_tolerance": "0",
    "row_order_significant": true
  },
  "difficulty": "medium",
  "error_code": null,
  "error_message": null,
  "execution_ms": 215.4034999985015,
  "expected_digest": "d0930aa0c0840f902dc9acbe1aaf084d6341194824b553f5a51ed3ed7743edf5",
  "expected_result_preview": {
    "columns": [
      {
        "name": "customer_id",
        "type": "BIGINT"
      },
      {
        "name": "customer_name",
        "type": "VARCHAR"
      },
      {
        "name": "completed_order_count",
        "type": "BIGINT"
      }
    ],
    "digest": "d0930aa0c0840f902dc9acbe1aaf084d6341194824b553f5a51ed3ed7743edf5",
    "row_count": 120,
    "rows": [
      [
        1,
        "客户-001",
        12
      ],
      [
        13,
        "客户-013",
        10
      ],
      [
        17,
        "客户-017",
        10
      ],
      [
        12,
        "客户-012",
        9
      ],
      [
        53,
        "客户-053",
        9
      ],
      [
        107,
        "客户-107",
        9
      ],
      [
        5,
        "客户-005",
        8
      ],
      [
        21,
        "客户-021",
        8
      ],
      [
        104,
        "客户-104",
        8
      ],
      [
        2,
        "客户-002",
        7
      ],
      [
        4,
        "客户-004",
        7
      ],
      [
        20,
        "客户-020",
        7
      ],
      [
        36,
        "客户-036",
        7
      ],
      [
        48,
        "客户-048",
        7
      ],
      [
        67,
        "客户-067",
        7
      ],
      [
        90,
        "客户-090",
        7
      ],
      [
        109,
        "客户-109",
        7
      ],
      [
        9,
        "客户-009",
        6
      ],
      [
        10,
        "客户-010",
        6
      ],
      [
        15,
        "客户-015",
        6
      ],
      [
        19,
        "客户-019",
        6
      ],
      [
        24,
        "客户-024",
        6
      ],
      [
        29,
        "客户-029",
        6
      ],
      [
        41,
        "客户-041",
        6
      ],
      [
        43,
        "客户-043",
        6
      ],
      [
        64,
        "客户-064",
        6
      ],
      [
        69,
        "客户-069",
        6
      ],
      [
        71,
        "客户-071",
        6
      ],
      [
        80,
        "客户-080",
        6
      ],
      [
        94,
        "客户-094",
        6
      ],
      [
        105,
        "客户-105",
        6
      ],
      [
        26,
        "客户-026",
        5
      ],
      [
        30,
        "客户-030",
        5
      ],
      [
        31,
        "客户-031",
        5
      ],
      [
        44,
        "客户-044",
        5
      ],
      [
        46,
        "客户-046",
        5
      ],
      [
        52,
        "客户-052",
        5
      ],
      [
        57,
        "客户-057",
        5
      ],
      [
        60,
        "客户-060",
        5
      ],
      [
        65,
        "客户-065",
        5
      ],
      [
        74,
        "客户-074",
        5
      ],
      [
        82,
        "客户-082",
        5
      ],
      [
        84,
        "客户-084",
        5
      ],
      [
        97,
        "客户-097",
        5
      ],
      [
        98,
        "客户-098",
        5
      ],
      [
        102,
        "客户-102",
        5
      ],
      [
        106,
        "客户-106",
        5
      ],
      [
        14,
        "客户-014",
        4
      ],
      [
        16,
        "客户-016",
        4
      ],
      [
        22,
        "客户-022",
        4
      ],
      [
        32,
        "客户-032",
        4
      ],
      [
        35,
        "客户-035",
        4
      ],
      [
        38,
        "客户-038",
        4
      ],
      [
        45,
        "客户-045",
        4
      ],
      [
        49,
        "客户-049",
        4
      ],
      [
        50,
        "客户-050",
        4
      ],
      [
        56,
        "客户-056",
        4
      ],
      [
        58,
        "客户-058",
        4
      ],
      [
        59,
        "客户-059",
        4
      ],
      [
        61,
        "客户-061",
        4
      ],
      [
        70,
        "客户-070",
        4
      ],
      [
        72,
        "客户-072",
        4
      ],
      [
        76,
        "客户-076",
        4
      ],
      [
        78,
        "客户-078",
        4
      ],
      [
        79,
        "客户-079",
        4
      ],
      [
        81,
        "客户-081",
        4
      ],
      [
        83,
        "客户-083",
        4
      ],
      [
        85,
        "客户-085",
        4
      ],
      [
        88,
        "客户-088",
        4
      ],
      [
        89,
        "客户-089",
        4
      ],
      [
        92,
        "客户-092",
        4
      ],
      [
        93,
        "客户-093",
        4
      ],
      [
        95,
        "客户-095",
        4
      ],
      [
        100,
        "客户-100",
        4
      ],
      [
        11,
        "客户-011",
        3
      ],
      [
        18,
        "客户-018",
        3
      ],
      [
        25,
        "客户-025",
        3
      ],
      [
        27,
        "客户-027",
        3
      ],
      [
        34,
        "客户-034",
        3
      ],
      [
        37,
        "客户-037",
        3
      ],
      [
        47,
        "客户-047",
        3
      ],
      [
        62,
        "客户-062",
        3
      ],
      [
        66,
        "客户-066",
        3
      ],
      [
        77,
        "客户-077",
        3
      ],
      [
        86,
        "客户-086",
        3
      ],
      [
        87,
        "客户-087",
        3
      ],
      [
        108,
        "客户-108",
        3
      ],
      [
        3,
        "客户-003",
        2
      ],
      [
        6,
        "客户-006",
        2
      ],
      [
        23,
        "客户-023",
        2
      ],
      [
        28,
        "客户-028",
        2
      ],
      [
        39,
        "客户-039",
        2
      ],
      [
        51,
        "客户-051",
        2
      ],
      [
        55,
        "客户-055",
        2
      ],
      [
        63,
        "客户-063",
        2
      ],
      [
        73,
        "客户-073",
        2
      ],
      [
        75,
        "客户-075",
        2
      ],
      [
        96,
        "客户-096",
        2
      ],
      [
        103,
        "客户-103",
        2
      ],
      [
        110,
        "客户-110",
        2
      ],
      [
        7,
        "客户-007",
        1
      ],
      [
        8,
        "客户-008",
        1
      ],
      [
        33,
        "客户-033",
        1
      ],
      [
        40,
        "客户-040",
        1
      ],
      [
        42,
        "客户-042",
        1
      ],
      [
        54,
        "客户-054",
        1
      ],
      [
        68,
        "客户-068",
        1
      ],
      [
        91,
        "客户-091",
        1
      ],
      [
        99,
        "客户-099",
        0
      ],
      [
        101,
        "客户-101",
        0
      ],
      [
        111,
        "客户-111",
        0
      ],
      [
        112,
        "客户-112",
        0
      ],
      [
        113,
        "客户-113",
        0
      ],
      [
        114,
        "客户-114",
        0
      ],
      [
        115,
        "客户-115",
        0
      ],
      [
        116,
        "客户-116",
        0
      ],
      [
        117,
        "客户-117",
        0
      ],
      [
        118,
        "客户-118",
        0
      ],
      [
        119,
        "客户-119",
        0
      ],
      [
        120,
        "客户-120",
        0
      ]
    ]
  },
  "finished_at": "2026-08-29T22:06:18.219209",
  "formatted_sql": "SELECT\n  c.customer_id,\n  c.customer_name,\n  COUNT(DISTINCT CASE WHEN o.status = 'completed' THEN o.order_id END) AS completed_order_count\nFROM dim_customers AS c\nLEFT JOIN fact_orders AS o\n  ON o.customer_id = c.customer_id\nGROUP BY\n  c.customer_id,\n  c.customer_name\nORDER BY\n  completed_order_count DESC,\n  c.customer_id ASC",
  "generated_sql": "SELECT\n    c.customer_id,\n    c.customer_name,\n    COUNT(DISTINCT CASE\n        WHEN o.status = 'completed' THEN o.order_id\n    END) AS completed_order_count\nFROM dim_customers AS c\nLEFT JOIN fact_orders AS o\n    ON o.customer_id = c.customer_id\nGROUP BY\n    c.customer_id,\n    c.customer_name\nORDER BY\n    completed_order_count DESC,\n    c.customer_id ASC;",
  "generation_ms": null,
  "id": 111,
  "model_name": "GPT 当前会话桥接（流程验收）",
  "model_run_id": 19,
  "plan": {
    "filters": [
      "仅在聚合条件中计入 fact_orders.status = 'completed'，不使用 WHERE 过滤右表。"
    ],
    "grain": "每行一个客户。",
    "joins": [
      "dim_customers LEFT JOIN fact_orders ON fact_orders.customer_id = dim_customers.customer_id，保留零订单客户。"
    ],
    "metrics": [
      "completed_order_count = COUNT(DISTINCT CASE WHEN fact_orders.status = 'completed' THEN fact_orders.order_id END)。"
    ],
    "risks": [
      "若将完成状态条件放入 WHERE，会丢失零订单客户。",
      "连接订单表后必须按客户聚合，避免输出重复客户。"
    ],
    "sources": [
      "dim_customers",
      "fact_orders"
    ],
    "steps": [
      "以 dim_customers 为主表左连接 fact_orders。",
      "按 customer_id、customer_name 分组并计算已完成订单数。",
      "按 completed_order_count 降序、customer_id 升序排序。"
    ]
  },
  "prompt": "你是 Text-to-SQL 数据分析与数据开发生成器。先形成简洁、可审计的结构化查询方案，再生成完成问题所需的 SQL；不要输出隐藏推理。\n\n方言与安全规则：\nUse DuckDB SQL. Return exactly one read-only query. Do not access files, URLs, extensions, or schemas outside the supplied tables.\n\n数据库结构：\n{\"semantic_relationships\":[{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.customer_id = dim_customers.customer_id\",\"to_entity\":\"customer\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.channel_id = dim_channels.channel_id\",\"to_entity\":\"channel\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.product_id = dim_products.product_id\",\"to_entity\":\"product\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"payment\",\"sql_on\":\"fact_payments.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"return\",\"sql_on\":\"fact_returns.order_id = fact_order_items.order_id AND fact_returns.line_no = fact_order_items.line_no\",\"to_entity\":\"order_item\"}],\"tables\":[{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"channel_id\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"channel_name\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"channel_type\",\"nullable\":false}],\"foreign_keys\":[],\"name\":\"dim_channels\",\"primary_key\":[\"channel_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"customer_id\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"customer_name\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"city\",\"nullable\":true},{\"data_type\":\"DATE\",\"name\":\"signup_date\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"segment\",\"nullable\":false}],\"foreign_keys\":[],\"name\":\"dim_customers\",\"primary_key\":[\"customer_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"product_id\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"product_name\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"category\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"brand\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"list_price\",\"nullable\":false}],\"foreign_keys\":[],\"name\":\"dim_products\",\"primary_key\":[\"product_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"line_no\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"product_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"quantity\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"unit_price\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"discount_amount\",\"nullable\":false}],\"foreign_keys\":[{\"columns\":[\"order_id\"],\"referenced_columns\":[\"order_id\"],\"referenced_table\":\"fact_orders\"},{\"columns\":[\"product_id\"],\"referenced_columns\":[\"product_id\"],\"referenced_table\":\"dim_products\"}],\"name\":\"fact_order_items\",\"primary_key\":[\"order_id\",\"line_no\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"customer_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"channel_id\",\"nullable\":false},{\"data_type\":\"DATE\",\"name\":\"order_date\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"status\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"total_amount\",\"nullable\":false}],\"foreign_keys\":[{\"columns\":[\"customer_id\"],\"referenced_columns\":[\"customer_id\"],\"referenced_table\":\"dim_customers\"},{\"columns\":[\"channel_id\"],\"referenced_columns\":[\"channel_id\"],\"referenced_table\":\"dim_channels\"}],\"name\":\"fact_orders\",\"primary_key\":[\"order_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"payment_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"TIMESTAMP\",\"name\":\"paid_at\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"payment_method\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"amount\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"status\",\"nullable\":false}],\"foreign_keys\":[{\"columns\":[\"order_id\"],\"referenced_columns\":[\"order_id\"],\"referenced_table\":\"fact_orders\"}],\"name\":\"fact_payments\",\"primary_key\":[\"payment_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"return_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"line_no\",\"nullable\":false},{\"data_type\":\"TIMESTAMP\",\"name\":\"returned_at\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"return_qty\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"refund_amount\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"reason\",\"nullable\":true}],\"foreign_keys\":[{\"columns\":[\"order_id\",\"line_no\"],\"referenced_columns\":[\"order_id\",\"line_no\"],\"referenced_table\":\"fact_order_items\"}],\"name\":\"fact_returns\",\"primary_key\":[\"return_id\"]}]}\n\n语义层与业务口径：\n{\"business_rules\":[\"完成订单仅指 fact_orders.status = 'completed'。\",\"净销售额为 quantity * unit_price - discount_amount。\",\"paid、refunded、failed 金额只按 fact_payments.status 分类。\",\"退货率为完成订单的 returned_qty / sold_qty，售出数量仅含完成订单。\",\"月份按 UTC Gregorian calendar 计算。\",\"订单头 total_amount 应等于订单行净额汇总，差异视为数据质量异常。\"],\"dimensions\":[{\"data_type\":\"VARCHAR\",\"description\":\"客户分群\",\"expression\":\"dim_customers.segment\",\"name\":\"customer_segment\"},{\"data_type\":\"VARCHAR\",\"description\":\"商品品类\",\"expression\":\"dim_products.category\",\"name\":\"product_category\"},{\"data_type\":\"VARCHAR\",\"description\":\"渠道类型\",\"expression\":\"dim_channels.channel_type\",\"name\":\"channel_type\"},{\"data_type\":\"VARCHAR\",\"description\":\"UTC Gregorian 月份\",\"expression\":\"strftime(fact_orders.order_date, '%Y-%m')\",\"name\":\"order_month\"}],\"entities\":[{\"description\":\"客户主数据\",\"grain\":\"每行一个客户\",\"name\":\"customer\",\"primary_key\":[\"customer_id\"],\"table\":\"dim_customers\"},{\"description\":\"商品主数据\",\"grain\":\"每行一个商品\",\"name\":\"product\",\"primary_key\":[\"product_id\"],\"table\":\"dim_products\"},{\"description\":\"渠道主数据\",\"grain\":\"每行一个渠道\",\"name\":\"channel\",\"primary_key\":[\"channel_id\"],\"table\":\"dim_channels\"},{\"description\":\"订单头\",\"grain\":\"每行一个订单\",\"name\":\"order\",\"primary_key\":[\"order_id\"],\"table\":\"fact_orders\"},{\"description\":\"订单行\",\"grain\":\"每行一个订单商品行\",\"name\":\"order_item\",\"primary_key\":[\"order_id\",\"line_no\"],\"table\":\"fact_order_items\"},{\"description\":\"支付尝试\",\"grain\":\"每行一笔支付\",\"name\":\"payment\",\"primary_key\":[\"payment_id\"],\"table\":\"fact_payments\"},{\"description\":\"退货记录\",\"grain\":\"每行一条订单行退货\",\"name\":\"return\",\"primary_key\":[\"return_id\"],\"table\":\"fact_returns\"}],\"metrics\":[{\"description\":\"已完成订单数\",\"expression\":\"COUNT(DISTINCT CASE WHEN fact_orders.status = 'completed' THEN fact_orders.order_id END)\",\"filters\":[\"fact_orders.status = 'completed'\"],\"grain\":\"聚合\",\"name\":\"completed_order_count\"},{\"description\":\"完成订单商品行净销售额\",\"expression\":\"SUM(fact_order_items.quantity * fact_order_items.unit_price - fact_order_items.discount_amount)\",\"filters\":[\"fact_orders.status = 'completed'\"],\"grain\":\"聚合\",\"name\":\"net_revenue\"},{\"description\":\"成功支付金额\",\"expression\":\"SUM(CASE WHEN fact_payments.status = 'paid' THEN fact_payments.amount ELSE 0 END)\",\"filters\":[],\"grain\":\"聚合\",\"name\":\"paid_amount\"},{\"description\":\"退款状态支付金额\",\"expression\":\"SUM(CASE WHEN fact_payments.status = 'refunded' THEN fact_payments.amount ELSE 0 END)\",\"filters\":[],\"grain\":\"聚合\",\"name\":\"refunded_amount\"},{\"description\":\"失败支付金额\",\"expression\":\"SUM(CASE WHEN fact_payments.status = 'failed' THEN fact_payments.amount ELSE 0 END)\",\"filters\":[],\"grain\":\"聚合\",\"name\":\"failed_amount\"},{\"description\":\"完成订单退货数量除以售出数量\",\"expression\":\"SUM(fact_returns.return_qty) / SUM(fact_order_items.quantity)\",\"filters\":[\"fact_orders.status = 'completed'\"],\"grain\":\"聚合\",\"name\":\"return_rate\"}],\"relationships\":[{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.customer_id = dim_customers.customer_id\",\"to_entity\":\"customer\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.channel_id = dim_channels.channel_id\",\"to_entity\":\"channel\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.product_id = dim_products.product_id\",\"to_entity\":\"product\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"payment\",\"sql_on\":\"fact_payments.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"return\",\"sql_on\":\"fact_returns.order_id = fact_order_items.order_id AND fact_returns.line_no = fact_order_items.line_no\",\"to_entity\":\"order_item\"}]}\n\n问题：\n列出所有客户及已完成订单数，必须保留零订单客户；输出 customer_id、customer_name、completed_order_count，并按 completed_order_count 降序、customer_id 升序；必须使用 LEFT JOIN。\n\n规划要求：\nplan 只记录输出粒度、数据源、连接、过滤、指标、执行步骤和风险检查；每项保持简短。\nSQL 必须严格实现该 plan，并遵循语义层业务口径。\nsummary 只概括最终做法，assumptions 只列无法由上下文确定的必要假设。\n输出必须严格匹配此 JSON Schema：\n{\"$defs\":{\"QueryPlan\":{\"additionalProperties\":false,\"properties\":{\"filters\":{\"items\":{\"type\":\"string\"},\"title\":\"Filters\",\"type\":\"array\"},\"grain\":{\"title\":\"Grain\",\"type\":\"string\"},\"joins\":{\"items\":{\"type\":\"string\"},\"title\":\"Joins\",\"type\":\"array\"},\"metrics\":{\"items\":{\"type\":\"string\"},\"title\":\"Metrics\",\"type\":\"array\"},\"risks\":{\"items\":{\"type\":\"string\"},\"title\":\"Risks\",\"type\":\"array\"},\"sources\":{\"items\":{\"type\":\"string\"},\"title\":\"Sources\",\"type\":\"array\"},\"steps\":{\"items\":{\"type\":\"string\"},\"title\":\"Steps\",\"type\":\"array\"}},\"required\":[\"grain\",\"sources\",\"joins\",\"filters\",\"metrics\",\"steps\",\"risks\"],\"title\":\"QueryPlan\",\"type\":\"object\"}},\"additionalProperties\":false,\"properties\":{\"assumptions\":{\"items\":{\"type\":\"string\"},\"title\":\"Assumptions\",\"type\":\"array\"},\"plan\":{\"$ref\":\"#/$defs/QueryPlan\"},\"sql\":{\"title\":\"Sql\",\"type\":\"string\"},\"summary\":{\"title\":\"Summary\",\"type\":\"string\"}},\"required\":[\"plan\",\"sql\",\"summary\",\"assumptions\"],\"title\":\"GenerationOutput\",\"type\":\"object\"}",
  "provider_request_id": null,
  "question": "列出所有客户及已完成订单数，必须保留零订单客户；输出 customer_id、customer_name、completed_order_count，并按 completed_order_count 降序、customer_id 升序；必须使用 LEFT JOIN。",
  "radar_dimension": "连接与粒度",
  "raw_output": "{\"plan\":{\"grain\":\"每行一个客户。\",\"sources\":[\"dim_customers\",\"fact_orders\"],\"joins\":[\"dim_customers LEFT JOIN fact_orders ON fact_orders.customer_id = dim_customers.customer_id，保留零订单客户。\"],\"filters\":[\"仅在聚合条件中计入 fact_orders.status = 'completed'，不使用 WHERE 过滤右表。\"],\"metrics\":[\"completed_order_count = COUNT(DISTINCT CASE WHEN fact_orders.status = 'completed' THEN fact_orders.order_id END)。\"],\"steps\":[\"以 dim_customers 为主表左连接 fact_orders。\",\"按 customer_id、customer_name 分组并计算已完成订单数。\",\"按 completed_order_count 降序、customer_id 升序排序。\"],\"risks\":[\"若将完成状态条件放入 WHERE，会丢失零订单客户。\",\"连接订单表后必须按客户聚合，避免输出重复客户。\"]},\"sql\":\"SELECT\\n    c.customer_id,\\n    c.customer_name,\\n    COUNT(DISTINCT CASE\\n        WHEN o.status = 'completed' THEN o.order_id\\n    END) AS completed_order_count\\nFROM dim_customers AS c\\nLEFT JOIN fact_orders AS o\\n    ON o.customer_id = c.customer_id\\nGROUP BY\\n    c.customer_id,\\n    c.customer_name\\nORDER BY\\n    completed_order_count DESC,\\n    c.customer_id ASC;\",\"summary\":\"以客户表为主表 LEFT JOIN 订单表，通过条件去重计数统计已完成订单，保留零订单客户并按指定顺序排序。\",\"assumptions\":[]}",
  "reference_sql": "SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS completed_order_count FROM dim_customers c LEFT JOIN fact_orders o ON o.customer_id = c.customer_id AND o.status = 'completed' GROUP BY c.customer_id, c.customer_name ORDER BY completed_order_count DESC, c.customer_id ASC",
  "requested_model_id": "gpt-session-bridge",
  "required_ast": [
    {
      "id": "left-join",
      "join_type": "LEFT",
      "kind": "join_type",
      "min": 1
    }
  ],
  "resolved_model_id": "gpt-session-bridge",
  "result_preview": {
    "columns": [
      {
        "name": "customer_id",
        "type": "BIGINT"
      },
      {
        "name": "customer_name",
        "type": "VARCHAR"
      },
      {
        "name": "completed_order_count",
        "type": "BIGINT"
      }
    ],
    "extra": [],
    "missing": [],
    "row_count": 120,
    "rows": [
      [
        1,
        "客户-001",
        12
      ],
      [
        13,
        "客户-013",
        10
      ],
      [
        17,
        "客户-017",
        10
      ],
      [
        12,
        "客户-012",
        9
      ],
      [
        53,
        "客户-053",
        9
      ],
      [
        107,
        "客户-107",
        9
      ],
      [
        5,
        "客户-005",
        8
      ],
      [
        21,
        "客户-021",
        8
      ],
      [
        104,
        "客户-104",
        8
      ],
      [
        2,
        "客户-002",
        7
      ],
      [
        4,
        "客户-004",
        7
      ],
      [
        20,
        "客户-020",
        7
      ],
      [
        36,
        "客户-036",
        7
      ],
      [
        48,
        "客户-048",
        7
      ],
      [
        67,
        "客户-067",
        7
      ],
      [
        90,
        "客户-090",
        7
      ],
      [
        109,
        "客户-109",
        7
      ],
      [
        9,
        "客户-009",
        6
      ],
      [
        10,
        "客户-010",
        6
      ],
      [
        15,
        "客户-015",
        6
      ],
      [
        19,
        "客户-019",
        6
      ],
      [
        24,
        "客户-024",
        6
      ],
      [
        29,
        "客户-029",
        6
      ],
      [
        41,
        "客户-041",
        6
      ],
      [
        43,
        "客户-043",
        6
      ],
      [
        64,
        "客户-064",
        6
      ],
      [
        69,
        "客户-069",
        6
      ],
      [
        71,
        "客户-071",
        6
      ],
      [
        80,
        "客户-080",
        6
      ],
      [
        94,
        "客户-094",
        6
      ],
      [
        105,
        "客户-105",
        6
      ],
      [
        26,
        "客户-026",
        5
      ],
      [
        30,
        "客户-030",
        5
      ],
      [
        31,
        "客户-031",
        5
      ],
      [
        44,
        "客户-044",
        5
      ],
      [
        46,
        "客户-046",
        5
      ],
      [
        52,
        "客户-052",
        5
      ],
      [
        57,
        "客户-057",
        5
      ],
      [
        60,
        "客户-060",
        5
      ],
      [
        65,
        "客户-065",
        5
      ],
      [
        74,
        "客户-074",
        5
      ],
      [
        82,
        "客户-082",
        5
      ],
      [
        84,
        "客户-084",
        5
      ],
      [
        97,
        "客户-097",
        5
      ],
      [
        98,
        "客户-098",
        5
      ],
      [
        102,
        "客户-102",
        5
      ],
      [
        106,
        "客户-106",
        5
      ],
      [
        14,
        "客户-014",
        4
      ],
      [
        16,
        "客户-016",
        4
      ],
      [
        22,
        "客户-022",
        4
      ],
      [
        32,
        "客户-032",
        4
      ],
      [
        35,
        "客户-035",
        4
      ],
      [
        38,
        "客户-038",
        4
      ],
      [
        45,
        "客户-045",
        4
      ],
      [
        49,
        "客户-049",
        4
      ],
      [
        50,
        "客户-050",
        4
      ],
      [
        56,
        "客户-056",
        4
      ],
      [
        58,
        "客户-058",
        4
      ],
      [
        59,
        "客户-059",
        4
      ],
      [
        61,
        "客户-061",
        4
      ],
      [
        70,
        "客户-070",
        4
      ],
      [
        72,
        "客户-072",
        4
      ],
      [
        76,
        "客户-076",
        4
      ],
      [
        78,
        "客户-078",
        4
      ],
      [
        79,
        "客户-079",
        4
      ],
      [
        81,
        "客户-081",
        4
      ],
      [
        83,
        "客户-083",
        4
      ],
      [
        85,
        "客户-085",
        4
      ],
      [
        88,
        "客户-088",
        4
      ],
      [
        89,
        "客户-089",
        4
      ],
      [
        92,
        "客户-092",
        4
      ],
      [
        93,
        "客户-093",
        4
      ],
      [
        95,
        "客户-095",
        4
      ],
      [
        100,
        "客户-100",
        4
      ],
      [
        11,
        "客户-011",
        3
      ],
      [
        18,
        "客户-018",
        3
      ],
      [
        25,
        "客户-025",
        3
      ],
      [
        27,
        "客户-027",
        3
      ],
      [
        34,
        "客户-034",
        3
      ],
      [
        37,
        "客户-037",
        3
      ],
      [
        47,
        "客户-047",
        3
      ],
      [
        62,
        "客户-062",
        3
      ],
      [
        66,
        "客户-066",
        3
      ],
      [
        77,
        "客户-077",
        3
      ],
      [
        86,
        "客户-086",
        3
      ],
      [
        87,
        "客户-087",
        3
      ],
      [
        108,
        "客户-108",
        3
      ],
      [
        3,
        "客户-003",
        2
      ],
      [
        6,
        "客户-006",
        2
      ],
      [
        23,
        "客户-023",
        2
      ],
      [
        28,
        "客户-028",
        2
      ],
      [
        39,
        "客户-039",
        2
      ],
      [
        51,
        "客户-051",
        2
      ],
      [
        55,
        "客户-055",
        2
      ],
      [
        63,
        "客户-063",
        2
      ],
      [
        73,
        "客户-073",
        2
      ],
      [
        75,
        "客户-075",
        2
      ],
      [
        96,
        "客户-096",
        2
      ],
      [
        103,
        "客户-103",
        2
      ],
      [
        110,
        "客户-110",
        2
      ],
      [
        7,
        "客户-007",
        1
      ],
      [
        8,
        "客户-008",
        1
      ],
      [
        33,
        "客户-033",
        1
      ],
      [
        40,
        "客户-040",
        1
      ],
      [
        42,
        "客户-042",
        1
      ],
      [
        54,
        "客户-054",
        1
      ],
      [
        68,
        "客户-068",
        1
      ],
      [
        91,
        "客户-091",
        1
      ],
      [
        99,
        "客户-099",
        0
      ],
      [
        101,
        "客户-101",
        0
      ],
      [
        111,
        "客户-111",
        0
      ],
      [
        112,
        "客户-112",
        0
      ],
      [
        113,
        "客户-113",
        0
      ],
      [
        114,
        "客户-114",
        0
      ],
      [
        115,
        "客户-115",
        0
      ],
      [
        116,
        "客户-116",
        0
      ],
      [
        117,
        "客户-117",
        0
      ],
      [
        118,
        "客户-118",
        0
      ],
      [
        119,
        "客户-119",
        0
      ],
      [
        120,
        "客户-120",
        0
      ]
    ]
  },
  "run_id": 16,
  "score": {
    "ast_rules": [
      {
        "details": {
          "actual": 1,
          "required": 1
        },
        "id": "left-join",
        "kind": "join_type",
        "passed": true
      }
    ],
    "column_count": 5,
    "column_names": 5,
    "execution": 10,
    "ordering": 10,
    "protocol": 5,
    "read_only_ast": 5,
    "row_f1": 45,
    "sql_capability": 15,
    "total": 100
  },
  "stable_key": "left_join_zero_orders",
  "started_at": "2026-08-29T22:06:09.837741",
  "status": "completed",
  "suite_content_hash": "5b5d98876ea35114f18ce6dfa48cc9800d88b6baba80d311b2f52552a38b31af",
  "title": "保留零订单客户",
  "token_usage": {
    "completion_tokens": 263,
    "prompt_tokens": 2379,
    "total_tokens": 2642
  },
  "visible_summary": "以客户表为主表 LEFT JOIN 订单表，通过条件去重计数统计已完成订单，保留零订单客户并按指定顺序排序。"
}