Rails 24:00 Orvane Home & Outdoor

← Definitions

Net sales net_sales

Order value of orders placed in the period and not cancelled, less refunds paid for returns in it.

Parameters: none

Test results

meaningtestpassedobservedexpected
financevalue_betweenpassed2838220.77between 100000.0 and 5000000.0

finance · v1 · finance lead

Sum of order totals placed in the period and not cancelled as of now, minus refunds for returns requested in the period, per sales channel.

Changed 2026-10-12: Agreed with the owner.

WITH sales AS (
  SELECT marketplace_id, sum(total) AS gross FROM orders
  WHERE placed_at >= {period_start} AND placed_at < {period_end} AND placed_at <= {now}
    AND (cancelled_at IS NULL OR cancelled_at > {now})
  GROUP BY marketplace_id
),
refunds AS (
  SELECT o.marketplace_id, sum(r.refund_amount) AS refunded FROM returns r JOIN orders o ON o.id = r.order_id
  WHERE r.requested_at >= {period_start} AND r.requested_at < {period_end} AND r.requested_at <= {now}
  GROUP BY o.marketplace_id
)
SELECT m.name AS marketplace, round(s.gross, 2) AS gross, round(coalesce(f.refunded, 0), 2) AS refunded,
       round(s.gross - coalesce(f.refunded, 0), 2) AS net_sales
FROM sales s JOIN marketplaces m ON m.id = s.marketplace_id LEFT JOIN refunds f ON f.marketplace_id = s.marketplace_id
ORDER BY m.id

Tests: value_between