20. Products by One Distributor

--20. Products by One Distributor

WITH Res_CTE (ProductName, DistributorName)
AS
(
    SELECT
                    p.Name AS [ProductName],
                    d.Name AS [DistributorName]
                FROM Products AS p
                INNER JOIN Feedbacks AS f
                ON f.ProductId = p.Id
                INNER JOIN ProductsIngredients AS pin
                ON pin.ProductId = p.Id
                INNER JOIN Ingredients AS i
                ON i.Id = pin.IngredientId
                INNER JOIN Distributors AS d
                ON d.Id = i.DistributorId
                INNER JOIN Countries AS c
                ON c.Id = d.CountryId
                GROUP BY p.Id, p.Name, d.Name, c.Name
)

SELECT
    p.Name AS [ProductName],
    AVG(f.Rate) AS [ProductAverageRate],
    d.Name AS [DistributorName],
    c.Name AS [DistributorCountry]
FROM Products AS p
INNER JOIN Feedbacks AS f
ON f.ProductId = p.Id
INNER JOIN ProductsIngredients AS pin
ON pin.ProductId = p.Id
INNER JOIN Ingredients AS i
ON i.Id = pin.IngredientId
INNER JOIN Distributors AS d
ON d.Id = i.DistributorId
INNER JOIN Countries AS c
ON c.Id = d.CountryId
GROUP BY p.Id, p.Name, d.Name, c.Name
HAVING p.Name IN
                (
                    SELECT r.ProductName
                    FROM Res_CTE AS r
                    GROUP BY r.ProductName
                    HAVING COUNT(r.DistributorName) = 1
                )
ORDER BY p.Id