Common Aggregation Patterns - Real-World Examples

Introduction

You've learned the mechanics of aggregate functions, GROUP BY, HAVING, and JOINs. Now it's time to see how these concepts come together in real-world scenarios.

This lesson presents practical aggregation patterns you'll use in production environments—from business analytics to data reporting. Master these patterns, and you'll be ready to tackle any aggregation challenge!

Pattern 1: Revenue Analysis

Daily Revenue

SELECT
    DATE(order_date) AS date,
    COUNT(*) AS order_count,
    SUM(total) AS revenue,
    ROUND(AVG(total), 2) AS avg_order_value,
    MIN(total) AS smallest_order,
    MAX(total) AS largest_order
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(order_date)
ORDER BY date DESC;

Result:

    date    | order_count | revenue  | avg_order_value | smallest_order | largest_order
------------+-------------+----------+-----------------+----------------+---------------
 2024-12-20 |      47     | 5847.50  |     124.41      |      15.99     |     999.99
 2024-12-19 |      52     | 6125.75  |     117.80      |      18.50     |     875.00

Month-over-Month Growth

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        2
    ) AS growth_pct
FROM monthly_revenue
ORDER BY month DESC;

Result:

        month        |  revenue  | prev_month_revenue | revenue_change | growth_pct
---------------------+-----------+--------------------+----------------+------------
 2024-12-01 00:00:00 |  15847.50 |        14250.25    |     1597.25    |     11.21
 2024-11-01 00:00:00 |  14250.25 |        13450.75    |      799.50    |      5.94

(LAG is a window function—covered in advanced courses)

Revenue by Product and Time

SELECT
    p.name AS product,
    DATE_TRUNC('month', o.order_date) AS month,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.quantity * oi.price) AS revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.order_date >= '2024-01-01'
GROUP BY p.id, p.name, DATE_TRUNC('month', o.order_date)
ORDER BY product, month;

Pattern 2: Customer Segmentation

RFM Analysis (Recency, Frequency, Monetary)

SELECT
    c.id,
    c.name,
    -- Recency: Days since last order
    CURRENT_DATE - MAX(o.order_date) AS days_since_last_order,
    -- Frequency: Number of orders
    COUNT(o.id) AS order_count,
    -- Monetary: Total spent
    COALESCE(SUM(o.total), 0) AS lifetime_value,
    -- Segmentation
    CASE
        WHEN COUNT(o.id) = 0 THEN 'Never purchased'
        WHEN CURRENT_DATE - MAX(o.order_date) <= 30 THEN 'Active'
        WHEN CURRENT_DATE - MAX(o.order_date) <= 90 THEN 'Lapsed'
        ELSE 'Churned'
    END AS status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC;

Result:

 id |      name       | days_since_last_order | order_count | lifetime_value |   status
----+-----------------+-----------------------+-------------+----------------+--------------
  1 | Alice Smith     |          5            |      12     |     1547.82    | Active
  2 | Bob Jones       |         35            |       5     |      687.25    | Lapsed
  4 | Dave Wilson     |       NULL            |       0     |        0.00    | Never purchased

Value Tiers

SELECT
    CASE
        WHEN total_spent >= 1000 THEN 'VIP'
        WHEN total_spent >= 500 THEN 'High Value'
        WHEN total_spent >= 100 THEN 'Regular'
        WHEN total_spent > 0 THEN 'Low Value'
        ELSE 'No Purchases'
    END AS tier,
    COUNT(*) AS customer_count,
    SUM(total_spent) AS total_revenue,
    ROUND(AVG(total_spent), 2) AS avg_spent_per_customer
FROM (
    SELECT
        c.id,
        COALESCE(SUM(o.total), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id
) customer_totals
GROUP BY tier
ORDER BY
    CASE tier
        WHEN 'VIP' THEN 1
        WHEN 'High Value' THEN 2
        WHEN 'Regular' THEN 3
        WHEN 'Low Value' THEN 4
        ELSE 5
    END;

Result:

     tier      | customer_count | total_revenue | avg_spent_per_customer
---------------+----------------+---------------+------------------------
 VIP           |      12        |    18750.50   |         1562.54
 High Value    |      28        |    19845.25   |          708.76
 Regular       |      45        |    10547.75   |          234.39
 Low Value     |      38        |     1247.00   |           32.82
 No Purchases  |      27        |        0.00   |            0.00

Pattern 3: Product Performance

Best Sellers

SELECT
    p.id,
    p.name,
    p.category,
    COUNT(DISTINCT oi.order_id) AS times_ordered,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.price) AS total_revenue,
    ROUND(AVG(oi.price), 2) AS avg_selling_price
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category
ORDER BY total_revenue DESC
LIMIT 20;

Products Never Sold

SELECT
    p.id,
    p.name,
    p.category,
    p.price,
    p.stock
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL
ORDER BY p.category, p.name;

Low Stock Alert

SELECT
    p.name,
    p.stock AS current_stock,
    COALESCE(SUM(oi.quantity), 0) AS units_sold_last_30_days,
    ROUND(
        COALESCE(SUM(oi.quantity), 0) / 30.0,
        2
    ) AS avg_daily_sales,
    ROUND(
        p.stock / NULLIF(COALESCE(SUM(oi.quantity), 0) / 30.0, 0),
        0
    ) AS days_until_stockout
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.id, p.name, p.stock
HAVING p.stock / NULLIF(COALESCE(SUM(oi.quantity), 0) / 30.0, 0) <= 14
    OR (p.stock <= 10 AND COALESCE(SUM(oi.quantity), 0) > 0)
ORDER BY days_until_stockout NULLS LAST;

Pattern 4: Conversion Funnel

Registration to Purchase

SELECT
    DATE_TRUNC('month', c.created_at) AS cohort_month,
    COUNT(DISTINCT c.id) AS registered_users,
    COUNT(DISTINCT o.customer_id) AS customers_who_purchased,
    ROUND(
        100.0 * COUNT(DISTINCT o.customer_id) / COUNT(DISTINCT c.id),
        2
    ) AS conversion_rate
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY DATE_TRUNC('month', c.created_at)
ORDER BY cohort_month DESC;

Result:

    cohort_month     | registered_users | customers_who_purchased | conversion_rate
---------------------+------------------+-------------------------+-----------------
 2024-12-01 00:00:00 |       87         |           52            |      59.77
 2024-11-01 00:00:00 |       92         |           68            |      73.91

Average Time to First Purchase

SELECT
    AVG(first_order_date - created_at) AS avg_days_to_first_purchase,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY first_order_date - created_at) AS median_days
FROM (
    SELECT
        c.id,
        c.created_at,
        MIN(o.order_date) AS first_order_date
    FROM customers c
    INNER JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.created_at
) first_purchases;

Pattern 5: Cohort Analysis

Monthly Cohorts

SELECT
    DATE_TRUNC('month', c.created_at) AS cohort_month,
    COUNT(DISTINCT c.id) AS new_customers,
    COUNT(DISTINCT o.customer_id) AS active_customers,
    SUM(o.total) AS revenue
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
    AND DATE_TRUNC('month', o.order_date) = DATE_TRUNC('month', c.created_at)
GROUP BY DATE_TRUNC('month', c.created_at)
ORDER BY cohort_month DESC;

Retention by Cohort

WITH cohorts AS (
    SELECT
        id,
        DATE_TRUNC('month', created_at) AS cohort_month
    FROM customers
),
cohort_orders AS (
    SELECT
        co.cohort_month,
        o.customer_id,
        DATE_TRUNC('month', o.order_date) AS order_month,
        EXTRACT(MONTH FROM AGE(o.order_date, co.cohort_month)) AS months_since_signup
    FROM cohorts co
    INNER JOIN orders o ON co.id = o.customer_id
)
SELECT
    cohort_month,
    COUNT(DISTINCT CASE WHEN months_since_signup = 0 THEN customer_id END) AS month_0,
    COUNT(DISTINCT CASE WHEN months_since_signup = 1 THEN customer_id END) AS month_1,
    COUNT(DISTINCT CASE WHEN months_since_signup = 2 THEN customer_id END) AS month_2,
    COUNT(DISTINCT CASE WHEN months_since_signup = 3 THEN customer_id END) AS month_3
FROM cohort_orders
GROUP BY cohort_month
ORDER BY cohort_month DESC;

Pattern 6: Inventory Management

Stock Value by Category

SELECT
    category,
    COUNT(*) AS product_count,
    SUM(stock) AS total_units,
    SUM(stock * price) AS inventory_value,
    ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY inventory_value DESC;

Slow-Moving Products

SELECT
    p.name,
    p.category,
    p.stock,
    COALESCE(COUNT(oi.id), 0) AS sales_last_90_days
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.id
    AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY p.id, p.name, p.category, p.stock
HAVING COALESCE(COUNT(oi.id), 0) <= 3
   AND p.stock > 10
ORDER BY sales_last_90_days, p.stock DESC;

Pattern 7: Top N per Group

Top 3 Products per Category

WITH product_revenue AS (
    SELECT
        p.category,
        p.name,
        SUM(oi.quantity * oi.price) AS revenue,
        ROW_NUMBER() OVER (
            PARTITION BY p.category
            ORDER BY SUM(oi.quantity * oi.price) DESC
        ) AS rank_in_category
    FROM products p
    INNER JOIN order_items oi ON p.id = oi.product_id
    GROUP BY p.id, p.category, p.name
)
SELECT
    category,
    name,
    revenue,
    rank_in_category
FROM product_revenue
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category;

Result:

   category   |       name        |  revenue  | rank_in_category
--------------+-------------------+-----------+------------------
 Books        | SQL Mastery       |  3547.50  |        1
 Books        | Python Cookbook   |  2875.25  |        2
 Books        | Data Science 101  |  2450.00  |        3
 Electronics  | Laptop Pro 15     | 24987.50  |        1
 Electronics  | Wireless Mouse    | 12450.75  |        2
 Electronics  | USB-C Hub         |  8745.00  |        3

Pattern 8: Running Totals and Moving Averages

Cumulative Revenue

SELECT
    DATE(order_date) AS date,
    SUM(total) AS daily_revenue,
    SUM(SUM(total)) OVER (ORDER BY DATE(order_date)) AS cumulative_revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE(order_date)
ORDER BY date;

Result:

    date    | daily_revenue | cumulative_revenue
------------+---------------+--------------------
 2024-01-01 |    2547.50    |        2547.50
 2024-01-02 |    3125.75    |        5673.25
 2024-01-03 |    2847.25    |        8520.50

7-Day Moving Average

SELECT
    DATE(order_date) AS date,
    SUM(total) AS daily_revenue,
    ROUND(
        AVG(SUM(total)) OVER (
            ORDER BY DATE(order_date)
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7_days
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE(order_date)
ORDER BY date;

Pattern 9: Data Quality Checks

Completeness Report

SELECT
    'Customers' AS table_name,
    COUNT(*) AS total_rows,
    COUNT(email) AS has_email,
    COUNT(phone) AS has_phone,
    COUNT(address) AS has_address,
    ROUND(100.0 * COUNT(email) / COUNT(*), 2) AS email_completeness,
    ROUND(100.0 * COUNT(phone) / COUNT(*), 2) AS phone_completeness,
    ROUND(100.0 * COUNT(address) / COUNT(*), 2) AS address_completeness
FROM customers;

Orphaned Records

-- Orders without customers
SELECT COUNT(*)
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

-- Order items without orders
SELECT COUNT(*)
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;

Duplicates

-- Find duplicate emails
SELECT
    email,
    COUNT(*) AS duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

Pattern 10: Performance Metrics

Order Fulfillment Time

SELECT
    AVG(shipped_date - order_date) AS avg_days_to_ship,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY shipped_date - order_date) AS median_days,
    MIN(shipped_date - order_date) AS fastest_shipment,
    MAX(shipped_date - order_date) AS slowest_shipment
FROM orders
WHERE shipped_date IS NOT NULL
  AND order_date >= '2024-01-01';

Orders per Hour of Day

SELECT
    EXTRACT(HOUR FROM order_date) AS hour,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY EXTRACT(HOUR FROM order_date)
ORDER BY hour;

Result:

 hour | order_count | revenue
------+-------------+---------
   0  |      12     |  847.50
   1  |       8     |  425.25
   2  |       5     |  247.75
  ...
  14  |      87     | 8547.50
  15  |      92     | 9125.75

Practice Exercises

Exercise 1

Create a customer lifetime value report showing each customer's total orders, total spent, and average order value.

SELECT
    c.id,
    c.name,
    COUNT(o.id) AS total_orders,
    COALESCE(SUM(o.total), 0) AS lifetime_value,
    COALESCE(ROUND(AVG(o.total), 2), 0) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC;

Exercise 2

Find categories where more than 50% of products have never been sold.

SELECT
    p.category,
    COUNT(DISTINCT p.id) AS total_products,
    COUNT(DISTINCT oi.product_id) AS products_sold,
    COUNT(DISTINCT p.id) - COUNT(DISTINCT oi.product_id) AS products_never_sold,
    ROUND(
        100.0 * (COUNT(DISTINCT p.id) - COUNT(DISTINCT oi.product_id)) / COUNT(DISTINCT p.id),
        2
    ) AS pct_never_sold
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.category
HAVING 100.0 * (COUNT(DISTINCT p.id) - COUNT(DISTINCT oi.product_id)) / COUNT(DISTINCT p.id) > 50
ORDER BY pct_never_sold DESC;

Exercise 3

Calculate month-over-month revenue growth for 2024.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2024
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        2
    ) AS growth_pct
FROM monthly
ORDER BY month;

Key Takeaways

• ✅ Revenue analysis: Daily, monthly, and growth metrics
• ✅ Customer segmentation: RFM, tiers, and cohorts
• ✅ Product performance: Best sellers, slow movers, stock alerts
• ✅ Conversion funnels: Registration to purchase tracking
• ✅ Cohort analysis: Retention and behavior by signup date
• ✅ Top N per group: Find leaders in each category
• ✅ Running totals: Cumulative metrics over time
• ✅ Data quality: Completeness, orphans, duplicates
• ✅ Performance metrics: Fulfillment time, peak hours

Next Steps

Congratulations! You've completed Module 5 and mastered aggregation, grouping, and real-world analytical patterns. In Module 6, we'll explore Advanced Query Techniques—subqueries, CTEs, CASE statements, and more tools for complex data manipulation.

Get ready to level up your SQL skills!