Analytical Query Patterns with FILTER, WITHIN GROUP, and Ordered-Set Aggregates for Advanced Statistical Reporting

Introduction

You're three days from the quarterly business review and the VP of Sales wants a single report showing revenue percentiles by region, conversion rates broken out by campaign type, median deal size for won versus lost opportunities, and the mode of the discount tier applied across each sales rep's book of business. Your instinct is to write four separate CTEs, join them together, and call it a day. That works — but it's fragile, slow, and hard to maintain. What you actually need are the SQL features that most developers learned exist but never fully internalized: FILTER, WITHIN GROUP, and ordered-set aggregate functions.

These three constructs sit in a part of the SQL standard that even experienced engineers treat as a curiosity. FILTER is often replaced with CASE WHEN hacks that muddy query intent. WITHIN GROUP is rarely typed by hand because most people don't know it exists. Ordered-set aggregates like PERCENTILE_CONT, PERCENTILE_DISC, and MODE are either avoided entirely or misused in ways that produce subtly wrong answers. The result is that entire categories of statistical reporting problems get solved with application-layer code, Python scripts, or BI tool workarounds — when the database engine could do it better, faster, and in a single pass.

By the end of this lesson, you will have the genuine working knowledge to use all three constructs fluently in production reporting contexts. You'll understand not just the syntax but the execution model, the edge cases that will bite you in real data, and the performance characteristics that determine when to reach for these tools versus alternatives.

What you'll learn:

• How FILTER replaces conditional aggregation patterns and why it's semantically cleaner and often faster than CASE WHEN inside aggregates
• The mechanics of ordered-set aggregates and how WITHIN GROUP (ORDER BY ...) changes the contract between the aggregate function and its input
• Practical use of PERCENTILE_CONT and PERCENTILE_DISC for median and arbitrary quantile calculations, including how they differ from each other
• How MODE() and RANK() within ordered-set semantics solve problems that standard aggregates cannot
• How to combine all three features into single-pass, multi-dimensional statistical reports that would otherwise require multiple scans

---

Prerequisites

This lesson assumes you are comfortable with:

• Standard SQL aggregation (GROUP BY, HAVING, COUNT, SUM, AVG)
• Window functions (OVER, PARTITION BY, ORDER BY in analytical context)
• CTEs and subquery patterns
• Basic statistics vocabulary: median, percentile, mode, quartile

The examples in this lesson are written for PostgreSQL 14+, which has the most complete implementation of SQL:2003 ordered-set aggregates. Behavioral notes for BigQuery, Snowflake, and SQL Server are included where the implementations diverge meaningfully. If you're on MySQL 8.x, be aware that ordered-set aggregates are not supported natively and you'll need workarounds discussed at the end.

---

The Problem with Conditional Aggregation the Old Way

Before building toward the advanced constructs, let's be precise about the problem we're solving. Suppose you have an opportunities table in a CRM-style schema:

-- Schema reference
CREATE TABLE opportunities (
    opportunity_id      BIGINT PRIMARY KEY,
    sales_rep_id        INT,
    region              VARCHAR(50),
    campaign_type       VARCHAR(50),   -- 'inbound', 'outbound', 'partner'
    stage               VARCHAR(30),   -- 'won', 'lost', 'open'
    deal_value          NUMERIC(12,2),
    discount_pct        NUMERIC(5,2),
    close_date          DATE,
    created_at          TIMESTAMPTZ
);

The classic approach to conditional aggregation — computing separate metrics for won vs. lost deals in the same row — looks like this:

-- The classic CASE WHEN approach
SELECT
    region,
    COUNT(*) AS total_opportunities,
    SUM(CASE WHEN stage = 'won' THEN 1 ELSE 0 END) AS won_count,
    SUM(CASE WHEN stage = 'lost' THEN 1 ELSE 0 END) AS lost_count,
    AVG(CASE WHEN stage = 'won' THEN deal_value ELSE NULL END) AS avg_won_value,
    AVG(CASE WHEN stage = 'lost' THEN deal_value ELSE NULL END) AS avg_lost_value
FROM opportunities
GROUP BY region;

This works. It has worked for twenty years. But it has real problems:

Problem 1: Intent is obscured. SUM(CASE WHEN stage = 'won' THEN 1 ELSE 0 END) is doing two things at once — filtering and counting — and a reader has to parse both operations simultaneously to understand what it means.

Problem 2: NULL handling is subtle. AVG(CASE WHEN stage = 'won' THEN deal_value ELSE NULL END) works because AVG ignores NULLs. But if you wrote SUM instead of AVG, the ELSE NULL becomes irrelevant — you'd want ELSE 0. These distinctions are easy to get wrong, and they're invisible in code review.

Problem 3: It doesn't compose with ordered-set aggregates. You cannot write PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN stage = 'won' THEN deal_value ELSE NULL END). The syntax doesn't allow it.

The FILTER clause is the SQL standard's answer to all three problems.

---

FILTER: Conditional Aggregation Done Right

The FILTER (WHERE ...) clause is an extension to any aggregate function that restricts which rows that function sees. Syntactically, it attaches to the aggregate call itself, after its argument list:

aggregate_function(expression) FILTER (WHERE condition)

Let's rewrite the previous query:

-- The FILTER approach
SELECT
    region,
    COUNT(*) AS total_opportunities,
    COUNT(*) FILTER (WHERE stage = 'won') AS won_count,
    COUNT(*) FILTER (WHERE stage = 'lost') AS lost_count,
    AVG(deal_value) FILTER (WHERE stage = 'won') AS avg_won_value,
    AVG(deal_value) FILTER (WHERE stage = 'lost') AS avg_lost_value
FROM opportunities
GROUP BY region;

The difference in readability is immediate. COUNT(*) FILTER (WHERE stage = 'won') reads exactly like what it does. There is no CASE WHEN. There is no ELSE NULL trap. The condition is syntactically separated from the aggregation logic.

How FILTER Actually Executes

Under the hood, PostgreSQL evaluates FILTER conditions during the aggregation pass. Rows that don't satisfy the filter condition are simply not passed to the aggregate's accumulator function. This is mechanically equivalent to the CASE WHEN ... ELSE NULL approach in most cases, but the optimizer has more information to work with.

In PostgreSQL's execution plan, you'll see this reflected in the aggregate node. Run EXPLAIN (ANALYZE, VERBOSE) on both versions:

EXPLAIN (ANALYZE, VERBOSE, FORMAT TEXT)
SELECT
    region,
    COUNT(*) FILTER (WHERE stage = 'won') AS won_count,
    AVG(deal_value) FILTER (WHERE stage = 'won') AS avg_won_value
FROM opportunities
GROUP BY region;

The plan will show a HashAggregate or GroupAggregate node with filter expressions embedded directly in the aggregate descriptions. The optimizer can sometimes use this structure to push filter conditions earlier in the plan when indexes are available on the filter columns.

FILTER with COUNT DISTINCT: A Performance Trap

One important behavioral detail: COUNT(DISTINCT expression) FILTER (WHERE condition) is valid but carries a hidden cost. Each distinct FILTER clause with COUNT DISTINCT requires a separate pass in most planners. If you need multiple conditional distinct counts, benchmark this against a CTE approach:

-- This can be expensive: multiple distinct passes
SELECT
    region,
    COUNT(DISTINCT sales_rep_id) FILTER (WHERE stage = 'won') AS reps_with_wins,
    COUNT(DISTINCT sales_rep_id) FILTER (WHERE stage = 'lost') AS reps_with_losses
FROM opportunities
GROUP BY region;

-- Sometimes faster for large tables: materialize first
WITH rep_stages AS (
    SELECT
        region,
        sales_rep_id,
        MAX(CASE WHEN stage = 'won' THEN 1 END) AS had_win,
        MAX(CASE WHEN stage = 'lost' THEN 1 END) AS had_loss
    FROM opportunities
    GROUP BY region, sales_rep_id
)
SELECT
    region,
    COUNT(sales_rep_id) FILTER (WHERE had_win = 1) AS reps_with_wins,
    COUNT(sales_rep_id) FILTER (WHERE had_loss = 1) AS reps_with_losses
FROM rep_stages
GROUP BY region;

Performance note: The CTE approach above pre-deduplicates before the outer aggregation. For tables with millions of rows and high cardinality on sales_rep_id, this can be significantly faster. Profile both before assuming either is optimal.

FILTER in Window Functions

FILTER also works with window functions, which is underused even by experienced SQL writers:

-- Running count of won opportunities per region, time-ordered
SELECT
    opportunity_id,
    region,
    close_date,
    COUNT(*) FILTER (WHERE stage = 'won') OVER (
        PARTITION BY region
        ORDER BY close_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_wins
FROM opportunities
WHERE stage IN ('won', 'lost');

This calculates a running win count per region without a self-join or subquery. The FILTER clause applies to each window frame evaluation, so only rows where stage = 'won' increment the counter within the window.

---

WITHIN GROUP and the Ordered-Set Aggregate Contract

Here's where the lesson shifts into territory most SQL developers have never visited. Standard aggregate functions like SUM, COUNT, and AVG are order-independent — the result doesn't change regardless of the order rows are processed. Ordered-set aggregates break this contract. They are defined to operate on an ordered sequence of values, and the result is meaningless without that ordering.

This is why they require the WITHIN GROUP (ORDER BY ...) clause. The clause isn't a sort hint or a performance knob — it's a functional requirement. It defines the input ordering that the aggregate uses to compute its result.

The general syntax is:

aggregate_function(argument) WITHIN GROUP (ORDER BY sort_expression [ASC|DESC] [NULLS FIRST|LAST])

The key insight: WITHIN GROUP orders the rows within each group before the aggregate function processes them. This is completely separate from the query's outer ORDER BY clause, which controls the output row order.

Why Ordered-Set Semantics Are Necessary

Consider computing the median of deal values. The median is the value at the 50th percentile — meaning 50% of values fall below it and 50% above. To find that value, you must first sort the values. A standard aggregate has no mechanism to enforce that sort. The ordered-set aggregate model makes the sort explicit and part of the function's definition.

-- Illegal: you cannot compute median with standard aggregate syntax
-- AVG doesn't give you median; there's no MEDIAN() in standard SQL without WITHIN GROUP
SELECT region, MEDIAN(deal_value) FROM opportunities GROUP BY region; -- won't work in PostgreSQL

-- Correct: ordered-set aggregate with explicit ordering
SELECT
    region,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value) AS median_deal_value
FROM opportunities
GROUP BY region;

---

PERCENTILE_CONT: Continuous Percentile Estimation

PERCENTILE_CONT computes a percentile using linear interpolation. When the percentile fraction falls between two actual data points, it returns an interpolated value that may not exist in the dataset.

The function signature:

PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY sort_column [ASC|DESC])

Where fraction is a value between 0 and 1 inclusive (0 = minimum, 0.5 = median, 1 = maximum, and so on).

A Multi-Percentile Report

In practice, you'll often want multiple percentiles simultaneously. PostgreSQL supports array input to PERCENTILE_CONT, which computes all percentiles in a single pass:

-- Single-pass multi-percentile computation
SELECT
    region,
    campaign_type,
    COUNT(*) FILTER (WHERE stage = 'won') AS won_count,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY deal_value)
        FILTER (WHERE stage = 'won') AS p25_won_value,
    PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY deal_value)
        FILTER (WHERE stage = 'won') AS median_won_value,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY deal_value)
        FILTER (WHERE stage = 'won') AS p75_won_value,
    PERCENTILE_CONT(0.9)  WITHIN GROUP (ORDER BY deal_value)
        FILTER (WHERE stage = 'won') AS p90_won_value
FROM opportunities
GROUP BY region, campaign_type
ORDER BY region, campaign_type;

Notice the combination of FILTER and WITHIN GROUP here. The FILTER (WHERE stage = 'won') restricts which rows feed into the percentile calculation. This is the correct way to compute conditional percentiles — and it's a pattern that has no clean equivalent with the CASE WHEN hack.

The Array Syntax for Multiple Percentiles

For even more concise code, PostgreSQL allows you to pass an array of fractions and receive an array of results:

SELECT
    region,
    PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75, 0.9])
        WITHIN GROUP (ORDER BY deal_value)
        FILTER (WHERE stage = 'won') AS quartiles_won
FROM opportunities
GROUP BY region;

The result is an array: {45000.00, 87500.00, 156000.00, 289000.00} — P25, P50, P75, P90 in a single cell. This is useful when you need to pass the full distribution to an application layer or serialize it to JSON.

-- Serialize distribution to JSON for API consumption
SELECT
    region,
    jsonb_build_object(
        'p25', PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY deal_value) FILTER (WHERE stage = 'won'),
        'p50', PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY deal_value) FILTER (WHERE stage = 'won'),
        'p75', PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY deal_value) FILTER (WHERE stage = 'won'),
        'p90', PERCENTILE_CONT(0.9)  WITHIN GROUP (ORDER BY deal_value) FILTER (WHERE stage = 'won')
    ) AS won_deal_distribution
FROM opportunities
GROUP BY region;

Interpolation Mechanics: When the Answer Doesn't Exist in Your Data

Here's the subtlety that catches people. Given the ordered sequence [10, 20, 30, 40], the median (P50) is computed as:

• There are 4 values. The 50th percentile falls between positions 2 and 3 (0-indexed: positions 1 and 2).
• PERCENTILE_CONT(0.5) interpolates: 20 + 0.5 * (30 - 20) = 25
• The result 25 does not appear in the data.

This is statistically correct for continuous distributions but can be surprising when deal_value is always an integer or currency amount. If you want a value that actually exists in the data, use PERCENTILE_DISC.

---

PERCENTILE_DISC: Discrete Percentile Selection

PERCENTILE_DISC returns the first value in the ordered set whose cumulative distribution is greater than or equal to the requested fraction. Crucially, the result is always an actual data value — no interpolation.

SELECT
    region,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY deal_value)
        FILTER (WHERE stage = 'won') AS median_won_value_discrete
FROM opportunities
GROUP BY region;

Given [10, 20, 30, 40]:

• PERCENTILE_DISC(0.5) returns 20 (the value at the lower median position, since the result must exist in the dataset)

When to Use CONT vs. DISC

This is a judgment call that depends on the semantic meaning of your data:

Scenario	Use
Deal value, salary, revenue — numeric quantities where interpolation is meaningful	PERCENTILE_CONT
Discount tier (1, 2, 3, 4), rating score, integer counts	PERCENTILE_DISC
You need the result to be a valid data point (for joining, matching, displaying)	PERCENTILE_DISC
Statistical accuracy for continuous distributions	PERCENTILE_CONT
Time durations, timestamps	Usually PERCENTILE_CONT

Warning: A common mistake is using PERCENTILE_CONT on a categorical ordinal variable (like a discount tier 1–5) and then presenting the interpolated result (e.g., 2.7) as if it's meaningful. It's not. Discount tier 2.7 doesn't exist. Use PERCENTILE_DISC for ordinal categoricals.

---

MODE: Finding the Most Frequent Value

MODE() is the ordered-set aggregate for computing the statistical mode — the most frequently occurring value in a set. Its syntax is slightly unusual: the argument is actually empty, and the value being evaluated comes from the WITHIN GROUP clause:

MODE() WITHIN GROUP (ORDER BY expression)

This can feel backwards at first. Think of it as: "order the values this way, then return the one that appears most often."

Practical Mode Queries

What discount percentage do sales reps most commonly apply?

SELECT
    sales_rep_id,
    COUNT(*) AS total_deals,
    MODE() WITHIN GROUP (ORDER BY discount_pct) AS most_common_discount,
    MODE() WITHIN GROUP (ORDER BY campaign_type) AS most_common_campaign
FROM opportunities
WHERE stage = 'won'
GROUP BY sales_rep_id
ORDER BY total_deals DESC;

Tie-breaking behavior: When two values appear with equal frequency, PostgreSQL's MODE() returns the smallest value (for numeric types) or the first in sort order. This is implementation-defined behavior — the SQL standard doesn't specify tie resolution for MODE. Document this assumption in your reporting code.

Mode vs. MAX/MIN for Most-Common-Value Problems

A frequent mistake is using a subquery with COUNT ... GROUP BY ... ORDER BY COUNT DESC LIMIT 1 inside a lateral join to find the most common value per group. That approach works but requires a lateral join for each group, which is expensive:

-- Expensive approach: lateral join for mode
SELECT
    o.sales_rep_id,
    most_common.discount_pct
FROM (SELECT DISTINCT sales_rep_id FROM opportunities WHERE stage = 'won') o
CROSS JOIN LATERAL (
    SELECT discount_pct
    FROM opportunities
    WHERE sales_rep_id = o.sales_rep_id AND stage = 'won'
    GROUP BY discount_pct
    ORDER BY COUNT(*) DESC
    LIMIT 1
) most_common;

-- Better: use MODE()
SELECT
    sales_rep_id,
    MODE() WITHIN GROUP (ORDER BY discount_pct) AS most_common_discount
FROM opportunities
WHERE stage = 'won'
GROUP BY sales_rep_id;

The MODE() version performs a single scan with an in-memory sort per group. The lateral join version executes a correlated subquery for each distinct sales_rep_id — potentially thousands of index scans.

---

Building the Complete Statistical Report

Now we assemble everything. This is the query you'd actually use to satisfy the quarterly review requirements described in the introduction. It combines FILTER, PERCENTILE_CONT, PERCENTILE_DISC, and MODE in a single aggregation pass over the opportunities table:

WITH report_base AS (
    SELECT
        region,
        campaign_type,

        -- Volume metrics with FILTER
        COUNT(*)                                        AS total_opportunities,
        COUNT(*) FILTER (WHERE stage = 'won')           AS won_count,
        COUNT(*) FILTER (WHERE stage = 'lost')          AS lost_count,
        COUNT(*) FILTER (WHERE stage = 'open')          AS open_count,

        -- Conversion rate
        ROUND(
            100.0 * COUNT(*) FILTER (WHERE stage = 'won')
            / NULLIF(COUNT(*) FILTER (WHERE stage IN ('won','lost')), 0),
            2
        ) AS conversion_rate_pct,

        -- Revenue aggregates for won deals
        SUM(deal_value) FILTER (WHERE stage = 'won')    AS total_won_revenue,
        AVG(deal_value) FILTER (WHERE stage = 'won')    AS avg_won_deal_value,

        -- Percentile distribution of won deal values
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY deal_value)
            FILTER (WHERE stage = 'won')                AS p25_won_value,
        PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY deal_value)
            FILTER (WHERE stage = 'won')                AS median_won_value,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY deal_value)
            FILTER (WHERE stage = 'won')                AS p75_won_value,
        PERCENTILE_CONT(0.9)  WITHIN GROUP (ORDER BY deal_value)
            FILTER (WHERE stage = 'won')                AS p90_won_value,

        -- Discrete median for lost deals (actual deal value, no interpolation)
        PERCENTILE_DISC(0.5)  WITHIN GROUP (ORDER BY deal_value)
            FILTER (WHERE stage = 'lost')               AS median_lost_value,

        -- Mode of discount tier across all closed deals
        MODE() WITHIN GROUP (ORDER BY discount_pct)
            FILTER (WHERE stage IN ('won','lost'))       AS modal_discount_pct,

        -- Mode of discount for won vs. lost separately
        MODE() WITHIN GROUP (ORDER BY discount_pct)
            FILTER (WHERE stage = 'won')                AS modal_discount_won,
        MODE() WITHIN GROUP (ORDER BY discount_pct)
            FILTER (WHERE stage = 'lost')               AS modal_discount_lost

    FROM opportunities
    WHERE close_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 quarter'
      AND close_date <  DATE_TRUNC('quarter', CURRENT_DATE)
    GROUP BY region, campaign_type
),

-- Add IQR and distribution shape metrics
enriched AS (
    SELECT
        *,
        p75_won_value - p25_won_value AS iqr_won_value,
        ROUND(
            (p75_won_value - p25_won_value) / NULLIF(median_won_value, 0) * 100,
            1
        ) AS relative_spread_pct
    FROM report_base
)

SELECT
    region,
    campaign_type,
    total_opportunities,
    won_count,
    lost_count,
    open_count,
    conversion_rate_pct,
    ROUND(total_won_revenue, 0)     AS total_won_revenue,
    ROUND(avg_won_deal_value, 0)    AS avg_won_deal_value,
    ROUND(median_won_value, 0)      AS median_won_value,
    ROUND(median_lost_value, 0)     AS median_lost_value,
    ROUND(p25_won_value, 0)         AS p25_won_value,
    ROUND(p75_won_value, 0)         AS p75_won_value,
    ROUND(p90_won_value, 0)         AS p90_won_value,
    ROUND(iqr_won_value, 0)         AS iqr_won_value,
    relative_spread_pct,
    modal_discount_pct,
    modal_discount_won,
    modal_discount_lost
FROM enriched
ORDER BY region, campaign_type;

This query does everything in essentially one table scan (within the CTE), enriches the results mathematically without re-scanning, and produces a complete statistical report. A naive approach using separate queries for each metric would require 6–8 separate passes over the same data or a complex join structure.

---

Performance Considerations and Execution Internals

Sort Cost of Ordered-Set Aggregates

Ordered-set aggregates impose a sort cost that standard aggregates do not. PERCENTILE_CONT and PERCENTILE_DISC must sort their input values within each group before computing the result. For large groups (hundreds of thousands of rows per group), this sort happens in memory if work_mem is sufficient, otherwise spills to disk.

Tune work_mem for sessions running heavy percentile queries:

SET work_mem = '256MB';

SELECT
    region,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value) AS median
FROM opportunities
GROUP BY region;

Warning: Setting work_mem at the session level in a connection pool can lead to memory pressure. Set it for specific analytical sessions or use SET LOCAL within transactions. A single query with multiple ordered-set aggregates can use work_mem multiple times simultaneously — once per distinct sort required.

Index Utilization

Unlike ORDER BY in a regular query, the ORDER BY inside WITHIN GROUP cannot use indexes directly — the rows must be aggregated first. An index on deal_value will not speed up PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value) in the general case.

However, indexes can help FILTER conditions by enabling index scans that reduce the row set before aggregation:

-- This index helps the FILTER clause reduce row volume early
CREATE INDEX idx_opportunities_stage_region 
    ON opportunities(stage, region)
    INCLUDE (deal_value, discount_pct);

Partial Aggregation and Parallelism

PostgreSQL can parallelize aggregations using partial aggregate nodes. For COUNT, SUM, and AVG, each worker computes a partial aggregate, and a final node combines them. For ordered-set aggregates, parallelism is more limited — PERCENTILE_CONT is not fully parallelizable because you can't trivially merge sorted lists from different workers without a merge step.

Check whether your percentile queries are running in parallel:

EXPLAIN (ANALYZE, VERBOSE, FORMAT TEXT)
SELECT
    region,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value) AS median
FROM opportunities
GROUP BY region;

If you see Gather or Gather Merge nodes but the percentile aggregate is not inside a Partial Aggregate, the percentile computation is not being parallelized. For very large tables partitioned by region, consider pre-filtering with a CTE or materializing per-region subsets.

Approximate Percentiles for Scale

When opportunities becomes opportunities_v2 with 500 million rows, exact PERCENTILE_CONT becomes expensive. PostgreSQL doesn't have a built-in approximate percentile function, but extensions like pg_tdigest provide t-digest-based approximate quantiles with configurable error bounds.

In BigQuery, APPROX_QUANTILES handles this natively:

-- BigQuery: approximate percentiles at scale
SELECT
    region,
    APPROX_QUANTILES(deal_value, 4) AS quartiles  -- returns [min, p25, p50, p75, max]
FROM opportunities
WHERE stage = 'won'
GROUP BY region;

In Snowflake, APPROX_PERCENTILE serves the same purpose:

-- Snowflake: approximate percentile
SELECT
    region,
    APPROX_PERCENTILE(deal_value, 0.5) AS approx_median
FROM opportunities
WHERE stage = 'won'
GROUP BY region;

---

Cross-Platform Compatibility

BigQuery

BigQuery supports PERCENTILE_CONT and PERCENTILE_DISC but only as window functions, not aggregate functions. You must write them with OVER():

-- BigQuery: percentile as window function
SELECT DISTINCT
    region,
    PERCENTILE_CONT(deal_value, 0.5) OVER (PARTITION BY region) AS median_deal_value
FROM opportunities
WHERE stage = 'won';

Notice the different syntax: the fraction is a second argument to the function, not wrapped in WITHIN GROUP. BigQuery does not support FILTER on aggregates either — you use COUNTIF, SUMIF (not standard SQL), or CASE WHEN inside the aggregate.

Snowflake

Snowflake supports PERCENTILE_CONT and PERCENTILE_DISC with WITHIN GROUP syntax matching PostgreSQL. FILTER is supported on aggregate functions since 2022. MODE is available as MODE() with WITHIN GROUP.

SQL Server

SQL Server has no PERCENTILE_CONT / PERCENTILE_DISC as aggregate functions. They exist only as window functions (similar to BigQuery). There is no MODE() aggregate. FILTER is not supported — use CASE WHEN ... ELSE NULL END inside aggregates.

MySQL 8.x

MySQL 8 does not support ordered-set aggregates at all. The workaround for median requires window functions with ROW_NUMBER:

-- MySQL 8: median workaround
WITH ranked AS (
    SELECT
        region,
        deal_value,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY deal_value) AS rn,
        COUNT(*) OVER (PARTITION BY region) AS total
    FROM opportunities
    WHERE stage = 'won'
)
SELECT
    region,
    AVG(deal_value) AS median_deal_value
FROM ranked
WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2))
GROUP BY region;

This works but is significantly more verbose and requires an extra pass for the window function computation.

---

Hands-On Exercise

Work through this exercise using the schema defined earlier. You can create a local PostgreSQL instance and populate with synthetic data, or adapt to your actual data environment.

Setup: Generate Synthetic Data

-- Generate ~50,000 synthetic opportunities for testing
INSERT INTO opportunities (
    opportunity_id, sales_rep_id, region, campaign_type,
    stage, deal_value, discount_pct, close_date, created_at
)
SELECT
    generate_series AS opportunity_id,
    (random() * 49 + 1)::INT AS sales_rep_id,
    (ARRAY['North', 'South', 'East', 'West', 'Central'])[floor(random() * 5 + 1)] AS region,
    (ARRAY['inbound', 'outbound', 'partner'])[floor(random() * 3 + 1)] AS campaign_type,
    (ARRAY['won', 'lost', 'open'])[floor(random() * 3 + 1)] AS stage,
    (random() * 490000 + 10000)::NUMERIC(12,2) AS deal_value,
    (ARRAY[0, 5, 10, 15, 20, 25])[floor(random() * 6 + 1)]::NUMERIC(5,2) AS discount_pct,
    CURRENT_DATE - (random() * 365)::INT AS close_date,
    NOW() - (random() * 365 || ' days')::INTERVAL AS created_at
FROM generate_series(1, 50000);

Exercise 1: Basic FILTER Mastery

Write a query that returns, per region and campaign type:

• Total opportunity count
• Win count and loss count using FILTER
• Win rate as a percentage, handling division by zero
• Average deal value for won and lost opportunities separately, using FILTER

Expected output columns: region, campaign_type, total, won, lost, win_rate_pct, avg_won_value, avg_lost_value

Exercise 2: Percentile Distribution Analysis

Extend your Exercise 1 query to include:

• The 25th, 50th, and 75th percentiles of deal value for won opportunities
• The IQR (P75 - P25) computed in a wrapping CTE
• The discrete median of discount percentage for won deals (so the result is always a valid discount tier)

Think about why you'd use PERCENTILE_DISC rather than PERCENTILE_CONT for discount percentage.

Exercise 3: Rep-Level Mode Analysis

Write a query that returns, for each sales rep:

• Their total deals won and lost
• Their most commonly applied discount percentage (modal discount) for won deals
• Their most commonly applied discount percentage for lost deals
• A column discount_strategy that labels the rep as 'heavy_discounter' (modal discount ≥ 20), 'moderate_discounter' (10–19), or 'low_discounter' (< 10)

Filter to reps with at least 10 closed (won or lost) deals.

Exercise 4: Full Report Integration

Combine all three exercises into a single CTE chain that produces a two-level report:

• Level 1: Regional summary with full percentile distribution
• Level 2: Rep-level detail within each region

Use UNION ALL to stack them with a report_level indicator column, so a BI tool can filter on level.

---

Common Mistakes and Troubleshooting

Mistake 1: Putting ORDER BY Inside WITHIN GROUP When It Should Be Outside

-- Wrong: this is trying to sort the output rows, not the aggregate input
SELECT region, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value)
FROM opportunities
GROUP BY region
ORDER BY deal_value; -- deal_value is ambiguous here

-- Right: order output rows by the computed percentile
SELECT region, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value) AS median_value
FROM opportunities
GROUP BY region
ORDER BY median_value DESC;

The ORDER BY inside WITHIN GROUP is the sort order for the aggregate's input data. The ORDER BY at the query level sorts the result rows. They are completely independent.

Mistake 2: Applying FILTER After WITHIN GROUP in Wrong Position

-- Wrong syntax: FILTER must come after the closing parenthesis of WITHIN GROUP
SELECT PERCENTILE_CONT(0.5) FILTER (WHERE stage = 'won') WITHIN GROUP (ORDER BY deal_value)
FROM opportunities;
-- ERROR: syntax error at or near "WITHIN"

-- Correct: FILTER comes after WITHIN GROUP (...) 
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value) FILTER (WHERE stage = 'won')
FROM opportunities;

The clause order is: function(args) WITHIN GROUP (ORDER BY ...) FILTER (WHERE ...). Many people instinctively put FILTER before WITHIN GROUP because they're thinking of it as "filtering first." In terms of execution semantics, the filtering does happen before the sort — but syntactically, FILTER must trail WITHIN GROUP.

Mistake 3: NULL Handling in Ordered-Set Aggregates

When WITHIN GROUP (ORDER BY deal_value) encounters NULLs, the default behavior follows standard SQL: NULLs sort last for ASC and first for DESC. This means NULLs can distort percentile calculations:

-- If deal_value has NULLs (e.g., open opportunities with no value set),
-- this includes NULLs in the sort, which may skew percentiles
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value)
FROM opportunities; -- includes NULLs!

-- Better: filter out NULLs explicitly
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value)
    FILTER (WHERE deal_value IS NOT NULL)
FROM opportunities;

-- Or use NULLS LAST explicitly and know what you're getting
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deal_value NULLS LAST)
FROM opportunities;

Critical: PERCENTILE_CONT in PostgreSQL actually skips NULLs in the computation even without an explicit filter — NULLs do not count toward the total N used to compute the percentile position. However, this is implementation-specific behavior. FILTER (WHERE column IS NOT NULL) makes the intent explicit and is more portable.

Mistake 4: Expecting MODE to Return Multiple Values on Ties

When two values have identical frequency, MODE() returns one of them (implementation-defined). If your use case requires knowing all tied modal values, MODE() is the wrong tool:

-- MODE() silently picks one on ties -- you might not know there's a tie
SELECT MODE() WITHIN GROUP (ORDER BY discount_pct) FROM opportunities;

-- If you need to detect ties, use a window function approach
WITH freq AS (
    SELECT
        discount_pct,
        COUNT(*) AS freq,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM opportunities
    WHERE stage = 'won'
    GROUP BY discount_pct
)
SELECT discount_pct, freq
FROM freq
WHERE rnk = 1; -- Returns all tied modal values

Mistake 5: Using PERCENTILE_CONT on Non-Numeric Types Without Casting

PERCENTILE_CONT requires a numeric input for interpolation. For timestamps, use it carefully:

-- Valid: PostgreSQL can interpolate timestamps
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY close_date)
FROM opportunities
WHERE stage = 'won';

-- Returns a timestamp, which is fine for date distributions
-- But be aware the result is an interpolated timestamp -- potentially mid-day

For durations (e.g., time to close), compute the numeric interval in days first:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (close_date - created_at::DATE)) / 86400.0
) AS median_days_to_close
FROM opportunities
WHERE stage = 'won';

---

Summary and Next Steps

You now have a complete understanding of three SQL capabilities that together form a powerful toolkit for statistical reporting without leaving the database:

FILTER (WHERE ...) brings conditional aggregation into the first-class syntax of SQL. It's cleaner than CASE WHEN, composes naturally with any aggregate including ordered-set aggregates, works in window functions, and gives the optimizer better information. Replace your CASE WHEN ... ELSE NULL patterns with it everywhere.

WITHIN GROUP (ORDER BY ...) is the mechanism that turns an aggregate function from order-independent to order-aware. It's the syntactic contract that says: "sort the input this way before you compute." Without it, statistical aggregates like percentiles are undefined.

Ordered-set aggregates — primarily PERCENTILE_CONT, PERCENTILE_DISC, and MODE — are the SQL standard's answer to distributional statistics. PERCENTILE_CONT interpolates and gives you statistically accurate continuous percentiles. PERCENTILE_DISC returns actual data values, appropriate for discrete or categorical ordinals. MODE gives you the most frequently occurring value, replacing expensive lateral join patterns.

When combined in a single GROUP BY query with thoughtful CTE layering, these features let you produce comprehensive statistical reports in a single pass over your data — no self-joins, no application-side aggregation, no Python post-processing.

Where to Go Next

• Hypothetical-set aggregates: A related family (RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST used with WITHIN GROUP) answers questions like "where would value X rank in this distribution?" — without materializing the full ranked set
• Window function frame specifications: RANGE BETWEEN vs. ROWS BETWEEN vs. GROUPS BETWEEN — the distinction matters enormously for running statistical calculations
• TimescaleDB and time-series percentiles: How PERCENTILE_CONT composes with time-bucket aggregation for continuous monitoring dashboards
• pg_tdigest and approximate statistics: When your tables are too large for exact ordered-set aggregates and you need approximate quantiles with guaranteed error bounds
• dbt integration patterns: How to model these statistical queries in dbt as metrics, including the incremental materialization strategies that make them tractable on large datasets

The most important next step is to take a report you currently produce with multiple separate queries or application-side aggregation and rebuild it as a single query using these patterns. The exercise will reveal both the power of the approach and the edge cases in your actual data that you'll need to handle — which is where real expertise is built.