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
| meaning | test | passed | observed | expected |
|---|---|---|---|---|
| finance | value_between | passed | 2838220.77 | between 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.
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