Finance reporting: revenue, receivables and cash

The CFO wants to see, for each month, how much was billed and how much of it has been collected. Billed = sum of amount over invoices with status 'paid' or 'open' ('void' invoices were cancelled and are not billed); collected = invoices with status 'paid'; outstanding = invoices with status 'open'. Use invoices as recorded, dated January to September 2026, grouped by the month of invoice_date. Return month ('YYYY-MM'), billed, collected and outstanding, sorted by month oldest first.

Which plan is slowest to pay? For invoices dated in 2026, the collection rate is the collected amount (status 'paid') divided by the billed amount (status 'paid' or 'open'); void invoices are left out of both. For each account plan return plan, billed, collected and collection rate as a percentage rounded to 1 decimal. Sort by collection rate lowest first, then plan A to Z.

The sales dashboard reports gross sales, but finance books revenue net of refunds. Refunded orders were charged and later paid back; cancelled orders were never charged. For each customer segment return segment, gross sales (orders with status 'paid' or 'refunded'), refunds (orders with status 'refunded') and net revenue (gross sales minus refunds), all as quantity × unit_price rounded to 2 decimals. Sort by net revenue highest first, then segment A to Z.

Pricing wants each category's discount rate against list price. For order lines in orders with status 'paid', list value = quantity × products.price and charged value = quantity × order_items.unit_price. For each product category return category, list value, charged value and discount rate = (list value − charged value) / list value as a percentage, all rounded to 2 decimals. Include every paid line, discounted or not. Sort by discount rate highest first, then category A to Z.

Finance tracks marketing budget against actual spend. Return every one of the 8 campaigns, including Partner affiliates, which is paid per customer and has no ad_spend rows. For each, return campaign name, budget, actual spend (sum of ad_spend.spend over all weeks, 0 if none) rounded to 2 decimals, and percentage of budget used rounded to 1 decimal. Sort by percentage used highest first, then campaign name A to Z.

For the month-end close, finance needs accounts receivable aging as of 30 September 2026. Receivables are invoices with status 'open', and age is the number of days from invoice_date to 2026-09-30. Use exactly four buckets labelled '0-30', '31-60', '61-90' and '90+' (more than 90 days), and show every bucket even when it is empty (0 invoices, amount 0). Return bucket, number of open invoices and open amount, sorted by bucket A to Z (which is also youngest to oldest).

Investors ask how concentrated revenue is. Using orders with status 'paid' only (revenue = quantity × unit_price), compute each customer's paid revenue, treating each customer id as one customer. Return one row with three columns: the combined revenue of the 3 customers with the highest revenue, total paid revenue across all customers (both rounded to 2 decimals), and the top 3's share of total as a percentage rounded to 1 decimal.

For the quarterly business review, show paid revenue (quantity × unit_price, orders with status 'paid') per quarter of 2026, labelled '2026-Q1', '2026-Q2' and '2026-Q3' (Q1 = January to March). Return quarter, revenue rounded to 2 decimals, and the change versus the previous quarter as a percentage of the previous quarter's revenue, rounded to 1 decimal (NULL for 2026-Q1, which has no earlier quarter in the data). Sort by quarter, oldest first.