Using a Common Table Expression (CTE) to Filter, Count, and Average Customer Feedback Scores in SQL Server

When you need to calculate an average that depends on a filtered subset of rows, a Common Table Expression (CTE) can keep the query tidy and readable. CTEs are a temporary result sets defined within a SQL query that can be referenced by the main query or even recursively within themselves. They provide a way to structure queries for improved readability, making them a great tool for handling complex queries.

Below is an example that uses a CTE to:

Pull together raw feedback entries for a set of products.
Count how many feedback records each product received (ignoring products with no feedback).
Compute the average number of feedback entries per product that actually got feedback.

Now let’s go ahead with the example.

Step 1 – Set up the Schema

The scenario we’ll use here uses a fictional online marketplace called “GearHub”, which sells niche hobby equipment such as custom‑laser‑cut drone frames, modular tabletop gaming accessories, and artisanal 3‑D‑printed board game pieces. We’ll use a CTE to query the user feedback for such products. So with that in mind, let’s create some tables:

-- Create schema
CREATE SCHEMA GearHub;
GO

-- Create a table that holds product information
CREATE TABLE GearHub.Products (
    ProductID      INT PRIMARY KEY,
    ProductName    NVARCHAR(100) NOT NULL,
    Category       NVARCHAR(50)  NOT NULL
);
GO

-- Create a table that stores customer feedback for each product
CREATE TABLE GearHub.Feedback (
    FeedbackID     INT IDENTITY(1,1) PRIMARY KEY,
    ProductID      INT NOT NULL REFERENCES GearHub.Products(ProductID),
    Rating         TINYINT CHECK (Rating BETWEEN 1 AND 5),   -- 1 = poor, 5 = excellent
    Comment        NVARCHAR(500),
    FeedbackDate   DATE NOT NULL DEFAULT GETDATE()
);
GO

Populate the Tables with Some Sample Data

Now we’ll go ahead and populate the above tables:

-- Insert a handful of products
INSERT INTO GearHub.Products (ProductID, ProductName, Category) VALUES
(101, N'Carbon‑Fiber Drone Frame',          N'Drones'),
(102, N'Modular RPG Terrain Tiles',         N'Gaming'),
(103, N'Bioluminescent 3‑D Printed Dice',  N'Board Games'),
(104, N'Programmable LED Light Strip Kit',  N'Electronics'),
(105, N'Magnetic Puzzle Box',               N'Toys');

-- Insert varied feedback entries (some products get many, others few)
INSERT INTO GearHub.Feedback (ProductID, Rating, Comment, FeedbackDate) VALUES
(101, 5, N'Perfect balance, flew like a dream.',           '2024-06-12'),
(101, 4, N'Slightly pricey but worth it.',                '2024-07-01'),
(101, 5, N'Lightweight and sturdy.',                      '2024-07-15'),

(102, 3, N'Nice tiles but colors fade quickly.',          '2024-05-20'),
(102, 4, N'Great for quick map setups.',                  '2024-06-02'),

(103, 5, N'Dice glow in the dark—awesome for night games.', '2024-04-30'),
(103, 5, N'Precision printed, feels premium.',           '2024-05-10'),
(103, 4, N'Good, but a bit fragile.',                    '2024-05-22'),
(103, 5, N'Best dice I’ve owned.',                        '2024-06-08'),

(104, 2, N'LEDs flicker after a week.',                  '2024-03-18');

Feel free to run more INSERT statements with different dates or ratings if you’d like a larger dataset for testing.

Step 2 – Write the CTE Query

Now we’ll compute the average number of feedback entries per product that actually received feedback. Products without any feedback are excluded from both the count and the average.

WITH FeedbackCounts (ProductID, NumFeedback)
AS (
    SELECT
        f.ProductID,
        COUNT(*) AS NumFeedback
    FROM GearHub.Feedback AS f
    GROUP BY f.ProductID
    HAVING COUNT(*) > 0
)

SELECT
    AVG(CAST(NumFeedback AS FLOAT)) AS AvgFeedbackPerProduct
FROM FeedbackCounts;

Result:

2.5

That means, on average, each product that got feedback received 2.5 entries.

Here’s a quick rundown on what happened:

The CTE (FeedbackCounts) groups the Feedback table by ProductID and counts how many rows each product has. The HAVING clause guarantees we ignore products that never received feedback (e.g., the Magnetic Puzzle Box).
The outer SELECT simply averages those counts, casting to FLOAT so the division yields a decimal result instead of integer truncation.

Why Use a CTE Here?

It’s possible to achieve the same result without a CTE, so you may be wondering why even do it. Here are a couple of reasons you might consider using a CTE instead of other methods:

Readability: The counting logic lives in its own named block (FeedbackCounts). If you later need to join additional tables (e.g., product categories) you can reference the CTE directly without repeating the aggregation.
Reusability: You could extend the CTE to pull extra columns (like the average rating per product) while still keeping the final averaging step clean.
Maintainability: Future changes (e.g., adding a date filter) only require editing the CTE definition, not every place the aggregated data is used.

Here’s an example of using a derived table (a subquery placed in the FROM clause) to get the same result:

SELECT
    AVG(CAST(fc.NumFeedback AS FLOAT)) AS AvgFeedbackPerProduct
FROM (
    SELECT
        f.ProductID,
        COUNT(*) AS NumFeedback
    FROM GearHub.Feedback AS f
    GROUP BY f.ProductID
    HAVING COUNT(*) > 0
) AS fc;

Result:

2.5

The logic is identical in that it first aggregates the feedback counts per product, then it takes the average of those counts.

Here’s what’s different:

The inner SELECT … GROUP BY that previously lived inside the CTE is now a subquery (fc) that appears right after FROM.
The outer SELECT works on that derived table exactly the same way it did on the CTE.

Here’s a breakdown of why you might choose one over the other:

Aspect	CTE (`WITH … AS (…)`)	Derived Table (subquery)
Readability	Gives the aggregation a name (`FeedbackCounts`) that can be referenced later in the same statement. Helpful when the same result set is used multiple times.	Keeps everything in a single `SELECT` block. For a one‑off calculation it can feel a little tighter.
Reusability	You can reference the CTE multiple times in the same query (e.g., join it to another table, filter it again).	To reuse the same aggregation you’d have to repeat the subquery or wrap it in a view.
Performance	Modern SQL engines treat both forms similarly; the optimizer usually produces the same execution plan.	Same as CTE for a single use; no measurable difference.
Complexity	Slightly more lines because of the separate `WITH` clause, but the logical steps are clearer.	Fewer keywords, all in one place.

Another way to do it is to embed the aggregation directly in the AVG() call:

SELECT
    AVG(CAST(cnt AS FLOAT)) AS AvgFeedbackPerProduct
FROM (
    SELECT COUNT(*) AS cnt
    FROM GearHub.Feedback AS f
    GROUP BY f.ProductID
    HAVING COUNT(*) > 0
) AS x;

Result:

2.5

Here the inner query only returns the count column (cnt). The outer query averages those values. Functionally it’s identical to the CTE version.

Our CTE example is quite simple, but we could easily expand the CTE query to be a lot more complex. This would allow our outer query to remain relatively simple while the CTE performs the bulk of the heavy lifting. Feel free to experiment with the schema and data from this example. The basic idea remains the same in that you can use a CTE to isolate a subquery, then apply further aggregation on top of it.

Step 1 – Set up the Schema

Populate the Tables with Some Sample Data

Step 2 – Write the CTE Query

Why Use a CTE Here?

Step 1 – Set up the Schema

Step 2 – Write the CTE Query