Invoice SQL: Query an Invoicing System to Report Overdue Invoices and Outstanding Balances

Question

## Problem

You have these tables:

```sql
-- invoices(id, client_id, amount, issued_date, due_date, paid_date)  -- paid_date NULL if unpaid
-- clients(id, name, country)
```

**Query 1:** List all overdue invoices (due date passed, not paid) with client name, amount, and days overdue.

```sql
SELECT c.name AS client, i.amount,
       DATEDIFF(CURRENT_DATE, i.due_date) AS days_overdue
FROM invoices i
JOIN clients c ON i.client_id = c.id
WHERE i.paid_date IS NULL
  AND i.due_date < CURRENT_DATE
ORDER BY days_overdue DESC;
```

**Query 2:** For each country, return total outstanding balance and number of overdue invoices.

```sql
SELECT c.country,
       COUNT(*) AS overdue_count,
       SUM(i.amount) AS outstanding_balance
FROM invoices i
JOIN clients c ON i.client_id = c.id
WHERE i.paid_date IS NULL AND i.due_date < CURRENT_DATE
GROUP BY c.country
ORDER BY outstanding_balance DESC;
```

## Follow-ups
1. A client partially pays an invoice (payments table). Rewrite Query 1 to show remaining balance.
2. Add a 30/60/90-day aging bucket column to Query 1.
3. How would you set up a scheduled job to email overdue invoice reports?
4. Write the equivalent of Query 1 in Python pandas without SQL.

Invoice SQL: Query an Invoicing System to Report Overdue Invoices and Outstanding Balances

Question Details

Full Details

Topics

More from Square/Block