Sales Analytics Report

Overview

"Which products sell the best" and "how is revenue trending month over month" are both questions a plain GROUP BY can only partly answer. GROUP BY collapses rows into one summary row per group, but it cannot tell you a product's *rank* relative to the others in the same group, or a *running total* that carries forward from one row to the next — for that you need window functions, which compute a value across a set of related rows without collapsing them into fewer rows. This project builds a sales table and then layers a Common Table Expression (CTE) and three window functions on top of it to answer exactly those questions.

By the end of this tutorial you will have a sales table of individual line items, a WITH ... AS (...) CTE that pre-aggregates monthly totals per product, RANK() and ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) ranking the top-selling products within each month, and a running total built with SUM(...) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) that shows cumulative revenue growing month by month.

Prerequisites

Project Structure

Unlike the previous two projects, this one is intentionally a single table — the interesting work here is entirely in how it is *queried*, not in how many tables it is split across. Every step below builds on sales directly, first through a CTE that reshapes it, then through window functions that add ranking and running totals on top.

Step 1: Create the Sales Table

Each row is one line-item sale: a product, a date, and how much revenue it brought in. Keeping this at the individual-sale grain (rather than pre-aggregating by month) is deliberate — it is what lets the CTE and window functions in the later steps compute monthly totals, rankings, and running sums directly from raw transactional data, the same shape a real point-of-sale or e-commerce database would produce.

CREATE TABLE sales (
    sale_id INT IDENTITY(1,1) PRIMARY KEY,     -- Surrogate key, one row per individual sale
    product_name NVARCHAR(100) NOT NULL,       -- Kept denormalized (no separate products table) to keep this project focused on querying
    sale_date DATE NOT NULL,                   -- The date this specific sale happened
    quantity INT NOT NULL,                     -- Units sold in this sale
    revenue DECIMAL(10,2) NOT NULL,            -- Total revenue from this sale (quantity * unit price, already computed)
    CONSTRAINT CK_sales_quantity CHECK (quantity > 0),   -- A sale of zero or negative units makes no sense
    CONSTRAINT CK_sales_revenue CHECK (revenue >= 0)     -- Revenue can never be negative
);

Step 2: Insert Sample Sales Data

Sample data spanning two months and three products, with deliberately uneven revenue so the ranking queries in Step 4 produce a clear, non-tied top product each month.

INSERT INTO sales (product_name, sale_date, quantity, revenue) VALUES
('Wireless Mouse', '2026-06-03', 10, 299.90),
('Mechanical Keyboard', '2026-06-05', 4, 319.96),
('27-inch Monitor', '2026-06-10', 2, 439.98),
('Wireless Mouse', '2026-06-18', 6, 179.94),
('Mechanical Keyboard', '2026-07-02', 8, 639.92),
('Wireless Mouse', '2026-07-06', 15, 449.85),
('27-inch Monitor', '2026-07-15', 3, 659.97),
('27-inch Monitor', '2026-07-22', 1, 219.99);

Step 3: Aggregate Monthly Totals with a CTE

A CTE — WITH monthly_totals AS (...) — defines a named, temporary result set that the rest of the query (or, in later steps, additional queries) can reference just like a table. Here it groups the raw sales rows by product and month, computing one summarized row per (product, month) pair. DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1) normalizes every sale in a given month down to that month's first day, so every sale in June groups under the same sale_month value regardless of which day it happened on.

WITH monthly_totals AS (
    SELECT
        product_name,
        DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1) AS sale_month,  -- Normalizes every date to the 1st of its month
        SUM(revenue) AS total_revenue,
        SUM(quantity) AS total_units
    FROM sales
    GROUP BY product_name, DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)
)
SELECT * FROM monthly_totals
ORDER BY sale_month, total_revenue DESC;

Step 4: Rank Top Products with RANK() and ROW_NUMBER()

PARTITION BY sale_month restarts the ranking fresh for every month, so "rank 1" means "top product that month," not "top product overall." RANK() and ROW_NUMBER() differ only in how they handle ties: RANK() would give two tied products the same rank number and skip the next one (1, 1, 3), while ROW_NUMBER() always assigns a strictly increasing, unique number (1, 2, 3) even when values are equal — which is exactly what you want when you need to pick a single definitive "the" top product per month, tie or no tie.

WITH monthly_totals AS (
    SELECT
        product_name,
        DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1) AS sale_month,
        SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY product_name, DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)
)
SELECT
    product_name,
    sale_month,
    total_revenue,
    RANK() OVER (PARTITION BY sale_month ORDER BY total_revenue DESC) AS revenue_rank,       -- Ties share a rank, next rank is skipped
    ROW_NUMBER() OVER (PARTITION BY sale_month ORDER BY total_revenue DESC) AS row_num        -- Always unique, even on ties
FROM monthly_totals
ORDER BY sale_month, revenue_rank;

Step 5: Compute a Running Monthly Total

SUM(total_revenue) OVER (ORDER BY sale_month ROWS UNBOUNDED PRECEDING) is a running total: for each row, it sums every row from the very first one (UNBOUNDED PRECEDING) up through the current row, in the order given by ORDER BY sale_month. Unlike a plain SUM() with GROUP BY, which collapses everything into one final total, this window function keeps one row per month while still showing the cumulative figure — exactly what a "revenue so far this year" chart needs.

WITH monthly_revenue AS (
    SELECT
        DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1) AS sale_month,
        SUM(revenue) AS month_revenue
    FROM sales
    GROUP BY DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)
)
SELECT
    sale_month,
    month_revenue,
    SUM(month_revenue) OVER (
        ORDER BY sale_month
        ROWS UNBOUNDED PRECEDING     -- Sum every row from the first through the current one
    ) AS running_total
FROM monthly_revenue
ORDER BY sale_month;

Complete Schema

The full schema, ready to run before any of the CTE/window-function queries above.

CREATE TABLE sales (
    sale_id INT IDENTITY(1,1) PRIMARY KEY,
    product_name NVARCHAR(100) NOT NULL,
    sale_date DATE NOT NULL,
    quantity INT NOT NULL,
    revenue DECIMAL(10,2) NOT NULL,
    CONSTRAINT CK_sales_quantity CHECK (quantity > 0),
    CONSTRAINT CK_sales_revenue CHECK (revenue >= 0)
);

Sample Queries

Two more analytics queries built the same way: a CTE to shape the data, then a window function on top.

-- The single top-selling product for each month, using ROW_NUMBER() to pick exactly one per group
WITH ranked AS (
    SELECT
        product_name,
        DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1) AS sale_month,
        SUM(revenue) AS total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)
            ORDER BY SUM(revenue) DESC
        ) AS rn
    FROM sales
    GROUP BY product_name, DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)
)
SELECT product_name, sale_month, total_revenue
FROM ranked
WHERE rn = 1
ORDER BY sale_month;

-- Each product's share of total revenue across the whole period, using a window SUM() with no PARTITION BY
-- (an empty OVER() treats the entire result set as one window)
SELECT
    product_name,
    SUM(revenue) AS product_revenue,
    SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER () AS pct_of_total
FROM sales
GROUP BY product_name
ORDER BY product_revenue DESC;

Extend This Project

Summary

You used a CTE to pre-aggregate raw sales into monthly totals, then layered window functions on top without ever collapsing rows the way GROUP BY alone would: RANK() and ROW_NUMBER() ranked products within each month via PARTITION BY, and a running SUM() OVER (... ROWS UNBOUNDED PRECEDING) produced a cumulative total that grows month by month. This CTE-plus-window-function pattern — shape the data first, then rank or accumulate over it — is the backbone of almost every real analytics report you will write in T-SQL.