Context

Finance and operations teams often use Excel pivot tables to summarize transactions and compare expected versus actual totals during reconciliations. In SQL roles, interviewers want to know how you would translate that same thinking into database queries.

Question

Explain how advanced Excel features such as pivot tables and variance analysis support reconciliations, and how you would reproduce the same workflow in PostgreSQL. In your answer, discuss:

1. How pivot-style summaries help identify mismatches quickly
2. How variance analysis is used to compare two sets of amounts or counts
3. Which SQL techniques you would use instead of Excel features
4. How you would investigate exceptions after finding a variance

Scope guidance

Keep the answer practical and interview-focused. You do not need to discuss spreadsheet mechanics in detail; focus on the reconciliation logic, the SQL equivalents, and how summary-level checks lead into row-level investigation.

Pivot-style aggregation

A pivot table groups data by one or more dimensions and summarizes metrics such as counts or sums. In PostgreSQL, this is commonly reproduced with GROUP BY and conditional aggregation so you can compare categories, periods, or sources side by side.

SELECT account_id, SUM(CASE WHEN source_system = 'ERP' THEN amount ELSE 0 END) AS erp_amount, SUM(CASE WHEN source_system = 'BANK' THEN amount ELSE 0 END) AS bank_amount
FROM reconciliation_entries
GROUP BY account_id;

Variance analysis

Variance analysis measures the difference between two values, such as expected versus actual totals. In reconciliations, this helps identify whether a group balances exactly, is short, or is over by a specific amount.

SELECT account_id, SUM(expected_amount) AS expected_total, SUM(actual_amount) AS actual_total, SUM(actual_amount) - SUM(expected_amount) AS variance
FROM reconciliation_entries
GROUP BY account_id;

Summary-first reconciliation

A strong reconciliation process starts with high-level summaries before drilling into detail. This makes it faster to isolate the specific account, date, or transaction type causing the mismatch instead of reviewing every row manually.

SELECT reconciliation_date, transaction_type, SUM(amount) AS total_amount
FROM reconciliation_entries
GROUP BY reconciliation_date, transaction_type
ORDER BY reconciliation_date, transaction_type;

Exception investigation

Once a variance is identified, the next step is to filter to only the mismatched groups and inspect underlying records. This narrows the search space and helps find missing transactions, duplicates, timing differences, or incorrect classifications.

SELECT *
FROM reconciliation_entries
WHERE account_id = 101
  AND reconciliation_date = DATE '2024-01-31';