Context

Analysts often summarize operational error data in Excel using pivot tables and formulas. In SQL interviews, the equivalent skill is turning raw rows into grouped summaries and, when needed, pivot-style report columns.

Core Question

Explain how you would summarize partner error data in SQL in a way that mirrors Excel pivot tables and formulas. Your answer should cover:

1. How to group raw error records by partner and error type
2. How to calculate counts, totals, and percentages
3. How to create pivot-style output in PostgreSQL using conditional aggregation
4. When SQL is preferable to Excel for this task

Scope Guidance

The interviewer is not looking for advanced dynamic SQL. Focus on clear explanations of GROUP BY, aggregate functions, CASE WHEN, and how these replace common Excel pivot table workflows for reporting and analysis.

GROUP BY for summarization

In SQL, GROUP BY is the core mechanism for summarizing row-level data into report-level totals. It plays the same role as placing fields into the Rows section of an Excel pivot table.

SELECT partner_name, error_type, COUNT(*) AS error_count
FROM partner_errors
GROUP BY partner_name, error_type;

Aggregate functions

Functions like COUNT, SUM, and AVG replace many Excel summary formulas. They let you compute totals, counts, and averages directly from grouped data without manual spreadsheet formulas.

SELECT partner_name, COUNT(*) AS total_errors
FROM partner_errors
GROUP BY partner_name;

Conditional aggregation for pivoting

PostgreSQL does not have a simple built-in PIVOT keyword like some other databases, so pivot-style reports are commonly built with SUM(CASE WHEN ...) or COUNT(*) FILTER (WHERE ...). This is the SQL equivalent of turning unique values into separate columns in a pivot table.

SELECT
  partner_name,
  COUNT(*) FILTER (WHERE error_type = 'Missing SKU') AS missing_sku_errors,
  COUNT(*) FILTER (WHERE error_type = 'Price Mismatch') AS price_mismatch_errors
FROM partner_errors
GROUP BY partner_name;

Percent-of-total calculations

Excel users often add formulas next to pivot tables to compute percentages. In SQL, these can be calculated using aggregate expressions, often with CASE or arithmetic on grouped totals.

SELECT
  partner_name,
  COUNT(*) AS total_errors,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_all_errors
FROM partner_errors
GROUP BY partner_name;

SQL vs spreadsheet workflows

Excel is useful for quick ad hoc exploration, but SQL is better for repeatable, scalable reporting on large datasets. SQL also reduces manual steps and makes transformation logic easier to review and automate.