SQL depth: one query instead of a backend service

You have probably written a lot of backend code just to aggregate data: load rows, group them in memory, sort, pick the top item. Often the right SQL query does all of it faster and in one place. Today I want to show a task that looks tangled at first and turns out elegant in SQL.

The task

An online store has two tables:

1. sales - sales records:
   • sale_id - unique sale identifier;
   • product_id - product identifier;
   • sale_date - sale date;
   • amount - sale amount.
2. products - product catalogue:
   • product_id - product identifier;
   • category - category;
   • price - price.

For each category, for the last six months, return:

• the total number of items sold;
• the total sales amount;
• the average check (average sale amount);
• the name of the best-selling product by units.

The SQL query

WITH product_sales AS (
    SELECT
        p.category,
        s.product_id,
        SUM(s.amount) AS total_sales,
        COUNT(s.sale_id) AS total_transactions,
        ROW_NUMBER() OVER (
            PARTITION BY p.category
            ORDER BY SUM(s.amount) DESC
        ) AS sales_rank
    FROM sales s
    JOIN products p ON s.product_id = p.product_id
    WHERE s.sale_date >= NOW() - INTERVAL '6 months'
    GROUP BY p.category, s.product_id
)
SELECT
    ps.category,
    SUM(ps.total_sales) AS total_category_sales,
    SUM(ps.total_transactions) AS total_transactions,
    ROUND(AVG(ps.total_sales::numeric), 2) AS avg_sale_per_product,
    (
        SELECT product_id
        FROM product_sales
        WHERE sales_rank = 1 AND category = ps.category
    ) AS most_popular_product
FROM product_sales ps
GROUP BY ps.category;

How it works

The window function syntax is documented in the PostgreSQL tutorial; the same idea exists in MySQL 8, SQL Server and Oracle.

1. WITH product_sales builds a temporary result set: sales amount and transaction count per product, plus a rank inside each category using the window function ROW_NUMBER().
2. The main query aggregates that set per category: total sales, total transactions, the average per product and the top-ranked product from a correlated subquery.

Two honest notes

The query above is close to the original example, and it has two rough edges worth knowing:

• most_popular_product returns the product ID, not its name - in a real report you would join products again.
• AVG(ps.total_sales) is the average per product, not the average check per transaction. If you need the true average check, divide the category total by the transaction count.

Both are one-line fixes, and they show why it is worth reading an elegant query twice before shipping it.

Why SQL beats backend loops

1. Performance. Databases are optimized for aggregating, filtering and ranking large volumes at the engine level. Backend code rarely matches that.
2. Less code. Loading, filtering, grouping, ranking - one query instead of dozens of lines.
3. Readability. JOIN, GROUP BY, window functions and subqueries express the logic compactly.
4. Scalability. Need twelve months instead of six, or an extra filter? One clause changes; the rest of the pipeline stays.

SQL is not a legacy skill. It is still the shortest path from a data question to a correct answer.

Questions about SQL

When is SQL the wrong tool? When the rule needs an external service, a user session or a long-running job. Keep aggregation in the database, keep decisions in code.

Do indexes still matter? More than ever. A window function over an unindexed table is a slow sort with extra steps.

Does the query port? The idea does, the syntax does not. Window functions exist in MySQL 8, SQL Server and Oracle, with differences in frames and null ordering.

When the SQL query is the wrong tool

A single SQL query wins when the data is already in the database and the shape is stable. It loses when the rules change weekly or the team cannot read the query at 2 a.m. The MySQL reference on CTEs is the honest starting point, and every SQL query in this article was run before it was published.

Related reading: Software architecture: six patterns and how to choose and The importance of correct naming in code.