Business metrics with SQL

Finance wants to chase customers with unpaid invoices before month-end. An invoice is unpaid when its status is 'open'; 'void' invoices were cancelled and nobody owes them. Return one row per open invoice with three columns: the company name, the invoice_date and the amount. Any row order is fine.

Finance needs cash collected from paid invoices for every month of 2026 so far. Sum amount over invoices with status 'paid' whose invoice_date falls in that month. Return two columns: month as text in the form 'YYYY-MM' and paid_revenue, for January to September 2026, sorted by month from oldest to newest. This is collected invoice revenue, not MRR.

Count your active customers each month. An account is active in a month if it has at least one paid invoice dated in that month. Be careful: a few billing mistakes created two paid invoices for the same account in the same month, and that account still counts once. Return month ('YYYY-MM') and the number of active accounts for January to September 2026, sorted by month from oldest to newest.

Sales leadership wants every live customer to have an account owner. Find accounts whose sales_owner is missing (NULL) and that have not churned (churned_at is NULL). For each, return company, plan and the total amount of their paid invoices dated in 2026, so they can decide who to assign first. Any row order is fine.

Three customers complained that they were charged twice. A duplicate is two or more invoices for the same account with the same invoice_date and the same amount, whatever their status. Return one row per duplicated group with four columns: company, invoice_date, amount and the number of copies. Any row order is fine.

Is any plan losing customers faster than the others? An account has churned when churned_at is not NULL. For each plan return four columns: plan, total number of accounts, number of churned accounts, and the churn rate as a percentage of that plan's accounts rounded to 1 decimal (for example 42.9). Sort by churn rate, highest first.

The support lead wants to know whether urgent tickets really get solved faster. For each priority return four columns: priority, number of tickets, number of tickets still open (resolved_at is NULL), and the average resolution time in days for resolved tickets only (resolved_at minus opened_at), rounded to 1 decimal. Any row order is fine.

Investors ask how much a typical customer on each plan has paid you so far. First compute each account's total of paid invoices (all dates), then average those account totals per plan, over accounts with at least one paid invoice. Return plan and the average revenue per account rounded to 2 decimals. Any row order is fine.