SQL Mastery
Advanced 15 min read SQL Mastery

Advanced Aggregates

More of the analytics, still in SQL

Fruitful aggregate extensions — FILTER, DISTINCT aggregates, percentiles, array_agg and statistical functions — turn SQL into a full analytics language.

FILTERpercentileDISTINCTarray_aggstatistical aggregates
Prerequisites: Aggregations and GROUP BY
Before we start

What you will learn

  • Use FILTER to compute conditional aggregates in one pass
  • Compute percentiles and quantiles
  • Use array_agg/string_agg for reshaping
Introduction

What is this?

Beyond COUNT/SUM, modern SQL ships percentile_cont/disc, FILTER clauses, DISTINCT aggregates, array_agg, string_agg and even regression functions — so heavy analytics run in-engine rather than in Python.

Motivation

Why do you need it?

  • FILTER computes several conditional sums in one scan instead of three filtered subqueries.
  • Percentiles measure the real user experience (p95 latency) where averages lie.
  • array_agg/string_agg reshape many-to-one rows that dashboards and ML feature builders need.
Think about it

Mental model

Imagine

A barista's multi-order ticket

Think of

One query with several aggregates

Instead of making three separate drinks and wrangling them onto one ticket, the barista writes all three items on a single order. FILTER is that ticket: several conditional totals computed in one pass over the same cups.

Mechanics

How it works

  • FILTER (WHERE cond) restricts a single aggregate: SUM(x) FILTER (WHERE status = 'paid').
  • COUNT(DISTINCT x) counts unique values; SUM(DISTINCT x) (rare) dedups before summing.
  • percentile_cont(0.95) WITHIN GROUP (ORDER BY x) interpolates; percentile_disc returns an actual value.
  • array_agg collects values into an array; string_agg concatenates with a delimiter.
  • Stats: variance, stddev, corr, regr_* exist on main engines and rows group-bucketed stay in-SQL.

Choosing an advanced aggregate.

Conditional totals
Unique / distinct
Percentiles
Reshape lists
Stats
Hands-on

Examples

Conditional aggregates + percentile

Conditional aggregates + percentile
SELECT
  channel,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid_revenue,
  COUNT(DISTINCT user_id) AS unique_users,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY amount) AS p95_amount
FROM orders
GROUP BY channel;

One scan produces paid-only sums, distinct user counts and a p95 — no subqueries needed.

Real world

In production

  • A latency dashboard buckets p95 response time by endpoint entirely in the warehouse query.
  • A funnel pull uses SUM(...) FILTER (WHERE step = 2) across step events in one pass.
  • A feature builder array_aggs a user's last 30 events per key for a model's sequential input.
Guidelines

Best practices

  • Prefer FILTER over multiple queries when the same rows feed several aggregates.
  • Choose percentile_cont when thresholds may fall between observations; percentile_disc for exact observed values.
  • Guard array_agg with a size cap or it can explode a report row into a huge blob.
  • Document DISTINCT semantics: COUNT(DISTINCT) discards NULLs and de-dupes by value in most engines.
Watch out

Common mistakes

  • Forgetting that percentiles need WITHIN GROUP (ORDER BY x) syntax and erroring elsewhere.
  • Using FILTER in a dialect that doesn't support it (pre-13 PostgreSQL or some engines) and 'fixing' by rewriting to CASE SUM.
  • array_agg without a bounded ORDER BY, producing ordering-dependent features.
  • COUNT(DISTINCT) on a high-cardinality string column hammering memory in some engines.
Root cause analysis

Common issues

Error
FILTER unsupported / syntax error
Root cause

Engine predates FILTER or uses different syntax

Fix

Rewrite as SUM(CASE WHEN cond THEN x ELSE 0 END) or check the dialect docs.

Error
p95 histogram looks flat/wrong
Root cause

percentile_disc on low-cardinality data returns an actual value that jumps

Fix

Use percentile_cont for interpolated estimates or increase histogram granularity.

When things break

Debugging tips

  • Verify FILTER conditions against sample rows: compute the conditional SUM by hand for 3 rows.
  • Check engine version for FILTER/percentile support before blaming your SQL.
  • With array_agg, SELECT DISTINCT the array's length to catch unexpected fan-out.
Interviews

Interview checkpoint

Practice

Hands-on lab

Conditional Aggregates, Percentiles and Lists
25 min · advanced
In progress
Objectives
  • Write FILTER aggregate queries in SQLite/Postgres
  • Compute a hand-checkable percentile
  • Use string_agg/group_concat to build a list column

Conditional Aggregates, Percentiles and Lists

25 min · advanced

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

SUM(amount) FILTER (WHERE status = 'paid') computes what?

2

percentile_disc can return interpolated values that aren't present in the data.

Go deeper

Official documentation

PostgreSQL aggregate docs
docs
BigQuery aggregate functions
docs