Definitions

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

Active client

active_client unit: companies no canonical variant: Ask returns all 3

How many merchants count as clients. Finance, Growth and Ops count differently; until one of the three is made canonical, every answer shows all three side by side with their owners.

financeCFO · finance v1

A merchant with at least one invoice for a positive amount issued in the period (default: this month). Credit notes alone do not make a client.

latest change: First version.

pass count_betweenobserved 1637, expected between 1200.0 and 1800.0
pass uniqueobserved {'duplicates': 0}, expected merchant_id unique

SQL, rendered for now

SELECT m.id AS merchant_id, m.name, mk.code AS market, m.plan,
       count(*) AS invoices,
       round(sum(i.amount), 2) AS invoiced_amount,
       any_value(i.currency) AS currency,
       min(i.issued_at) AS first_issued_at
FROM invoices i
JOIN merchants m ON m.id = i.merchant_id
LEFT JOIN markets mk ON mk.id = m.market_id
WHERE i.issued_at >= TIMESTAMP 'Thu 00:00:00 (15 days ago)' AND i.issued_at < TIMESTAMP 'Fri 23:00:00'
  AND i.amount > 0
GROUP BY m.id, m.name, mk.code, m.plan
ORDER BY m.id

growthCGO · growth v1

A merchant with a signed contract that has not churned, as of the time asked. Includes merchants still being onboarded.

latest change: First version.

pass count_betweenobserved 1776, expected between 1600.0 and 1850.0
pass uniqueobserved {'duplicates': 0}, expected merchant_id unique

SQL, rendered for now

SELECT m.id AS merchant_id, m.name, mk.code AS market, m.segment, m.plan,
       m.contract_signed_at, m.onboarded_at, m.first_order_at
FROM merchants m
LEFT JOIN markets mk ON mk.id = m.market_id
WHERE m.contract_signed_at <= TIMESTAMP 'Fri 23:00:00'
  AND (m.churned_at IS NULL OR m.churned_at > TIMESTAMP 'Fri 23:00:00')
ORDER BY m.id

opsCOO · ops v1

A merchant whose first order has arrived and that had at least one order in the period (default: the last 30 days), counted from the daily aggregates plus the order-level data.

latest change: First version.

pass count_betweenobserved 1631, expected between 1300.0 and 1800.0
pass uniqueobserved {'duplicates': 0}, expected merchant_id unique

SQL, rendered for now

WITH daily AS (
  SELECT b.merchant_id, sum(d.orders) AS orders, max(d.day) AS last_day
  FROM orders_daily d
  JOIN branches b ON b.id = d.branch_id
  WHERE d.day >= CAST(TIMESTAMP 'Wed 23:00:00 (30 days ago)' AS DATE) AND d.day < CAST(TIMESTAMP 'Fri 23:00:00' AS DATE)
    AND d.orders > 0
  GROUP BY b.merchant_id
),
live AS (
  SELECT b.merchant_id, count(*) AS orders, max(o.placed_at) AS last_order_at
  FROM orders o
  JOIN branches b ON b.id = o.branch_id
  WHERE o.placed_at >= TIMESTAMP 'Wed 23:00:00 (30 days ago)' AND o.placed_at < TIMESTAMP 'Fri 23:00:00'
    AND o.status <> 'rejected'
  GROUP BY b.merchant_id
)
SELECT m.id AS merchant_id, m.name, mk.code AS market, m.first_order_at,
       coalesce(daily.orders, 0) AS orders_daily_aggregate,
       coalesce(live.orders, 0) AS orders_full_resolution,
       coalesce(live.last_order_at, CAST(daily.last_day AS TIMESTAMP)) AS last_order_at
FROM merchants m
LEFT JOIN markets mk ON mk.id = m.market_id
LEFT JOIN daily ON daily.merchant_id = m.id
LEFT JOIN live ON live.merchant_id = m.id
WHERE m.first_order_at IS NOT NULL AND m.first_order_at < TIMESTAMP 'Fri 23:00:00'
  AND (daily.merchant_id IS NOT NULL OR live.merchant_id IS NOT NULL)
ORDER BY m.id

History

append-only; nothing is edited in place

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