Finance teams live in spreadsheets until the data outgrows them. Analysts who can pull actuals straight from the warehouse, compare them with budget, and reconcile them to the ledger themselves become the fastest, most trusted people in the room.
This path covers the SQL behind monthly close and planning work: rolling transactions up to P&L lines, handling fiscal calendars, calculating growth, year-to-date and variance, laying numbers out like a financial statement, and proving your totals tie back to the general ledger. Every module maps to a report you'd otherwise build by hand in Excel.
A P&L is a GROUP BY: transactions rolled up to account lines, then to sections like revenue, cost of sales and operating expenses. Map accounts to report lines with a lookup table rather than hard-coding account numbers in CASE statements.
SELECT m.report_section, m.report_line,
SUM(g.amount) AS actual
FROM gl_entries g
JOIN account_mapping m ON m.account_no = g.account_no
WHERE g.period = '2026-09'
GROUP BY m.report_section, m.report_line
ORDER BY m.report_section, m.report_line;Finance rarely runs January to December. Truncating dates to month is easy; mapping them to fiscal periods (April-March in India, July-June elsewhere) is best done with a calendar table that holds fiscal year, quarter and period for every date. Join to it instead of recalculating fiscal logic in every query.
SELECT c.fiscal_year, c.fiscal_quarter, SUM(s.amount) AS revenue
FROM sales s
JOIN dim_calendar c ON c.calendar_date = s.invoice_date
GROUP BY c.fiscal_year, c.fiscal_quarter
ORDER BY c.fiscal_year, c.fiscal_quarter;Growth compares a period with an earlier one. LAG() fetches the previous month, or the same month last year with an offset of 12, without a self-join. Guard the division so a zero base doesn't break the query.
SELECT month, revenue,
LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
/ NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1) AS yoy_pct
FROM monthly_revenue;LAG(revenue, 12) assumes no missing months. Build from a calendar table first, or a gap silently shifts every comparison by a month.Year-to-date is a running total that restarts each fiscal year. A windowed SUM() with PARTITION BY fiscal_year gives you YTD for every month in one pass, which is exactly the column a monthly finance pack needs.
SELECT fiscal_year, fiscal_period, revenue,
SUM(revenue) OVER (PARTITION BY fiscal_year
ORDER BY fiscal_period
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS revenue_ytd
FROM monthly_revenue;ROWS BETWEEN ...). The default frame on some engines treats tied dates as one group and can make running totals jump unexpectedly.Variance analysis joins actuals with budget at the same grain (department and month, say) and calculates the absolute and percentage difference. Aggregate each side to that grain before joining, or a department with many transactions will multiply its budget.
WITH a AS (SELECT dept, period, SUM(amount) AS actual FROM gl_entries GROUP BY dept, period),
b AS (SELECT dept, period, SUM(amount) AS budget FROM budget_lines GROUP BY dept, period)
SELECT COALESCE(a.dept, b.dept) AS dept, COALESCE(a.period, b.period) AS period,
a.actual, b.budget,
a.actual - b.budget AS variance,
ROUND(100.0 * (a.actual - b.budget) / NULLIF(b.budget, 0), 1) AS variance_pct
FROM a FULL OUTER JOIN b ON b.dept = a.dept AND b.period = a.period;"What share of revenue comes from each region?" is a percent-of-total question. A window SUM() with an empty OVER() gives the grand total on every row, so the share is a simple division with no subquery.
SELECT region, SUM(amount) AS revenue,
ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_total
FROM sales
GROUP BY region
ORDER BY revenue DESC;Finance packs show months or quarters as columns. Conditional aggregation (SUM(CASE WHEN ... )) pivots periods into columns in plain SQL, producing a layout that pastes straight into a board pack or feeds a Power BI matrix.
SELECT m.report_line,
SUM(CASE WHEN g.period = '2026-07' THEN g.amount ELSE 0 END) AS jul,
SUM(CASE WHEN g.period = '2026-08' THEN g.amount ELSE 0 END) AS aug,
SUM(CASE WHEN g.period = '2026-09' THEN g.amount ELSE 0 END) AS sep,
SUM(g.amount) AS q2_total
FROM gl_entries g
JOIN account_mapping m ON m.account_no = g.account_no
WHERE g.period BETWEEN '2026-07' AND '2026-09'
GROUP BY m.report_line;CASE period quietly drops a month.A finance number is only accepted once it ties out. Reconcile your report total to the general ledger trial balance for the same period, investigate any difference to the account level, and watch for the classic causes: join fan-out, NULLs dropped by WHERE, and period cut-off mismatches.
SELECT r.account_no, r.report_total, t.ledger_balance,
r.report_total - t.ledger_balance AS difference
FROM (SELECT account_no, SUM(amount) AS report_total FROM report_extract GROUP BY account_no) r
JOIN trial_balance t ON t.account_no = r.account_no AND t.period = '2026-09'
WHERE ABS(r.report_total - t.ledger_balance) > 0.01;Practice building a monthly finance pack from raw transactions.
Finance analyst SQL interviews focus on aggregation accuracy, growth and running-total calculations, and explaining why two numbers don't match.
25 questions covering everything above. Score 80% or higher to earn your Financial / FP&A Analyst badge on your dashboard.
25 questions · pass at 80%+ to earn your Financial / FP&A Analyst badge on your dashboard. One-time payment, lifetime access.