What is your net income by month — and how does it compare to last year?

Q: What database does this query work with?

This query is designed for QuickBooks Online. Taptic Data reads your live schema, so the generated SQL always matches your actual table and column names.

Net income is the single most important number in your P&L. This query pulls it from QuickBooks month by month, with year-over-year comparison built in.

📊 QuickBooks Online👥 Finance teams and business owners🔤 Plain-English → SQL

The question

Show me net income by month for the last 12 months compared to the same period last year

↓

The SQL Taptic generates

QuickBooks Net Income by Month — generated by Taptic Data AI

SELECT
  TO_CHAR(DATE_TRUNC('month', i.txn_date), 'YYYY-MM') AS month,
  ROUND(SUM(CASE WHEN i.doc_number IS NOT NULL THEN il.amount ELSE 0 END)::numeric, 2) AS gross_revenue,
  ROUND(SUM(CASE WHEN e.account_type = 'Expense' THEN e.total_amount ELSE 0 END)::numeric, 2) AS total_expenses,
  ROUND(
    SUM(CASE WHEN i.doc_number IS NOT NULL THEN il.amount ELSE 0 END)::numeric
    - SUM(CASE WHEN e.account_type = 'Expense' THEN e.total_amount ELSE 0 END)::numeric
  , 2) AS net_income,
  LAG(
    ROUND(SUM(CASE WHEN i.doc_number IS NOT NULL THEN il.amount ELSE 0 END)::numeric
      - SUM(CASE WHEN e.account_type = 'Expense' THEN e.total_amount ELSE 0 END)::numeric, 2),
    12
  ) OVER (ORDER BY DATE_TRUNC('month', i.txn_date)) AS prior_year_net_income
FROM qbo_invoices i
JOIN qbo_invoice_line_items il ON il.invoice_id = i.id
LEFT JOIN qbo_expenses e ON DATE_TRUNC('month', e.txn_date) = DATE_TRUNC('month', i.txn_date)
WHERE i.txn_date >= NOW() - INTERVAL '24 months'
GROUP BY DATE_TRUNC('month', i.txn_date)
ORDER BY month ASC

Schema-aware SQL generated from plain English19 lines

This query was generated by Taptic Data from plain English against a real QuickBooks Online schema. In Taptic, you type the question — the AI writes the SQL, runs it, and returns the result. You can edit the SQL, ask for explanations, and save it as a refreshable report. This type of analysis is commonly used in quickbooks financial reporting and multi-channel analytics.

How this query works

Breaking it down line by line

Joins invoices to line items to compute gross revenue by month
Left joins expenses to the same month to subtract total costs
Net income calculated inline as revenue minus expenses
LAG(12) window function retrieves the same month last year for YoY comparison without a self-join
24 months of data pulled so the most recent 12 months have a prior-year comparison

What it returns

Result description

Monthly net income for 24 months with gross revenue, total expenses, net income, and prior year net income side by side — ready to chart as a trend.

Why it matters

The business impact

Monthly net income trend is the fastest way to see whether your business is growing, contracting, or seasonal. The year-over-year column removes noise from one-off months and shows the real underlying direction.

All queries and use cases on this page relate to analyzing your QuickBooks Online data in Taptic. To see the full analytics workflow, explore quickbooks financial reporting, multi-channel analytics.

FAQ

Common questions

How do I generate a "QuickBooks Net Income by Month" query automatically?

In Taptic Data, type "Show me net income by month for the last 12 months compared to the same period last year" and the AI generates schema-aware SQL against your real QuickBooks Online data — no manual writing required.

What database does this query work with?

This query is designed for QuickBooks Online. Taptic reads your live schema so the generated SQL always matches your actual table and column names.

Can I edit the generated SQL?

Yes. Taptic shows you the exact SQL it generated. You can edit it directly, ask the AI to explain any line, or request a revision in plain English.

Can I save this as a scheduled report?

Yes. Once you run this query in Taptic, you can save it as a report, add charts and KPIs, and schedule it to email your team on any cadence — daily, weekly, or monthly.

Generate this automatically

Skip the SQL. Ask the question.

In Taptic Data, you type "Show me net income by month for the last 12 months compared ..." and this SQL runs automatically against your real QuickBooks Online data.

Try Taptic Free — $29.99/mo

No credit card required. Connect your data source in under 5 minutes.

Related use cases

See how teams use Taptic for quickbooks financial reporting See how teams use Taptic for multi-channel analytics