SQL: Accounts Receivable Turnover | Dataford Interview Questions

Your question is SQL: Accounts Receivable Turnover. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Huron Consulting Group needs a client-level financial reporting query for its consulting engagements. Calculate each client's 2024 accounts receivable turnover, defined as posted net credit sales divided by average beginning and ending accounts receivable.

Requirements

Include every client, including clients without 2024 invoices or complete balance records.
Count only invoices dated in 2024 with status = 'posted' as net credit sales.
Calculate average accounts receivable from the January 1 and December 31 balances. Return NULL when either balance is missing or the average balance is zero.
Round turnover to two decimal places and order clients by turnover descending, with null values last.

Schema

clients

Column	Type	Description
client_idPK	INTEGER	Unique client identifier
client_name	VARCHAR(120)	Client organization name

invoices

Column	Type	Description
invoice_idPK	INTEGER	Unique invoice identifier
client_id	INTEGER	Client associated with the invoice
invoice_date	DATE	Invoice issue date
status	VARCHAR(20)	Invoice processing status
net_amount	NUMERIC(14,2)	Net invoice amount

ar_balances

Column	Type	Description
balance_idPK	INTEGER	Unique balance snapshot identifier
client_id	INTEGER	Client associated with the balance
as_of_date	DATE	Date of the balance snapshot
ending_balance	NUMERIC(14,2)	Accounts receivable balance at the snapshot date