SQL Aggregate Functions

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

The Five Aggregate Functions

Five functions, each with its own NULL story. COUNT(*) ignores nothing. COUNT(column) ignores NULLs. AVG divides by the non-null count, which is the single most common source of "off by a tiny bit" reporting bugs in our corpus of graded answers.

-- Three forms of COUNT
SELECT
  COUNT(*) AS total_rows,           -- 100 (includes NULLs)
  COUNT(email) AS has_email,        -- 90 (10 NULLs skipped)
  COUNT(DISTINCT city) AS cities    -- 25 (unique non-NULL cities)
FROM users;

-- Total and distinct sum
SELECT
  department,
  SUM(salary) AS total_payroll,
  SUM(DISTINCT salary) AS unique_salary_total,
  COUNT(*) AS headcount
FROM employees
GROUP BY department;

-- SUM with COALESCE to guarantee 0 instead of NULL
SELECT COALESCE(SUM(bonus), 0) AS total_bonus
FROM employees
WHERE department = 'Research';

-- Average salary per department
SELECT
  department,
  AVG(salary) AS avg_salary,
  AVG(salary)::NUMERIC(10,2) AS avg_rounded  -- PostgreSQL cast
FROM employees
GROUP BY department;

-- The NULL trap: these return different results
SELECT
  AVG(bonus) AS avg_with_nulls_skipped,  -- ignores NULLs
  SUM(bonus) / COUNT(*) AS avg_with_nulls_as_zero  -- treats NULLs as 0
FROM employees;

-- Earliest order per customer
SELECT
  customer_id,
  MIN(order_date) AS first_order,
  MIN(amount) AS smallest_order
FROM orders
GROUP BY customer_id;

-- MIN on strings (alphabetical)
SELECT MIN(last_name) FROM employees;  -- 'Adams' before 'Zhang'

-- Latest event per user (watermark pattern)
SELECT
  user_id,
  MAX(event_timestamp) AS last_seen,
  MAX(amount) AS largest_purchase
FROM user_events
GROUP BY user_id;

-- Pipeline watermark
SELECT MAX(updated_at) AS high_watermark
FROM source_table;

NULL Handling Summary

NULL handling is the single biggest source of aggregate function bugs. Here is the complete picture in one table.

GROUP BY Patterns with Aggregates

Aggregate functions almost always appear with GROUP BY. These four patterns cover the majority of interview and production scenarios.

SELECT
  department,
  COUNT(*) AS headcount,
  AVG(salary) AS avg_salary,
  MAX(salary) AS top_salary,
  MIN(hire_date) AS earliest_hire
FROM employees
GROUP BY department
ORDER BY headcount DESC;

-- Departments with average salary above 100k
SELECT
  department,
  AVG(salary) AS avg_salary,
  COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;

-- WHERE vs HAVING: different stages
SELECT department, AVG(salary)
FROM employees
WHERE hire_date >= '2020-01-01'   -- filters rows BEFORE aggregation
GROUP BY department
HAVING AVG(salary) > 100000;     -- filters groups AFTER aggregation

SELECT
  department,
  EXTRACT(YEAR FROM hire_date) AS hire_year,
  COUNT(*) AS hires,
  AVG(salary) AS avg_starting_salary
FROM employees
GROUP BY department, EXTRACT(YEAR FROM hire_date)
ORDER BY department, hire_year;

-- Employees earning above company average
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Equivalent with window function
SELECT employee_name, salary
FROM (
  SELECT
    employee_name, salary,
    AVG(salary) OVER () AS company_avg
  FROM employees
) sub
WHERE salary > company_avg;

Aggregates as Window Functions

Every aggregate function can also be used as a window function by adding an OVER clause. This computes the aggregate without collapsing rows. The result is attached to every row as an additional column.

-- Each employee with their department average and total
SELECT
  department,
  employee_name,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  COUNT(*) OVER (PARTITION BY department) AS dept_size,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Adding ORDER BY inside the OVER clause changes the behavior from a static aggregate to a running aggregate. SUM with ORDER BY produces a running total. AVG with ORDER BY produces a running average. This default frame behavior (UNBOUNDED PRECEDING to CURRENT ROW) surprises many candidates.

-- Running total vs static total
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS lifetime_total,
  SUM(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS running_total
FROM orders;

5 Aggregate Function Interview Questions

These questions test practical aggregation skills. Each includes the interviewer's intent and a step-by-step approach.

Aggregate Functions FAQ

Related Guides