This project analyzes a fictional company's Accounts Receivable data — customers, invoices, and payments — to answer the kinds of questions a finance or data analyst gets asked in real companies: Who owes us money? How overdue are they? Which customers or industries are the riskiest? Are we collecting on time?
It's built as a guided, tiered SQL project. Each tier builds directly on the one before it, moving from simple filtering to multi-table joins to CASE-based aging analysis (the classic AR technique). The dataset is small and fully synthetic, so it's safe to share publicly on GitHub.
Skills demonstrated: SELECT/WHERE/ORDER BY, aggregate functions, GROUP BY/HAVING, multi-table JOINs, CASE statements, and subqueries.
You're a data analyst supporting the Finance team. The company invoices its customers on credit (net-15/30/45/60 terms). Some customers pay in full, some pay partially, and some don't pay by the due date. Finance needs visibility into:
- How much money is currently owed to the company (open AR)
- How overdue those balances are (AR aging)
- Which customers/regions/industries carry the most risk
- How collections are trending
customers — one row per customer
| Column | Type | Description |
|---|---|---|
| customer_id | INTEGER (PK) | Unique customer ID |
| customer_name | TEXT | Company name |
| region | TEXT | Sales region |
| industry | TEXT | Customer's industry |
| credit_limit | DECIMAL | Approved credit limit |
| customer_since | DATE | Date relationship began |
invoices — one row per invoice issued
| Column | Type | Description |
|---|---|---|
| invoice_id | INTEGER (PK) | Unique invoice ID |
| customer_id | INTEGER (FK) | Links to customers |
| invoice_date | DATE | Date invoice was issued |
| due_date | DATE | Payment due date |
| invoice_amount | DECIMAL | Total invoice amount |
| status | TEXT | 'Open', 'Paid', or 'Partially Paid' |
payments — one row per payment received (an invoice can have 0 or 1 payment in this dataset; a natural stretch goal is extending it to multiple partial payments per invoice)
| Column | Type | Description |
|---|---|---|
| payment_id | INTEGER (PK) | Unique payment ID |
| invoice_id | INTEGER (FK) | Links to invoices |
| payment_date | DATE | Date payment was received |
| payment_amount | DECIMAL | Amount paid |
Note on "today": Treat 2026-07-30 as "today" for any question involving overdue days or aging — that's the reference date the data was generated against.
- Open a new database, then run
schema.sqlto create the tables. - Run
seed_data.sqlto load the sample data. - Work through the questions below in order, writing your queries in a
.sqlfile (e.g.answers.sql) as you go — that becomes part of your portfolio submission.
- List all customers located in the
Southeastregion. - List all invoices with an
invoice amountgreater than $5,000, ordered from highest to lowest. - Find all invoices with a status of
'Open'. - What is the total (
SUM) of all invoice amounts in theinvoicestable? - What is the average
credit limitacross all customers? - How many customers are there in each
region?
- List each invoice along with the customer's name and region (join
invoicestocustomers). - For each customer, calculate their total invoiced amount across all invoices.
- For each customer, calculate their total amount paid (join
invoicestopayments). - Which 5 customers have the highest total invoiced amount? (Hint:
GROUP BY+ORDER BY+LIMIT) - Which industries generate the most total invoice revenue? Use
GROUP BYandHAVINGto only show industries with more than $30,000 in total invoices.
This is where it connects directly to what you're practicing right now. In real AR work, "aging" invoices into buckets (Current, 1-30 days overdue, 31-60, 61-90, 90+) is one of the most common CASE use cases there is.
- Write a query that labels each invoice as
'Overdue'or'On Time'based on whetherdue_dateis before 2026-07-30 and the invoice is still'Open'or'Partially Paid'. - Now build a full AR aging bucket column using CASE: label each open/partially-paid invoice as
'Current','1-30 Days','31-60 Days','61-90 Days', or'90+ Days'based on how many days past due it is. - Using the aging buckets from Q13, calculate the total outstanding balance in each bucket. (Hint: you'll need to calculate the outstanding amount — invoice amount minus any payment received — not just the invoice amount.)
- Add a
risk_flagcolumn using CASE: flag any customer whose total outstanding balance exceeds 50% of theircredit_limitas'High Risk', otherwise'Low Risk'.
- Find all customers who have at least one invoice in the
'90+ Days'aging bucket (use a subquery orEXISTS). - Find customers whose total outstanding balance is above the average outstanding balance across all customers (correlated or non-correlated subquery — try both).
- List invoices where the
invoice_amountis greater than the average invoice amount for that customer (correlated subquery).
These aren't required for v1 of the portfolio project — come back to them later as you progress through your curriculum:
- Use
RANK()orDENSE_RANK()to rank customers by total outstanding balance within each region. - Calculate a running total of payments received over time using a window function.
- Calculate days-sales-outstanding (DSO) — a real finance KPI — using aggregate + window functions together.
ar-analysis-sql-project/
├── README.md
├── schema.sql
├── seed_data.sql
└── answers.sql (your solutions — add as you complete each tier)
A guided SQL project analyzing accounts receivable data (customers, invoices, payments) to surface AR aging, collections risk, and outstanding balances — built to practice SQL fundamentals through CASE statements and subqueries using a realistic finance dataset.