品类退货率

{
  "actual_digest": "733702af92a3c65ebb8f41c0ca46a721682381e97b14443eb8f2430a16c9a65e",
  "assumptions": [],
  "attempt": 1,
  "category": "multi_stage_cte",
  "comparison": {
    "abs_tolerance": "0.00005",
    "decimal_scale": 4,
    "duplicate_policy": "multiset",
    "max_rows": 10000,
    "rel_tolerance": "0",
    "row_order_significant": true
  },
  "difficulty": "hard",
  "error_code": null,
  "error_message": null,
  "execution_ms": 240.50150000402937,
  "expected_digest": "1cd2adf76508064abb1c4545713625bdc2f4ecc4e9ddaba90d4cde74b93e3aed",
  "expected_result_preview": {
    "columns": [
      {
        "name": "category",
        "type": "VARCHAR"
      },
      {
        "name": "sold_qty",
        "type": "HUGEINT"
      },
      {
        "name": "returned_qty",
        "type": "HUGEINT"
      },
      {
        "name": "return_rate",
        "type": "DOUBLE"
      }
    ],
    "digest": "1cd2adf76508064abb1c4545713625bdc2f4ecc4e9ddaba90d4cde74b93e3aed",
    "row_count": 6,
    "rows": [
      [
        "品类-5",
        531,
        58,
        "0.1092"
      ],
      [
        "品类-3",
        624,
        52,
        "0.0833"
      ],
      [
        "品类-4",
        592,
        49,
        "0.0828"
      ],
      [
        "品类-6",
        667,
        50,
        "0.075"
      ],
      [
        "品类-2",
        649,
        25,
        "0.0385"
      ],
      [
        "品类-1",
        593,
        17,
        "0.0287"
      ]
    ]
  },
  "finished_at": "2026-08-29T22:08:23.073848",
  "formatted_sql": "WITH sold_by_category AS (\n  SELECT\n    p.category,\n    SUM(oi.quantity) AS sold_qty\n  FROM fact_orders AS o\n  INNER JOIN fact_order_items AS oi\n    ON o.order_id = oi.order_id\n  INNER JOIN dim_products AS p\n    ON oi.product_id = p.product_id\n  WHERE\n    o.status = 'completed'\n  GROUP BY\n    p.category\n), returned_by_category AS (\n  SELECT\n    p.category,\n    SUM(r.return_qty) AS returned_qty\n  FROM fact_returns AS r\n  INNER JOIN fact_order_items AS oi\n    ON r.order_id = oi.order_id AND r.line_no = oi.line_no\n  INNER JOIN fact_orders AS o\n    ON oi.order_id = o.order_id\n  INNER JOIN dim_products AS p\n    ON oi.product_id = p.product_id\n  WHERE\n    o.status = 'completed'\n  GROUP BY\n    p.category\n)\nSELECT\n  s.category,\n  s.sold_qty,\n  COALESCE(r.returned_qty, 0) AS returned_qty,\n  ROUND(CAST(COALESCE(r.returned_qty, 0) AS DECIMAL(38, 10)) / NULLIF(s.sold_qty, 0), 4) AS return_rate\nFROM sold_by_category AS s\nLEFT JOIN returned_by_category AS r\n  ON s.category = r.category\nORDER BY\n  return_rate DESC,\n  s.category ASC",
  "generated_sql": "WITH sold_by_category AS (\n    SELECT\n        p.category,\n        SUM(oi.quantity) AS sold_qty\n    FROM fact_orders AS o\n    INNER JOIN fact_order_items AS oi\n        ON o.order_id = oi.order_id\n    INNER JOIN dim_products AS p\n        ON oi.product_id = p.product_id\n    WHERE o.status = 'completed'\n    GROUP BY p.category\n),\nreturned_by_category AS (\n    SELECT\n        p.category,\n        SUM(r.return_qty) AS returned_qty\n    FROM fact_returns AS r\n    INNER JOIN fact_order_items AS oi\n        ON r.order_id = oi.order_id\n       AND r.line_no = oi.line_no\n    INNER JOIN fact_orders AS o\n        ON oi.order_id = o.order_id\n    INNER JOIN dim_products AS p\n        ON oi.product_id = p.product_id\n    WHERE o.status = 'completed'\n    GROUP BY p.category\n)\nSELECT\n    s.category,\n    s.sold_qty,\n    COALESCE(r.returned_qty, 0) AS returned_qty,\n    ROUND(\n        COALESCE(r.returned_qty, 0)::DECIMAL(38, 10)\n        / NULLIF(s.sold_qty, 0),\n        4\n    ) AS return_rate\nFROM sold_by_category AS s\nLEFT JOIN returned_by_category AS r\n    ON s.category = r.category\nORDER BY return_rate DESC, s.category ASC",
  "generation_ms": null,
  "id": 119,
  "model_name": "GPT 当前会话桥接（流程验收）",
  "model_run_id": 19,
  "plan": {
    "filters": [
      "售出数量仅包含 fact_orders.status = 'completed'。",
      "退货数量仅包含 fact_orders.status = 'completed' 对应订单行。"
    ],
    "grain": "每行一个已完成订单商品品类。",
    "joins": [
      "售出聚合：fact_orders 按 order_id 连接 fact_order_items，再按 product_id 连接 dim_products。",
      "退货聚合：fact_returns 按 (order_id, line_no) 连接 fact_order_items，再连接 fact_orders 和 dim_products。",
      "最终以售出品类为主表，按 category 左连接退货聚合。"
    ],
    "metrics": [
      "sold_qty = SUM(fact_order_items.quantity)。",
      "returned_qty = SUM(fact_returns.return_qty)，无退货填 0。",
      "return_rate = ROUND(returned_qty / sold_qty, 4)。"
    ],
    "risks": [
      "售出与退货必须先独立聚合，避免多条退货记录放大售出数量。",
      "使用 NULLIF 防止 sold_qty 为 0 时除零。",
      "退货品类依赖订单行商品当前映射。"
    ],
    "sources": [
      "fact_orders",
      "fact_order_items",
      "fact_returns",
      "dim_products"
    ],
    "steps": [
      "在 sold_by_category CTE 按品类聚合已完成订单售出数量。",
      "在 returned_by_category CTE 按品类独立聚合已完成订单退货数量。",
      "连接两个品类级聚合并计算退货率。",
      "按 return_rate 降序、category 升序排序。"
    ]
  },
  "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先分别聚合已完成订单的售出数量和退货数量，再计算品类退货率；输出 category、sold_qty、returned_qty、return_rate，比率四舍五入 4 位，按 return_rate 降序、category 升序；必须至少两个 CTE 且不得明细直接 JOIN 造成 fan-out。\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": "先分别聚合已完成订单的售出数量和退货数量，再计算品类退货率；输出 category、sold_qty、returned_qty、return_rate，比率四舍五入 4 位，按 return_rate 降序、category 升序；必须至少两个 CTE 且不得明细直接 JOIN 造成 fan-out。",
  "radar_dimension": "数据开发",
  "raw_output": "{\"plan\":{\"grain\":\"每行一个已完成订单商品品类。\",\"sources\":[\"fact_orders\",\"fact_order_items\",\"fact_returns\",\"dim_products\"],\"joins\":[\"售出聚合：fact_orders 按 order_id 连接 fact_order_items，再按 product_id 连接 dim_products。\",\"退货聚合：fact_returns 按 (order_id, line_no) 连接 fact_order_items，再连接 fact_orders 和 dim_products。\",\"最终以售出品类为主表，按 category 左连接退货聚合。\"],\"filters\":[\"售出数量仅包含 fact_orders.status = 'completed'。\",\"退货数量仅包含 fact_orders.status = 'completed' 对应订单行。\"],\"metrics\":[\"sold_qty = SUM(fact_order_items.quantity)。\",\"returned_qty = SUM(fact_returns.return_qty)，无退货填 0。\",\"return_rate = ROUND(returned_qty / sold_qty, 4)。\"],\"steps\":[\"在 sold_by_category CTE 按品类聚合已完成订单售出数量。\",\"在 returned_by_category CTE 按品类独立聚合已完成订单退货数量。\",\"连接两个品类级聚合并计算退货率。\",\"按 return_rate 降序、category 升序排序。\"],\"risks\":[\"售出与退货必须先独立聚合，避免多条退货记录放大售出数量。\",\"使用 NULLIF 防止 sold_qty 为 0 时除零。\",\"退货品类依赖订单行商品当前映射。\"]},\"sql\":\"WITH sold_by_category AS (\\n    SELECT\\n        p.category,\\n        SUM(oi.quantity) AS sold_qty\\n    FROM fact_orders AS o\\n    INNER JOIN fact_order_items AS oi\\n        ON o.order_id = oi.order_id\\n    INNER JOIN dim_products AS p\\n        ON oi.product_id = p.product_id\\n    WHERE o.status = 'completed'\\n    GROUP BY p.category\\n),\\nreturned_by_category AS (\\n    SELECT\\n        p.category,\\n        SUM(r.return_qty) AS returned_qty\\n    FROM fact_returns AS r\\n    INNER JOIN fact_order_items AS oi\\n        ON r.order_id = oi.order_id\\n       AND r.line_no = oi.line_no\\n    INNER JOIN fact_orders AS o\\n        ON oi.order_id = o.order_id\\n    INNER JOIN dim_products AS p\\n        ON oi.product_id = p.product_id\\n    WHERE o.status = 'completed'\\n    GROUP BY p.category\\n)\\nSELECT\\n    s.category,\\n    s.sold_qty,\\n    COALESCE(r.returned_qty, 0) AS returned_qty,\\n    ROUND(\\n        COALESCE(r.returned_qty, 0)::DECIMAL(38, 10)\\n        / NULLIF(s.sold_qty, 0),\\n        4\\n    ) AS return_rate\\nFROM sold_by_category AS s\\nLEFT JOIN returned_by_category AS r\\n    ON s.category = r.category\\nORDER BY return_rate DESC, s.category ASC\",\"summary\":\"分别在品类粒度聚合已完成订单的售出数量与退货数量，再连接聚合结果计算四舍五入至 4 位的退货率，避免明细连接导致 fan-out。\",\"assumptions\":[]}",
  "reference_sql": "WITH sold AS (SELECT p.category, SUM(i.quantity) AS sold_qty FROM fact_order_items i JOIN fact_orders o ON o.order_id = i.order_id JOIN dim_products p ON p.product_id = i.product_id WHERE o.status = 'completed' GROUP BY p.category), returned AS (SELECT p.category, SUM(r.return_qty) AS returned_qty FROM fact_returns r JOIN fact_order_items i ON i.order_id = r.order_id AND i.line_no = r.line_no JOIN fact_orders o ON o.order_id = i.order_id JOIN dim_products p ON p.product_id = i.product_id WHERE o.status = 'completed' GROUP BY p.category) SELECT s.category, s.sold_qty, COALESCE(r.returned_qty, 0) AS returned_qty, ROUND(COALESCE(r.returned_qty, 0)::DECIMAL / NULLIF(s.sold_qty, 0), 4) AS return_rate FROM sold s LEFT JOIN returned r ON r.category = s.category ORDER BY return_rate DESC, s.category ASC",
  "requested_model_id": "gpt-session-bridge",
  "required_ast": [
    {
      "id": "cte-2",
      "kind": "cte_count",
      "min": 2
    },
    {
      "group_keys": [
        "category"
      ],
      "id": "preaggregate",
      "kind": "separate_measure_preaggregation",
      "left_cte": "sold",
      "left_measure_column": "quantity",
      "left_measure_table": "fact_order_items",
      "right_cte": "returned",
      "right_measure_column": "return_qty",
      "right_measure_table": "fact_returns"
    }
  ],
  "resolved_model_id": "gpt-session-bridge",
  "result_preview": {
    "columns": [
      {
        "name": "category",
        "type": "VARCHAR"
      },
      {
        "name": "sold_qty",
        "type": "HUGEINT"
      },
      {
        "name": "returned_qty",
        "type": "HUGEINT"
      },
      {
        "name": "return_rate",
        "type": "DOUBLE"
      }
    ],
    "extra": [],
    "missing": [],
    "row_count": 6,
    "rows": [
      [
        "品类-5",
        531,
        58,
        0.1092
      ],
      [
        "品类-3",
        624,
        52,
        0.0833
      ],
      [
        "品类-4",
        592,
        49,
        0.0828
      ],
      [
        "品类-6",
        667,
        50,
        0.075
      ],
      [
        "品类-2",
        649,
        25,
        0.0385
      ],
      [
        "品类-1",
        593,
        17,
        0.0287
      ]
    ]
  },
  "run_id": 16,
  "score": {
    "ast_rules": [
      {
        "details": {
          "actual": 2,
          "required": 2
        },
        "id": "cte-2",
        "kind": "cte_count",
        "passed": true
      },
      {
        "details": {},
        "id": "preaggregate",
        "kind": "separate_measure_preaggregation",
        "passed": false
      }
    ],
    "column_count": 5,
    "column_names": 5,
    "execution": 10,
    "ordering": 10,
    "protocol": 5,
    "read_only_ast": 5,
    "row_f1": 45,
    "sql_capability": 7.5,
    "total": 92.5
  },
  "stable_key": "category_return_rate",
  "started_at": "2026-08-29T22:08:03.670568",
  "status": "completed",
  "suite_content_hash": "5b5d98876ea35114f18ce6dfa48cc9800d88b6baba80d311b2f52552a38b31af",
  "title": "品类退货率",
  "token_usage": {
    "completion_tokens": 524,
    "prompt_tokens": 2383,
    "total_tokens": 2907
  },
  "visible_summary": "分别在品类粒度聚合已完成订单的售出数量与退货数量，再连接聚合结果计算四舍五入至 4 位的退货率，避免明细连接导致 fan-out。"
}