Given table Products(ProductGroupId INT NOT NULL, ProductId INT NOT NULL)
My task is to output only rows with ProductGroupId that is superset of other product groups

For example, for data

```

    ProductGroupId ProductId
        1             101
        1             102
        1             103
        2             101
        3             102
        4             102
        4             103
        5             104

```

I expect

```

    ProductGroupId ProductId
        1             101
        1             102
        1             103
        5             104

```

Because ProductGroupId=1 is superset of product groups 2,3,4

**My solution so far:**

**First**: We generate pairs of two ProductGroupId that can contain each other

**Second**: We check if superset ProductGroupId (Parent) contains all ProductIds of another ProductGroupId (Child) and Parent has more ProductIds then Child.

To that moment we will have:

```
    ProductGroupId ProductId
        1             101
        1             102
        1             103

```

So it's necessary to add rows where ProductId uniquely identifies ProductGroupId

```
    ProductGroupId ProductId
        5             104

```

After UNION we have

```
    ProductGroupId ProductId
        1             101
        1             102
        1             103
        5             104

```

Code

```
DECLARE @products TABLE
(
    ProductGroupId INT NOT NULL,
    ProductId INT NOT NULL
)

INSERT INTO @products
SELECT 1, 101
UNION
SELECT 1, 102
UNION
SELECT 1, 103
UNION
SELECT 2, 101
UNION
SELECT 3, 102
UNION 
SELECT 4, 102
UNION
SELECT 4, 103
UNION
SELECT 5, 104

;WITH possible_pairs
AS
(
    SELECT ParentId = parent.ProductGroupId,
        ChildId = child.ProductGroupId
    FROM @products parent
        JOIN @products child
            ON child.ProductId = parent.ProductId
    WHERE parent.ProductGroupId <> child.ProductGroupId
    GROUP BY parent.ProductGroupId, child.ProductGroupId
)
, supersets
AS
(
    SELECT ParentId, ChildId
    FROM possible_pairs pp
    WHERE NOT EXISTS
    (
        -- All ProductIds in child exist in parent
        SELECT ProductId
        FROM @products p_child
        WHERE p_child.ProductGroupId = pp.ChildId
        EXCEPT
        SELECT ProductId
        FROM @products p_parent
        WHERE p_parent.ProductGroupId = pp.ParentId
    )
    AND EXISTS
    (
        -- Parent has more ProductIds then child
        SELECT ProductId
        FROM @products p_parent
        WHERE p_parent.ProductGroupId = pp.ParentId
        EXCEPT
        SELECT ProductId
        FROM @products p_child
        WHERE p_child.ProductGroupId = pp.ChildId
    )
)
SELECT p.*
FROM @products p
    JOIN supersets s
        ON p.ProductGroupId = s.ParentId
WHERE NOT EXISTS
(
    -- We need to filter product groups that at the same time are superset and subset. 
    -- Eg. product group 4, that is superset for 3 and subset for 1
    SELECT s2.ChildId
    FROM supersets s2
    WHERE s2.ChildId = s.ParentId
)
/*
    ProductGroupId ProductId
        1             101
        1             102
        1             103
*/
UNION 
SELECT p.*
FROM @products p
WHERE NOT EXISTS
(
    SELECT *
    FROM supersets s2
    WHERE s2.ParentId = p.ProductGroupId
        OR s2.ChildId = p.ProductGroupId
)
/*
    ProductGroupId ProductId
        5             104
*/

```