5 Use Cases for SQL Window Functions in a Real-Life Cat System

Originally published on Ackee Blog on May 27, 2022

In this article, I explore five practical applications of SQL window functions within the context of a real-life system designed for managing cat-related data. Through code examples and explanations, I’ll demonstrate how these functions can simplify complex queries and enhance data analysis capabilities.

Introduction to Window Functions

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, window functions don’t group rows into a single output row—they retain the individual row identity while providing aggregate information.

Our Cat Management System

For these examples, let’s imagine we’re working with a cat shelter management system with the following key tables:

-- Cats table
CREATE TABLE cats (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    breed VARCHAR(50),
    age INT,
    adoption_date DATE,
    shelter_id INT
);

-- Shelters table
CREATE TABLE shelters (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    location VARCHAR(100)
);

Use Case 1: Ranking Cats by Age Within Each Breed

Scenario: We want to rank cats by age within each breed to identify the oldest cats of each breed.

SELECT 
    name,
    breed,
    age,
    RANK() OVER (PARTITION BY breed ORDER BY age DESC) as age_rank
FROM cats
WHERE adoption_date IS NULL; -- Only non-adopted cats

Benefits: This query helps shelter staff quickly identify senior cats within each breed, which is useful for special care planning and adoption strategies.

Use Case 2: Calculating Running Total of Adoptions

Scenario: Track the cumulative number of adoptions over time to monitor shelter performance.

SELECT 
    adoption_date,
    COUNT(*) as daily_adoptions,
    SUM(COUNT(*)) OVER (ORDER BY adoption_date) as running_total
FROM cats
WHERE adoption_date IS NOT NULL
GROUP BY adoption_date
ORDER BY adoption_date;

Benefits: This provides insights into adoption trends and helps measure the shelter’s success over time.

Use Case 3: Comparing Each Shelter’s Performance

Scenario: Compare each shelter’s adoption rate against the overall average.

SELECT 
    s.name as shelter_name,
    COUNT(c.id) as total_cats,
    SUM(CASE WHEN c.adoption_date IS NOT NULL THEN 1 ELSE 0 END) as adopted_cats,
    ROUND(
        100.0 * SUM(CASE WHEN c.adoption_date IS NOT NULL THEN 1 ELSE 0 END) / COUNT(c.id), 
        2
    ) as adoption_rate,
    ROUND(
        AVG(100.0 * SUM(CASE WHEN c.adoption_date IS NOT NULL THEN 1 ELSE 0 END) / COUNT(c.id)) 
        OVER (), 
        2
    ) as avg_adoption_rate
FROM shelters s
LEFT JOIN cats c ON s.id = c.shelter_id
GROUP BY s.id, s.name;

Benefits: This enables performance comparison and helps identify best practices from high-performing shelters.

Use Case 4: Finding the Most Recent Addition to Each Breed

Scenario: Identify the newest cat of each breed for highlighting in adoption campaigns.

SELECT 
    name,
    breed,
    age,
    arrival_date,
    FIRST_VALUE(name) OVER (
        PARTITION BY breed 
        ORDER BY arrival_date DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as newest_in_breed
FROM cats
WHERE adoption_date IS NULL;

Benefits: This helps create targeted marketing content featuring the newest arrivals in popular breeds.

Use Case 5: Analyzing Adoption Patterns with Lag/Lead

Scenario: Analyze the time gap between consecutive adoptions to optimize staffing and marketing efforts.

SELECT 
    adoption_date,
    name,
    breed,
    LAG(adoption_date) OVER (ORDER BY adoption_date) as previous_adoption,
    adoption_date - LAG(adoption_date) OVER (ORDER BY adoption_date) as days_since_last,
    LEAD(adoption_date) OVER (ORDER BY adoption_date) as next_adoption
FROM cats
WHERE adoption_date IS NOT NULL
ORDER BY adoption_date;

Benefits: Understanding adoption patterns helps with resource allocation and identifying optimal times for adoption events.

Advanced Window Function Techniques

Moving Averages

For tracking adoption trends over time:

SELECT 
    adoption_date,
    COUNT(*) as daily_adoptions,
    AVG(COUNT(*)) OVER (
        ORDER BY adoption_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_average
FROM cats
WHERE adoption_date IS NOT NULL
GROUP BY adoption_date;

Percentile Analysis

For understanding age distribution:

SELECT 
    name,
    age,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY age) OVER () as median_age,
    PERCENT_RANK() OVER (ORDER BY age) as age_percentile
FROM cats
WHERE adoption_date IS NULL;

Performance Considerations

When using window functions in production:

1. Indexing: Ensure proper indexes on columns used in PARTITION BY and ORDER BY
2. Memory Usage: Large result sets with window functions can consume significant memory
3. Query Planning: Use EXPLAIN to understand execution plans

Conclusion

Window functions provide powerful analytical capabilities that can transform how we analyze data in complex systems. In our cat shelter example, they enabled us to:

• Rank and compare data within groups
• Calculate running totals and moving averages
• Perform time-series analysis
• Compare individual records against aggregates

These techniques are applicable beyond cat systems—they’re valuable for any scenario requiring sophisticated data analysis while maintaining row-level detail. The key is identifying where window functions can replace complex self-joins or subqueries, making your SQL more readable and often more performant.

By mastering window functions, you can unlock insights that would otherwise require complex application logic or multiple queries, making your data analysis more efficient and your insights more accessible.