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.