With the unit of the question. An invoice is not a joined row. If invoice A has three payment events, a one-to-many join produces three rows for A. PostgreSQL's table-expression documentation describes how a join returns a row for each matching pair, and its aggregate documentation defines count over the rows presented to the aggregate. The database can be perfectly correct while the generated query counts the wrong entity. A citation to that result does not turn the wrong unit into the right answer.

I would define “overdue” before fixing SQL. Is it invoice due date before the end of last month with a balance outstanding at that time, or any invoice currently unpaid that was once due? Payments after month end must not retroactively change the historical count unless that is the specified metric. Build the set of invoice IDs in the authorized tenant and time window, derive balance at the cutoff from payments or a trusted snapshot, and count unique invoices. If the payment join is used to compute balances, pre-aggregate payments per invoice or group by invoice ID before counting. COUNT(DISTINCT invoice.id) can repair a simple fanout, but it does not automatically fix an incorrect temporal predicate or a balance computed after duplicated joins to other tables.

I would inspect row counts and unique invoice IDs at each query stage, with a small fixture: one unpaid invoice, one paid in two installments, one paid after the cutoff, and one belonging to another tenant. Compare the assistant-generated query with a hand-checked reference and the business system's definition. A query that returns five example rows cannot answer a global count. RAG found five rows. Can it answer how many accounts breached a limit? covers that evidence gap. Here the assistant has access to a full relational query but has made the wrong aggregation after a join.

Would I let the model write arbitrary SQL and hope an LLM judge catches it? No. Give it a typed metric or a constrained query builder when the question is common and consequential, then expose the definition and cutoff in the answer. For exploratory SQL, validate query shape and cardinality assumptions, use read-only credentials and require a result trace that includes the counted entity and source snapshot. If the definition remains ambiguous, ask whether “overdue” means at month end or now. The first-principles fix is not DISTINCT as a reflex. It is counting the entity the user named at the time the user meant.