Rails 24:00 Orvane Home & Outdoor

Ask

Questions are answered from the dictionary's own SQL, so a number here is the number the operator acts on. When a term has two meanings, both answers come back with how far they overlap.

How many active listings do we have?late shipments last 7 daysnet sales this weekchargebacks in the windowclean returns last 30 daysstock on hand

EUR 2,195,222.67 according to the finance lead (finance v1, this week (from Monday)).

EUR 2,195,222.67

EUR 2,195,222.67 according to the finance lead (finance v1, this week (from Monday))

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.

SQL
WITH sales AS (
  SELECT marketplace_id, sum(total) AS gross FROM orders
  WHERE placed_at >= TIMESTAMP '2026-11-23 00:00:00' AND placed_at < TIMESTAMP '2026-11-28 00:00:00' AND placed_at <= TIMESTAMP '2026-11-28 00:00:00'
    AND (cancelled_at IS NULL OR cancelled_at > TIMESTAMP '2026-11-28 00:00:00')
  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 >= TIMESTAMP '2026-11-23 00:00:00' AND r.requested_at < TIMESTAMP '2026-11-28 00:00:00' AND r.requested_at <= TIMESTAMP '2026-11-28 00:00:00'
  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