SQL for Financial Analytics: Profit & Loss Reporting, Forecasting, and Ratio Analysis

Table of Contents

1. Introduction
2. Importance of SQL in Financial Analysis
3. Core Financial Statements in Data Tables
4. Building a Profit & Loss (P&L) Report in SQL
5. Structuring Accounts: Revenue vs Expense Categories
6. Monthly and Quarterly P&L Trends
7. EBITDA and Operating Margin in SQL
8. Using Window Functions for Running Totals
9. Forecasting Future Revenue with SQL Techniques
10. Year-over-Year and Quarter-over-Quarter Comparisons
11. Common Financial Ratios and Metrics
12. Working with Budget vs Actuals
13. Rolling 12-Month Financials
14. Revenue Recognition Logic in SQL
15. Modeling Deferred Revenue and Accruals
16. Real-World Example: SaaS Financial Analytics
17. Cohort-Based Revenue Analysis
18. Integrating SQL Output into BI Dashboards
19. Best Practices for Financial Modeling in SQL
20. Summary and What’s Next

1. Introduction

Financial analytics translates raw transactional data into meaningful insights such as profitability, performance, and forecasted revenue. SQL provides the foundation for transforming structured financial data into clear, scalable reports like P&Ls, forecasts, and KPIs.

2. Importance of SQL in Financial Analysis

• Extract and aggregate general ledger or ERP data
• Prepare cash flow, P&L, and balance sheet views
• Enable dynamic filtering by region, department, or entity
• Build fiscal calendars and roll-forward reports
• Power finance dashboards in BI tools

3. Core Financial Statements in Data Tables

Typical financial table structures:

4. Building a Profit & Loss (P&L) Report in SQL

SELECT
  a.account_group,
  DATE_TRUNC('month', t.transaction_date) AS month,
  SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE -t.amount END) AS amount
FROM transactions t
JOIN accounts a ON t.account_id = a.account_id
WHERE a.account_group IN ('Revenue', 'COGS', 'Expenses')
GROUP BY a.account_group, month
ORDER BY month, a.account_group;

5. Structuring Accounts: Revenue vs Expense Categories

Use a consistent account hierarchy in your accounts table to simplify queries.

6. Monthly and Quarterly P&L Trends

SELECT
  DATE_TRUNC('quarter', transaction_date) AS quarter,
  account_group,
  SUM(amount) AS total
FROM p_l_data
GROUP BY quarter, account_group;

Enable rolling views with window functions.

7. EBITDA and Operating Margin in SQL

EBITDA = Earnings Before Interest, Taxes, Depreciation, and Amortization.

SELECT
  report_month,
  SUM(CASE WHEN group = 'Revenue' THEN amount ELSE 0 END) AS revenue,
  SUM(CASE WHEN group = 'COGS' THEN amount ELSE 0 END) AS cogs,
  SUM(CASE WHEN group IN ('Salaries', 'Rent') THEN amount ELSE 0 END) AS opex,
  SUM(...) AS EBITDA
FROM p_l_data
GROUP BY report_month;

Operating Margin:

(EBITDA / Revenue) * 100

8. Using Window Functions for Running Totals

SELECT
  report_month,
  account_group,
  SUM(amount) OVER (PARTITION BY account_group ORDER BY report_month) AS cumulative_amount
FROM p_l_data;

Track cumulative revenue or cost trends.

9. Forecasting Future Revenue with SQL Techniques

Simple Linear Projection:

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', transaction_date) AS month, SUM(amount) AS revenue
  FROM transactions
  WHERE account_group = 'Revenue'
  GROUP BY month
)
SELECT *,
       LAG(revenue, 1) OVER (ORDER BY month) AS last_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_growth
FROM monthly_revenue;

Use that growth to extrapolate future months manually or in views.

10. Year-over-Year and Quarter-over-Quarter Comparisons

SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
  (revenue - LAG(revenue, 12) OVER (ORDER BY month)) / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100 AS yoy_growth
FROM monthly_revenue;

11. Common Financial Ratios and Metrics

Combine fact tables with customer/order dimensions for full visibility.

12. Working with Budget vs Actuals

SELECT
  a.account_id,
  c.month,
  SUM(b.amount) AS budgeted,
  SUM(t.amount) AS actual,
  SUM(t.amount) - SUM(b.amount) AS variance
FROM budgets b
JOIN transactions t ON b.account_id = t.account_id AND b.month = DATE_TRUNC('month', t.transaction_date)
JOIN calendar c ON b.month = c.date
GROUP BY a.account_id, c.month;

13. Rolling 12-Month Financials

SELECT
  month,
  SUM(amount) OVER (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS rolling_12m
FROM monthly_revenue;

Useful for smoothing volatility and spotting trends.

14. Revenue Recognition Logic in SQL

For SaaS companies or subscription services:

-- Spread revenue over service period
SELECT
  customer_id,
  service_start,
  service_end,
  GENERATE_SERIES(service_start, service_end, INTERVAL '1 month') AS recognition_month,
  amount / EXTRACT(MONTH FROM age(service_end, service_start)) AS monthly_revenue
FROM subscriptions;

15. Modeling Deferred Revenue and Accruals

-- Deferred revenue = total invoice - revenue recognized so far
SELECT
  invoice_id,
  total_amount,
  SUM(recognized_amount) AS revenue_recognized,
  total_amount - SUM(recognized_amount) AS deferred_revenue
FROM revenue_schedule
GROUP BY invoice_id, total_amount;

Track liabilities accurately in SQL reporting views.

16. Real-World Example: SaaS Financial Analytics

Build dashboards using:

• Monthly Recurring Revenue (MRR)
• Churn Rate
• Net Revenue Retention (NRR)
• Customer Lifetime Value (LTV)
• ARPA (Average Revenue per Account)

Example:

SELECT DATE_TRUNC('month', invoice_date) AS mrr_month, SUM(invoice_amount) AS mrr
FROM subscriptions
GROUP BY mrr_month;

17. Cohort-Based Revenue Analysis

WITH cohorts AS (
  SELECT customer_id, MIN(subscription_date) AS cohort_date
  FROM subscriptions
  GROUP BY customer_id
),
activity AS (
  SELECT s.customer_id, DATE_TRUNC('month', payment_date) AS active_month, s.amount
  FROM subscriptions s
)
SELECT
  cohort_date,
  active_month,
  SUM(amount) AS revenue
FROM cohorts
JOIN activity USING(customer_id)
GROUP BY cohort_date, active_month;

Visualize revenue evolution per cohort.

18. Integrating SQL Output into BI Dashboards

• Build views or materialized tables for metrics
• Feed SQL outputs to Tableau, Power BI, or Looker
• Use parameterized queries for flexible analysis
• Automate scheduling via dbt or Airflow

19. Best Practices for Financial Modeling in SQL

• Normalize accounting data with clear account hierarchies
• Use fiscal_calendar dimensions for YTD/MTD logic
• Avoid mixing debits and credits in reporting tables
• Always reconcile totals (trial balance logic)
• Maintain audit trails (source file, ETL job, timestamp)

20. Summary and What’s Next

SQL is indispensable in financial analytics — from producing core reports like P&Ls to modeling deferred revenue and forecasting trends. With structured design and advanced SQL techniques, finance teams can build powerful, auditable, and automated pipelines.