The Finance Reporting Query Library (Capstone) | SQL for Finance Ep8

Episode 8 of *SQL for Finance* — the capstone. Take the seven previous episodes and bundle them into the thing finance teams actually use SQL for: a query library. A folder of `.sql` files, each one answering a recurring question. P&L, balance sheet, AR aging, trial balance. Run from CLI, redirect to CSV, refresh on the first of every month. The library outlives the analyst. This is the mental shift this episode is about. From "I'll write a query when someone asks me a question" to "I have a folder of queries, and I run them on the first of every month." Same dataset, same patterns from Ep1-7, packaged into the artifact you actually ship. 📂 The full query library and AtlasParts dataset are on GitHub: https://github.com/GoCelesteAI/excel-powerbi-for-finance What You'll Learn: - The library mindset — why a `.sql` file beats a notebook beats a stacked Excel formula (plain text, runs anywhere, hands off cleanly to engineering) - Trial balance as the integrity check — `SUM(debit) - SUM(credit)` across `gl_journal` joined to `chart_of_accounts`, with the 22K-row, 70-account integrity check that nets to zero - The marquee P&L — CTE + sign-flip CASE → $10.46M revenue, $10.00M expense, $461K net income (4.4% operating margin) - Balance sheet snapshot — a date filter on the GL slices it to a point in time, sign-flipped per account-type to show $7.99M assets / $3.44M liabilities / $4.55M equity - AR aging as the mirror of Ep7's AP aging — three substitutions only (`sales_orders`, customers' payment terms, Shipped status) reuse the exact same shape - Library packaging — numbered prefixes, a single `for` loop runner that pipes each file to a CSV, the `.headers on` / `.mode csv` ritual Timestamps: 0:00 - Intro - the capstone framing 0:48 - The library mindset - why .sql beats notebooks and Excel 2:08 - Trial balance demo - the 70-account integrity check 3:53 - P&L demo - CTE + sign-flip + the headline numbers 6:15 - Balance sheet snapshot - the date-filter trick 7:55 - AR aging demo - mirror of Ep7's AP aging 9:45 - Library packaging + 8-ep arc recap 11:35 - End screen Key Takeaways: 1. A `.sql` file is the artifact. Plain text means you diff it, commit it, email it, run it in CI. A notebook can't do those things and an Excel formula doesn't survive the workbook. The text is the contract — the same shape that scales up to dbt models when the loose-files phase outgrows itself. 2. Trial balance is the first file in any finance library. `SUM(debit) - SUM(credit)` per account, summing to zero across the whole GL. If the integrity check fails, every report below it is suspect — so the library starts with the sanity check, file `01`, before P&L, balance sheet, or aging. 3. The P&L is one CTE plus one CASE expression. The CTE aggregates `credit - debit` per account (so revenue is positive, expense is negative); the CASE flips the sign on Expense rows so both are presented positive. Drop the result to a CSV and your CFO has the headline pack on the second of every month. AtlasParts 2025 lands at $10.46M revenue, $10.00M expense, $461K net income. 4. Balance sheet is just a date filter. Filter `posting_date` to on-or-before the snapshot date and sum every prior posting. Run it as of December 31 for closing position, July 31 for mid-year. Sign-flip per account-type (Asset = debit-natural, Liability/Equity = credit-natural) and the accounting equation holds — $7.99M = $3.44M + $4.55M. 5. AR aging is Ep7's AP aging with three substitutions. Sales orders instead of AP invoices. Customer payment terms parsed from `'Net 30'` strings. Shipped status meaning unpaid. The aging pattern transfers to inventory aging, contract renewals, ticket aging — a hundred how-old-is-this-thing reports. The 8-episode arc: Ep1 Why SQL · Ep2 WHERE/ORDER BY · Ep3 GROUP BY · Ep4 JOINs · Ep5 CTEs · Ep6 Window functions · Ep7 Dates · Ep8 The library. Same dataset. Same mental model. From `SELECT * FROM customers LIMIT 5` to a board-ready close pack. #SQL #SQLite #FinanceAnalytics #DataAnalytics #SQLforFinance #PnL #BalanceSheet #ARAging #TrialBalance #FinancialReporting #ERPdata #Database #LearnSQL #Capstone