运行 #18

Luna

连接完成订单与客户维度，筛选 2026-01-01 起的完成订单，并按订单金额降序、订单编号升序输出所需字段。

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

Sol

筛选 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

Luna

连接订单头、订单行和商品维度，筛选完成订单后按品类统计 distinct 订单数与订单行净销售额，并按指定顺序排序。

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

Sol

连接订单、订单行和商品三表，筛选已完成订单后按品类统计 distinct 订单数及订单行净销售额，并按指定顺序排序。

SELECT
  p.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 o.order_id = oi.order_id
JOIN dim_products AS p
  ON oi.product_id = p.product_id
WHERE
  o.status = 'completed'
GROUP BY
  p.category
ORDER BY
  net_revenue DESC,
  p.category ASC

Luna

以客户为粒度，从客户表 LEFT JOIN 订单表，将完成状态条件放入去重计数中，保留零完成订单客户并按要求排序。

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

Sol

以客户表为主表左连接已完成订单，按客户聚合计数，从而保留完成订单数为零的客户。

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS completed_order_count
FROM dim_customers AS c
LEFT JOIN fact_orders AS 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

Luna

连接已完成订单与客户分群，通过引用外层 c.segment 的相关子查询计算同分群已完成订单平均金额，筛选高于该均值的订单并排序。

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(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
  )
ORDER BY
  o.total_amount DESC,
  o.order_id ASC

Sol

连接已完成订单与客户，并通过引用外层客户分群的相关子查询计算同分群已完成订单平均金额，再筛选高于该平均值的订单并按指定顺序排序。

SELECT
  order_id,
  customer_id,
  segment,
  total_amount,
  segment_avg_amount
FROM (
  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'
) AS completed_orders
WHERE
  total_amount > segment_avg_amount
ORDER BY
  total_amount DESC,
  order_id ASC

Luna

从客户主数据中筛选不存在已完成订单的客户，并按 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

Sol

从客户表出发，使用关联 NOT EXISTS 子查询排除任何存在已完成订单的客户，并按 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

Luna

汇总完成订单的商品净销售额，按品类使用 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_orders AS o
  INNER JOIN fact_order_items AS oi
    ON oi.order_id = o.order_id
  INNER JOIN dim_products AS p
    ON p.product_id = oi.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

Sol

汇总完成订单中各商品的净销售额，再按品类使用 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

Luna

按 UTC 月份汇总 2025 年完成订单的订单行净销售额，并使用按 month 升序的 SUM 窗口计算累计收入。

WITH monthly AS (
  SELECT
    STRFTIME(o.order_date, '%Y-%m') AS month,
    SUM(i.quantity * i.unit_price - i.discount_amount) AS monthly_revenue
  FROM fact_orders AS o
  JOIN fact_order_items AS i
    ON i.order_id = o.order_id
  WHERE
    o.status = 'completed'
    AND o.order_date >= CAST('2025-01-01' AS DATE)
    AND o.order_date < CAST('2026-01-01' AS DATE)
  GROUP BY
    1
)
SELECT
  month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
FROM monthly
ORDER BY
  month

Sol

汇总 2025 年已完成订单的月度订单行净销售额，再用按月份排序的 SUM 窗口计算累计收入。

WITH monthly AS (
  SELECT
    STRFTIME(fact_orders.order_date, '%Y-%m') AS month,
    SUM(
      fact_order_items.quantity * fact_order_items.unit_price - fact_order_items.discount_amount
    ) AS monthly_revenue
  FROM fact_orders
  JOIN fact_order_items
    ON fact_order_items.order_id = fact_orders.order_id
  WHERE
    fact_orders.status = 'completed'
    AND fact_orders.order_date >= CAST('2025-01-01' AS DATE)
    AND fact_orders.order_date < CAST('2026-01-01' AS DATE)
  GROUP BY
    1
)
SELECT
  month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
FROM monthly
ORDER BY
  month ASC

Luna

按月汇总 2025 年完成订单的订单行净销售额，并使用 LAG 计算环比百分比；首月及上月收入为零时返回 NULL。

WITH monthly_revenue AS (
  SELECT
    STRFTIME(o.order_date, '%Y-%m') AS month,
    SUM(i.quantity * i.unit_price - i.discount_amount) AS monthly_revenue
  FROM fact_orders AS o
  JOIN fact_order_items AS i
    ON i.order_id = o.order_id
  WHERE
    o.status = 'completed'
    AND o.order_date >= CAST('2025-01-01' AS DATE)
    AND o.order_date < CAST('2026-01-01' AS DATE)
  GROUP BY
    STRFTIME(o.order_date, '%Y-%m')
), with_previous AS (
  SELECT
    month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY month) AS previous_revenue
  FROM monthly_revenue
)
SELECT
  month,
  monthly_revenue,
  ROUND((
    monthly_revenue - previous_revenue
  ) / NULLIF(previous_revenue, 0) * 100, 2) AS mom_pct
FROM with_previous
ORDER BY
  month ASC

Sol

生成 2025 年完整月份序列，汇总完成订单的订单行净销售额，补零后使用 LAG 计算月度环比。

Luna

先按客户汇总完成订单的订单行净销售额，再计算这些客户的平均累计消费，筛选高于平均值的客户并按指定顺序排序。

SELECT
  customer_id,
  customer_name,
  total_spend
FROM (
  SELECT
    customer_id,
    customer_name,
    total_spend,
    AVG(total_spend) OVER () AS avg_total_spend
  FROM (
    SELECT
      c.customer_id,
      c.customer_name,
      SUM(i.quantity * i.unit_price - i.discount_amount) AS total_spend
    FROM fact_orders AS o
    JOIN fact_order_items AS i
      ON i.order_id = o.order_id
    JOIN dim_customers AS c
      ON c.customer_id = o.customer_id
    WHERE
      o.status = 'completed'
    GROUP BY
      c.customer_id,
      c.customer_name
  ) AS customer_spend
) AS scored_customers
WHERE
  total_spend > avg_total_spend
ORDER BY
  total_spend DESC,
  customer_id ASC

Sol

先按客户汇总已完成订单行的净销售额，再与同一客户集合的平均累计消费比较，连接客户名称后输出并排序。

SELECT
  customer_spend.customer_id,
  customers.customer_name,
  customer_spend.total_spend
FROM (
  SELECT
    orders.customer_id,
    SUM(items.quantity * items.unit_price - items.discount_amount) AS total_spend
  FROM fact_orders AS orders
  INNER JOIN fact_order_items AS items
    ON orders.order_id = items.order_id
  WHERE
    orders.status = 'completed'
  GROUP BY
    orders.customer_id
) AS customer_spend
INNER JOIN dim_customers AS customers
  ON customer_spend.customer_id = customers.customer_id
WHERE
  customer_spend.total_spend > (
    SELECT
      AVG(spend_by_customer.total_spend)
    FROM (
      SELECT
        orders.customer_id,
        SUM(items.quantity * items.unit_price - items.discount_amount) AS total_spend
      FROM fact_orders AS orders
      INNER JOIN fact_order_items AS items
        ON orders.order_id = items.order_id
      WHERE
        orders.status = 'completed'
      GROUP BY
        orders.customer_id
    ) AS spend_by_customer
  )
ORDER BY
  customer_spend.total_spend DESC,
  customer_spend.customer_id ASC

Luna

按订单关联渠道类型，对支付记录按状态进行三组 SUM(CASE WHEN ...) 条件聚合，并按渠道类型升序输出。

SELECT
  dim_channels.channel_type,
  SUM(CASE WHEN fact_payments.status = 'paid' THEN fact_payments.amount ELSE 0 END) AS paid_amount,
  SUM(CASE WHEN fact_payments.status = 'refunded' THEN fact_payments.amount ELSE 0 END) AS refunded_amount,
  SUM(CASE WHEN fact_payments.status = 'failed' THEN fact_payments.amount ELSE 0 END) AS failed_amount
FROM fact_payments
JOIN fact_orders
  ON fact_payments.order_id = fact_orders.order_id
JOIN dim_channels
  ON fact_orders.channel_id = dim_channels.channel_id
GROUP BY
  dim_channels.channel_type
ORDER BY
  dim_channels.channel_type ASC

Sol

连接渠道、订单和支付表，按渠道类型分组，并用三组条件聚合分别汇总 paid、refunded 和 failed 状态的支付金额。

SELECT
  c.channel_type,
  SUM(CASE WHEN p.status = 'paid' THEN p.amount ELSE 0 END) AS paid_amount,
  SUM(CASE WHEN p.status = 'refunded' THEN p.amount ELSE 0 END) AS refunded_amount,
  SUM(CASE WHEN p.status = 'failed' THEN p.amount ELSE 0 END) AS failed_amount
FROM dim_channels AS c
JOIN fact_orders AS o
  ON o.channel_id = c.channel_id
JOIN fact_payments AS p
  ON p.order_id = o.order_id
GROUP BY
  c.channel_type
ORDER BY
  c.channel_type ASC

Luna

使用独立的完成订单售出量和退货量聚合，并先按订单行汇总退货，避免 fan-out；随后按品类计算并排序退货率。

WITH sold_by_category AS (
  SELECT
    p.category,
    SUM(oi.quantity) AS sold_qty
  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
), returns_by_line AS (
  SELECT
    r.order_id,
    r.line_no,
    SUM(r.return_qty) AS returned_qty
  FROM fact_returns AS r
  GROUP BY
    r.order_id,
    r.line_no
), returned_by_category AS (
  SELECT
    p.category,
    SUM(COALESCE(rl.returned_qty, 0)) AS returned_qty
  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
  LEFT JOIN returns_by_line AS rl
    ON rl.order_id = oi.order_id AND rl.line_no = oi.line_no
  WHERE
    o.status = 'completed'
  GROUP BY
    p.category
)
SELECT
  s.category,
  s.sold_qty,
  COALESCE(r.returned_qty, 0) AS returned_qty,
  ROUND(CAST(COALESCE(r.returned_qty, 0) AS DOUBLE) / NULLIF(s.sold_qty, 0), 4) AS return_rate
FROM sold_by_category AS s
LEFT JOIN returned_by_category AS r
  ON r.category = s.category
ORDER BY
  return_rate DESC,
  s.category ASC

Sol

分别在品类粒度汇总完成订单的售出量和退货量，再连接汇总结果计算退货率，从而避免明细连接引起的 fan-out。

WITH sold_by_category AS (
  SELECT
    p.category,
    SUM(oi.quantity) AS sold_qty
  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
), returned_by_category AS (
  SELECT
    p.category,
    SUM(r.return_qty) AS returned_qty
  FROM fact_returns AS r
  JOIN fact_order_items AS oi
    ON r.order_id = oi.order_id AND r.line_no = oi.line_no
  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
)
SELECT
  s.category,
  s.sold_qty,
  COALESCE(r.returned_qty, 0) AS returned_qty,
  ROUND(CAST(COALESCE(r.returned_qty, 0) AS DECIMAL(18, 3)) / NULLIF(s.sold_qty, 0), 4) AS return_rate
FROM sold_by_category AS s
LEFT JOIN returned_by_category AS r
  ON s.category = r.category
ORDER BY
  return_rate DESC,
  s.category ASC

Luna

筛选成功支付记录，使用 ROW_NUMBER 按订单分组并按支付时间、payment_id 倒序选取最新一笔，最终按 order_id 升序输出。

WITH ranked_paid_payments AS (
  SELECT
    order_id,
    payment_id,
    paid_at,
    amount,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC, payment_id DESC) AS rn
  FROM fact_payments
  WHERE
    status = 'paid'
)
SELECT
  order_id,
  payment_id,
  paid_at,
  amount
FROM ranked_paid_payments
WHERE
  rn = 1
ORDER BY
  order_id ASC

Sol

筛选成功支付后，使用 ROW_NUMBER 按订单分区并按支付时间和 payment_id 降序排名，取每个订单的第一条记录。

SELECT
  order_id,
  payment_id,
  paid_at,
  amount
FROM (
  SELECT
    order_id,
    payment_id,
    paid_at,
    amount,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC, payment_id DESC) AS row_num
  FROM fact_payments
  WHERE
    status = 'paid'
) AS ranked_payments
WHERE
  row_num = 1
ORDER BY
  order_id ASC

Luna

从客户维表筛选 city 为 NULL 的客户，用 COALESCE 统一显示为“未知”，并按 customer_id 升序输出。

SELECT
  customer_id,
  customer_name,
  COALESCE(city, '未知') AS city_label
FROM dim_customers
WHERE
  city IS NULL
ORDER BY
  customer_id ASC

Sol

从客户表筛选 city 为 NULL 的记录，将缺失城市标记为“未知”，并按 customer_id 升序输出。

SELECT
  customer_id,
  customer_name,
  COALESCE(city, '未知') AS city_label
FROM dim_customers
WHERE
  city IS NULL
ORDER BY
  customer_id ASC

Luna

筛选 2026 年已完成订单，使用 CASE 按 total_amount 分为 high、medium、low，并按金额降序及订单号升序输出。

SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount >= 2000
    THEN 'high'
    WHEN total_amount >= 1000 AND total_amount < 2000
    THEN 'medium'
    ELSE 'low'
  END AS amount_band
FROM fact_orders
WHERE
  order_date >= CAST('2026-01-01' AS DATE)
  AND order_date < CAST('2027-01-01' AS DATE)
  AND status = 'completed'
ORDER BY
  total_amount DESC,
  order_id ASC

Sol

筛选 2026 年已完成订单，使用 CASE 按订单头金额划分 high、medium、low，并按金额降序及订单编号升序输出。

SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount >= 2000
    THEN 'high'
    WHEN total_amount >= 1000
    THEN 'medium'
    ELSE 'low'
  END AS amount_band
FROM fact_orders
WHERE
  status = 'completed'
  AND order_date >= CAST('2026-01-01' AS DATE)
  AND order_date < CAST('2027-01-01' AS DATE)
ORDER BY
  total_amount DESC,
  order_id ASC

Luna

以渠道维表为主表左连接完成订单聚合，保留所有渠道；完成订单数按订单去重计数，净销售额按完成订单商品行净额汇总，空值填 0 后按渠道 ID 升序。

SELECT
  c.channel_id,
  c.channel_name,
  COALESCE(m.completed_order_count, 0) AS completed_order_count,
  COALESCE(m.net_revenue, 0.00) AS net_revenue
FROM dim_channels AS c
LEFT JOIN (
  SELECT
    o.channel_id,
    COUNT(DISTINCT o.order_id) AS completed_order_count,
    SUM(i.quantity * i.unit_price - i.discount_amount) AS net_revenue
  FROM fact_orders AS o
  LEFT JOIN fact_order_items AS i
    ON i.order_id = o.order_id
  WHERE
    o.status = 'completed'
  GROUP BY
    o.channel_id
) AS m
  ON m.channel_id = c.channel_id
ORDER BY
  c.channel_id ASC

Sol

先按渠道汇总完成订单数和订单行净销售额，再左连接全部渠道，并将缺失指标补为 0。

SELECT
  c.channel_id,
  c.channel_name,
  COALESCE(a.completed_order_count, 0) AS completed_order_count,
  COALESCE(a.net_revenue, CAST(0 AS DECIMAL(38, 2))) AS net_revenue
FROM dim_channels AS c
LEFT JOIN (
  SELECT
    o.channel_id,
    COUNT(DISTINCT o.order_id) AS completed_order_count,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
  FROM fact_orders AS o
  INNER JOIN fact_order_items AS oi
    ON oi.order_id = o.order_id
  WHERE
    o.status = 'completed'
  GROUP BY
    o.channel_id
) AS a
  ON a.channel_id = c.channel_id
ORDER BY
  c.channel_id ASC

Luna

先分别按 channel_type 聚合完成订单订单行指标与支付状态金额，再汇总连接并按 channel_type 升序输出，避免订单行与支付记录连接导致重复计算。

WITH channel_types AS (
  SELECT DISTINCT
    channel_type
  FROM dim_channels
), order_metrics AS (
  SELECT
    c.channel_type,
    COUNT(DISTINCT o.order_id) AS completed_order_count,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
  FROM fact_orders AS o
  JOIN dim_channels AS c
    ON o.channel_id = c.channel_id
  JOIN fact_order_items AS oi
    ON oi.order_id = o.order_id
  WHERE
    o.status = 'completed'
  GROUP BY
    c.channel_type
), payment_metrics AS (
  SELECT
    c.channel_type,
    SUM(CASE WHEN p.status = 'paid' THEN p.amount ELSE 0 END) AS paid_amount,
    SUM(CASE WHEN p.status = 'refunded' THEN p.amount ELSE 0 END) AS refunded_amount,
    SUM(CASE WHEN p.status = 'failed' THEN p.amount ELSE 0 END) AS failed_amount
  FROM fact_payments AS p
  JOIN fact_orders AS o
    ON p.order_id = o.order_id
  JOIN dim_channels AS c
    ON o.channel_id = c.channel_id
  GROUP BY
    c.channel_type
)
SELECT
  ct.channel_type,
  COALESCE(om.completed_order_count, 0) AS completed_order_count,
  COALESCE(om.net_revenue, 0) AS net_revenue,
  COALESCE(pm.paid_amount, 0) AS paid_amount,
  COALESCE(pm.refunded_amount, 0) AS refunded_amount,
  COALESCE(pm.failed_amount, 0) AS failed_amount
FROM channel_types AS ct
LEFT JOIN order_metrics AS om
  ON ct.channel_type = om.channel_type
LEFT JOIN payment_metrics AS pm
  ON ct.channel_type = pm.channel_type
ORDER BY
  ct.channel_type ASC

Sol

分别在订单、订单行和支付粒度聚合指标，再按 channel_type 合并，避免订单行与支付记录相乘导致重复计算。

WITH channel_types AS (
  SELECT
    channel_type
  FROM dim_channels
  GROUP BY
    channel_type
), completed_orders AS (
  SELECT
    c.channel_type,
    COUNT(DISTINCT o.order_id) AS completed_order_count
  FROM fact_orders AS o
  JOIN dim_channels AS c
    ON o.channel_id = c.channel_id
  WHERE
    o.status = 'completed'
  GROUP BY
    c.channel_type
), net_revenue AS (
  SELECT
    c.channel_type,
    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_channels AS c
    ON o.channel_id = c.channel_id
  WHERE
    o.status = 'completed'
  GROUP BY
    c.channel_type
), payment_amounts AS (
  SELECT
    c.channel_type,
    SUM(CASE WHEN p.status = 'paid' THEN p.amount ELSE 0 END) AS paid_amount,
    SUM(CASE WHEN p.status = 'refunded' THEN p.amount ELSE 0 END) AS refunded_amount,
    SUM(CASE WHEN p.status = 'failed' THEN p.amount ELSE 0 END) AS failed_amount
  FROM fact_payments AS p
  JOIN fact_orders AS o
    ON p.order_id = o.order_id
  JOIN dim_channels AS c
    ON o.channel_id = c.channel_id
  GROUP BY
    c.channel_type
)
SELECT
  ct.channel_type,
  COALESCE(co.completed_order_count, 0) AS completed_order_count,
  COALESCE(nr.net_revenue, 0) AS net_revenue,
  COALESCE(pa.paid_amount, 0) AS paid_amount,
  COALESCE(pa.refunded_amount, 0) AS refunded_amount,
  COALESCE(pa.failed_amount, 0) AS failed_amount
FROM channel_types AS ct
LEFT JOIN completed_orders AS co
  ON ct.channel_type = co.channel_type
LEFT JOIN net_revenue AS nr
  ON ct.channel_type = nr.channel_type
LEFT JOIN payment_amounts AS pa
  ON ct.channel_type = pa.channel_type
ORDER BY
  ct.channel_type ASC

Luna

按品类汇总完成订单商品行净销售额，并以全部品类净销售额为分母计算四舍五入至 2 位的百分比，最后按占比降序和品类升序排序。

WITH category_revenue AS (
  SELECT
    p.category,
    COALESCE(SUM(i.quantity * i.unit_price - i.discount_amount), 0) AS net_revenue
  FROM dim_products AS p
  LEFT JOIN fact_order_items AS i
    ON p.product_id = i.product_id
  LEFT JOIN fact_orders AS o
    ON i.order_id = o.order_id AND o.status = 'completed'
  GROUP BY
    p.category
), totals AS (
  SELECT
    SUM(net_revenue) AS total_net_revenue
  FROM category_revenue
)
SELECT
  cr.category,
  cr.net_revenue,
  ROUND(cr.net_revenue * 100.0 / NULLIF(t.total_net_revenue, 0), 2) AS revenue_share_pct
FROM category_revenue AS cr
CROSS JOIN totals AS t
ORDER BY
  revenue_share_pct DESC,
  cr.category ASC

Sol

按商品品类汇总完成订单行的净销售额，再以全部品类净销售额为分母计算百分比占比并四舍五入到2位。

WITH category_revenue AS (
  SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
  FROM fact_order_items AS oi
  INNER JOIN fact_orders AS o
    ON oi.order_id = o.order_id
  INNER JOIN dim_products AS p
    ON oi.product_id = p.product_id
  WHERE
    o.status = 'completed'
  GROUP BY
    p.category
), revenue_shares AS (
  SELECT
    category,
    net_revenue,
    ROUND(100 * net_revenue / NULLIF(SUM(net_revenue) OVER (), 0), 2) AS revenue_share_pct
  FROM category_revenue
)
SELECT
  category,
  net_revenue,
  revenue_share_pct
FROM revenue_shares
ORDER BY
  revenue_share_pct DESC,
  category ASC

Luna

按订单汇总订单行净额，与订单头 total_amount 对账，保留四舍五入到 2 位后存在差异的订单，并按绝对差异降序、订单号升序排列。

WITH order_line_totals AS (
  SELECT
    order_id,
    SUM(quantity * unit_price - discount_amount) AS calculated_total_raw
  FROM fact_order_items
  GROUP BY
    order_id
), reconciliation AS (
  SELECT
    o.order_id,
    ROUND(o.total_amount, 2) AS stored_total,
    ROUND(COALESCE(olt.calculated_total_raw, 0), 2) AS calculated_total,
    ROUND(o.total_amount - COALESCE(olt.calculated_total_raw, 0), 2) AS difference
  FROM fact_orders AS o
  LEFT JOIN order_line_totals AS olt
    ON o.order_id = olt.order_id
)
SELECT
  order_id,
  stored_total,
  calculated_total,
  difference
FROM reconciliation
WHERE
  difference <> 0
ORDER BY
  ABS(difference) DESC,
  order_id ASC

Sol

按订单汇总订单行净额，与订单头金额比较，仅输出非零差异，并按绝对差异降序排列。

WITH reconciled AS (
  SELECT
    o.order_id,
    ROUND(o.total_amount, 2) AS stored_total,
    ROUND(COALESCE(SUM(oi.quantity * oi.unit_price - oi.discount_amount), 0), 2) AS calculated_total
  FROM fact_orders AS o
  LEFT JOIN fact_order_items AS oi
    ON o.order_id = oi.order_id
  GROUP BY
    o.order_id,
    o.total_amount
), differences AS (
  SELECT
    order_id,
    stored_total,
    calculated_total,
    ROUND(stored_total - calculated_total, 2) AS difference
  FROM reconciled
)
SELECT
  order_id,
  stored_total,
  calculated_total,
  difference
FROM differences
WHERE
  difference <> 0
ORDER BY
  ABS(difference) DESC,
  order_id ASC