运行 #13 评测报告

A1 · 完成 · 100.00

查询 2026-01-01 起的已完成订单及客户名称，按订单金额降序、订单 ID 升序排列。

结果正确（页面派生）

SELECT
  o.order_id,
  c.customer_name,
  o.order_date,
  o.total_amount
FROM fact_orders AS o
JOIN dim_customers AS c
  ON o.customer_id = c.customer_id
WHERE
  o.status = 'completed' AND o.order_date >= CAST('2026-01-01' AS DATE)
ORDER BY
  o.total_amount DESC,
  o.order_id ASC

A1 · 完成 · 100.00

按商品品类汇总已完成订单的去重订单数和订单行净销售额，并按净销售额降序、品类升序排列。

结果正确（页面派生）

SELECT
  p.category AS category,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
FROM fact_orders AS o
JOIN fact_order_items AS oi
  ON oi.order_id = o.order_id
JOIN dim_products AS p
  ON p.product_id = oi.product_id
WHERE
  o.status = 'completed'
GROUP BY
  p.category
ORDER BY
  net_revenue DESC,
  category ASC

A1 · 完成 · 100.00

列出所有客户及其已完成订单数，通过 LEFT JOIN 保留零订单客户，并按完成订单数降序、客户 ID 升序排列。

结果正确（页面派生）

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(DISTINCT o.order_id) AS completed_order_count
FROM dim_customers AS c
LEFT JOIN fact_orders AS o
  ON c.customer_id = o.customer_id AND o.status = 'completed'
GROUP BY
  c.customer_id,
  c.customer_name
ORDER BY
  completed_order_count DESC,
  c.customer_id ASC

A1 · 完成 · 100.00

返回金额高于其客户分群已完成订单平均金额的已完成订单，并按订单金额降序、订单 ID 升序排列。

结果正确（页面派生）

SELECT
  o.order_id,
  o.customer_id,
  c.segment,
  o.total_amount,
  (
    SELECT
      AVG(o2.total_amount)
    FROM fact_orders AS o2
    JOIN dim_customers AS c2
      ON o2.customer_id = c2.customer_id
    WHERE
      o2.status = 'completed' AND c2.segment = c.segment
  ) AS segment_avg_amount
FROM fact_orders AS o
JOIN dim_customers AS c
  ON o.customer_id = c.customer_id
WHERE
  o.status = 'completed'
  AND o.total_amount > (
    SELECT
      AVG(o3.total_amount)
    FROM fact_orders AS o3
    JOIN dim_customers AS c3
      ON o3.customer_id = c3.customer_id
    WHERE
      o3.status = 'completed' AND c3.segment = c.segment
  )
ORDER BY
  o.total_amount DESC,
  o.order_id ASC

A1 · 完成 · 100.00

返回从未有已完成订单的客户，并按 customer_id 升序排列。

结果正确（页面派生）

SELECT
  c.customer_id,
  c.customer_name
FROM dim_customers AS c
WHERE
  NOT EXISTS(
    SELECT
      1
    FROM fact_orders AS o
    WHERE
      o.customer_id = c.customer_id AND o.status = 'completed'
  )
ORDER BY
  c.customer_id ASC

A1 · 完成 · 100.00

统计完成订单中各商品的净销售额，并用 ROW_NUMBER 选出每个品类净销售额最高的恰好前三个商品；同额时按 product_id 升序排名。

结果正确（页面派生）

WITH product_revenue AS (
  SELECT
    p.category,
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
  FROM fact_order_items AS oi
  JOIN fact_orders AS o
    ON oi.order_id = o.order_id
  JOIN dim_products AS p
    ON oi.product_id = p.product_id
  WHERE
    o.status = 'completed'
  GROUP BY
    p.category,
    p.product_id,
    p.product_name
), ranked_products AS (
  SELECT
    category,
    product_id,
    product_name,
    net_revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY net_revenue DESC, product_id ASC) AS rank_no
  FROM product_revenue
)
SELECT
  category,
  product_id,
  product_name,
  net_revenue,
  rank_no
FROM ranked_products
WHERE
  rank_no <= 3
ORDER BY
  category ASC,
  rank_no ASC

A1 · 已取消 · —

没有可见摘要。

结果正确性未知

A1 · 已取消 · —

没有可见摘要。

结果正确性未知

A1 · 已取消 · —

没有可见摘要。

结果正确性未知

A1 · 已取消 · —

没有可见摘要。

结果正确性未知

A1 · 已取消 · —

没有可见摘要。

结果正确性未知

A1 · 已取消 · —

没有可见摘要。

结果正确性未知