SQL - COUNT() as Window Function
• COUNT() as a window function calculates running totals and relative frequencies without collapsing rows, unlike its aggregate counterpart which groups results into single rows per partition
Key Insights
• COUNT() as a window function calculates running totals and relative frequencies without collapsing rows, unlike its aggregate counterpart which groups results into single rows per partition • Window functions enable complex analytical queries like calculating percentages of totals, ranking within groups, and identifying gaps in sequences—all within a single query without self-joins • The PARTITION BY clause divides data into logical groups while ORDER BY controls the window frame, allowing precise control over which rows are included in each calculation
Understanding COUNT() in Window Context
The COUNT() function operates differently as a window function compared to its aggregate form. While aggregate COUNT() collapses rows into groups, window COUNT() maintains all rows while adding computed values. This distinction enables sophisticated analytical queries that would otherwise require subqueries or self-joins.
-- Aggregate COUNT: collapses to one row per department
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;
-- Window COUNT: maintains all rows, adds count column
SELECT
employee_id,
employee_name,
department,
COUNT(*) OVER (PARTITION BY department) as dept_employee_count
FROM employees;
The window function version preserves individual employee records while annotating each with the department’s total count. This proves invaluable for calculations requiring both detail and aggregate information simultaneously.
Basic Syntax and PARTITION BY
The OVER() clause transforms COUNT() into a window function. PARTITION BY divides the result set into logical groups, similar to GROUP BY, but without collapsing rows.
SELECT
order_id,
customer_id,
order_date,
order_amount,
COUNT(*) OVER (PARTITION BY customer_id) as customer_order_count,
COUNT(*) OVER () as total_orders
FROM orders
ORDER BY customer_id, order_date;
This query shows each order alongside two counts: orders per customer and total orders across all customers. The empty OVER() clause treats the entire result set as one partition.
ORDER BY and Running Counts
Adding ORDER BY within the OVER() clause creates running counts—cumulative totals that update row by row.
SELECT
transaction_id,
account_id,
transaction_date,
amount,
COUNT(*) OVER (
PARTITION BY account_id
ORDER BY transaction_date
) as running_transaction_count
FROM transactions
ORDER BY account_id, transaction_date;
The running count increments with each transaction, ordered chronologically within each account. This pattern identifies transaction sequences, helping detect unusual activity patterns or account milestones.
Window Frames: ROWS and RANGE
Window frames define which rows within a partition are included in the calculation. Without explicit frame specification, ORDER BY implies “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.”
-- Explicit frame: count last 7 days of activity
SELECT
event_date,
user_id,
event_type,
COUNT(*) OVER (
PARTITION BY user_id
ORDER BY event_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as events_last_7_days
FROM user_events
ORDER BY user_id, event_date;
This sliding window counts events within a 7-row frame, useful for calculating rolling activity metrics. ROWS counts physical rows, while RANGE considers logical ranges based on values.
-- RANGE example: count events within 7 days
SELECT
event_date,
user_id,
COUNT(*) OVER (
PARTITION BY user_id
ORDER BY event_date
RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
) as events_last_7_days
FROM user_events;
RANGE handles date gaps correctly—if no events occur on certain days, the count still reflects a true 7-day window.
Calculating Percentages and Proportions
Combining window COUNT() with regular aggregates enables percentage calculations without subqueries.
SELECT
product_category,
product_name,
sales_count,
COUNT(*) OVER (PARTITION BY product_category) as category_total,
ROUND(
100.0 * sales_count / COUNT(*) OVER (PARTITION BY product_category),
2
) as pct_of_category,
ROUND(
100.0 * sales_count / COUNT(*) OVER (),
2
) as pct_of_total
FROM product_sales
ORDER BY product_category, sales_count DESC;
Each row shows its contribution to both category and overall totals. This single query replaces what would traditionally require multiple subqueries or CTEs.
Identifying Gaps and Sequences
Window COUNT() helps identify missing sequences or gaps in ordered data.
WITH numbered_orders AS (
SELECT
order_id,
customer_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as order_sequence,
COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) as orders_so_far
FROM orders
)
SELECT
customer_id,
order_date,
order_sequence,
orders_so_far,
CASE
WHEN order_sequence = orders_so_far THEN 'No gaps'
ELSE 'Gap detected'
END as sequence_status
FROM numbered_orders
WHERE customer_id = 12345
ORDER BY order_date;
This pattern detects discontinuities in expected sequences, useful for audit trails and data quality checks.
Conditional Counting with FILTER
Modern SQL supports FILTER clauses for conditional aggregation within window functions.
SELECT
order_date,
customer_id,
order_status,
COUNT(*) FILTER (WHERE order_status = 'completed')
OVER (PARTITION BY customer_id ORDER BY order_date)
as completed_orders_count,
COUNT(*) FILTER (WHERE order_status = 'cancelled')
OVER (PARTITION BY customer_id ORDER BY order_date)
as cancelled_orders_count,
COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date)
as total_orders_count
FROM orders
ORDER BY customer_id, order_date;
For databases without FILTER support, use CASE expressions:
SELECT
order_date,
customer_id,
COUNT(CASE WHEN order_status = 'completed' THEN 1 END)
OVER (PARTITION BY customer_id ORDER BY order_date)
as completed_orders_count
FROM orders;
Practical Application: Customer Segmentation
Combining multiple window COUNT() operations enables sophisticated segmentation logic.
WITH customer_metrics AS (
SELECT
customer_id,
order_date,
order_amount,
COUNT(*) OVER (PARTITION BY customer_id) as lifetime_orders,
COUNT(*) OVER (
PARTITION BY customer_id
ORDER BY order_date
RANGE BETWEEN INTERVAL '90' DAY PRECEDING AND CURRENT ROW
) as orders_last_90_days,
COUNT(*) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as order_number
FROM orders
)
SELECT
customer_id,
order_date,
order_number,
CASE
WHEN order_number = 1 THEN 'New'
WHEN orders_last_90_days >= 3 THEN 'Active'
WHEN orders_last_90_days >= 1 THEN 'Occasional'
ELSE 'Dormant'
END as customer_segment
FROM customer_metrics
ORDER BY customer_id, order_date;
This query classifies customers at each order point, enabling time-based analysis of segment transitions.
Performance Considerations
Window functions can be resource-intensive. Optimize by:
Limiting partitions: Fewer, larger partitions generally perform better than many small ones.
-- More efficient: partition by date ranges
SELECT
event_date,
COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', event_date))
FROM events;
Using appropriate indexes: Index columns used in PARTITION BY and ORDER BY clauses.
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
Filtering before windowing: Apply WHERE clauses to reduce the working set before window calculations.
SELECT
order_id,
COUNT(*) OVER (PARTITION BY customer_id) as order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year' -- Filter first
ORDER BY customer_id;
Window functions execute after WHERE but before ORDER BY in SQL’s logical processing order, making pre-filtering effective for performance optimization.