Definitions

The definitions the agent and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.

GMV (gross merchandise value)

gmv unit: AED no canonical variant: Ask returns all 2

The value of orders through the platform, in AED. Finance reports net of VAT on delivered orders only; Ops reports what kitchens handled, VAT included. The two differ by VAT plus cancellations.

financeCFO · finance v1

Subtotal excluding VAT of delivered orders, by delivery time, converted to AED with a fixed table of illustrative rates. Cancelled and rejected orders never count.

latest change: First version.

pass value_betweenobserved 16570372.8, expected between 1000000.0 and 200000000.0
pass india_marketsobserved 2, expected between 2.0 and 2.0

SQL, rendered for now

WITH fx(currency, aed_per_unit) AS (
  VALUES ('AED', 1.0), ('SAR', 0.97933), ('QAR', 1.00893), ('KWD', 11.9512), ('BHD', 9.76729),
         ('GBP', 4.88443), ('EUR', 4.27118), ('USD', 3.6725), ('INR', 0.0437)
)
SELECT mk.code AS market, mk.currency, count(*) AS orders,
       round(sum(o.subtotal), 2) AS gmv_local,
       round(sum(o.subtotal * fx.aed_per_unit), 2) AS gmv_aed
FROM orders o
JOIN branches b ON b.id = o.branch_id
JOIN markets mk ON mk.id = b.market_id
JOIN fx ON fx.currency = mk.currency
WHERE o.status = 'delivered'
  AND o.delivered_at >= TIMESTAMP 'Fri 23:00:00 (7 days ago)' AND o.delivered_at < TIMESTAMP 'Fri 23:00:00' AND o.delivered_at <= TIMESTAMP 'Fri 23:00:00'
GROUP BY mk.code, mk.currency
ORDER BY gmv_aed DESC

opsCOO · ops v1

Total including VAT of every order a branch accepted in the period, whatever happened after (rejections excluded, cancellations after acceptance included), converted to AED with the same rates.

latest change: First version.

pass value_betweenobserved 17933359.38, expected between 1000000.0 and 250000000.0
pass india_marketsobserved 2, expected between 2.0 and 2.0

SQL, rendered for now

WITH fx(currency, aed_per_unit) AS (
  VALUES ('AED', 1.0), ('SAR', 0.97933), ('QAR', 1.00893), ('KWD', 11.9512), ('BHD', 9.76729),
         ('GBP', 4.88443), ('EUR', 4.27118), ('USD', 3.6725), ('INR', 0.0437)
)
SELECT mk.code AS market, mk.currency, count(*) AS orders,
       round(sum(o.total), 2) AS gmv_local,
       round(sum(o.total * fx.aed_per_unit), 2) AS gmv_aed
FROM orders o
JOIN branches b ON b.id = o.branch_id
JOIN markets mk ON mk.id = b.market_id
JOIN fx ON fx.currency = mk.currency
WHERE o.accepted_at IS NOT NULL AND o.status <> 'rejected'
  AND o.accepted_at >= TIMESTAMP 'Fri 23:00:00 (7 days ago)' AND o.accepted_at < TIMESTAMP 'Fri 23:00:00' AND o.accepted_at <= TIMESTAMP 'Fri 23:00:00'
GROUP BY mk.code, mk.currency
ORDER BY gmv_aed DESC

History

append-only; nothing is edited in place

definitionCFO finance v1 First version.
definitionCOO ops v1 First version.