SQL Average Order Value

Divide revenue by distinct orders at the correct transaction grain.

What & Why

Average Order Value is total order revenue divided by the number of distinct orders in the same period. AOV explains whether revenue changes come from order volume or the value of each transaction.

See How It Works

BUSINESS QUESTION

Use paid SaaS invoices as Queryflo's internal transaction equivalent and calculate average paid invoice value per account.

id	account_id	amount	status	due_date	paid_at
10	1	240.00	paid	2024-02-01	2024-01-29 13:00:00+00
11	1	260.00	paid	2024-03-01	2024-02-28 16:30:00+00
12	2	90.00	overdue	2024-03-15	NULL
13	3	480.00	open	2024-04-01	NULL

EXAMPLE QUERY

SELECT
  account_id,
  ROUND(SUM(amount)::numeric / NULLIF(COUNT(DISTINCT id), 0), 2) AS average_paid_invoice_value
FROM saas.invoices
WHERE status = 'paid'
GROUP BY account_id
ORDER BY average_paid_invoice_value DESC, account_id;

RESULT — average paid invoice value

account_id	average_paid_invoice_value
1	250.00

Account 1 has two paid invoices totaling 500.00, so its average paid invoice value is 250.00.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice