MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE task (
    user_id INT(10) NOT NULL,
    attribute VARCHAR(50) NULL DEFAULT NULL);
INSERT INTO task (user_id, attribute)
VALUES
(1, 'red'),
(1, 'green'),
(2, 'green'),
(2, 'yellow'),
(3, 'grey'),
(3, 'coffeemaker'),
(4, 'grey'),
(4, 'purple'),
(5, 'purple'),
(5, 'red'),
(6, 'black'),
(7, 'black'),
(7, 'milk'),
(8, 'milk'),
(8, 'croissant');
select *
from task


user_id
attribute


1
red

1
green

2
green

2
yellow

3
grey

3
coffeemaker

4
grey

4
purple

5
purple

5
red

6
black

7
black

7
milk

8
milk

8
croissant


with recursive aa as (
      select distinct t1.attribute as at1, t2.attribute as at2
      from task t1 join
           task t2
           on t1.user_id = t2.user_id
     ),
     cte as (
      select at1, at2, at1 as found, 1 as lev
      from aa
      union all
      select cte.at1, aa.at2, concat_ws(',', found, aa.at2), lev + 1
      from cte join
           aa
           on cte.at2 = aa.at1
      where find_in_set(aa.at2, found) = 0 
     )
select distinct at1, at2
from cte


at1
at2


green
red

red
red

green
green

red
green

yellow
green

yellow
yellow

green
yellow

coffeemaker
grey

grey
grey

coffeemaker
coffeemaker

grey
coffeemaker

purple
grey

purple
purple

grey
purple

red
purple

purple
red

black
black

milk
black

milk
milk

black
milk

croissant
milk

croissant
croissant

milk
croissant

green
purple

red
yellow

yellow
red

coffeemaker
purple

purple
coffeemaker

grey
red

red
grey

purple
green

black
croissant

croissant
black

green
grey

yellow
purple

coffeemaker
red

grey
green

red
coffeemaker

purple
yellow

green
coffeemaker

yellow
grey

coffeemaker
green

grey
yellow

yellow
coffeemaker

coffeemaker
yellow


with recursive aa as (
      select distinct t1.attribute as at1, t2.attribute as at2
      from task t1 join
           task t2
           on t1.user_id = t2.user_id
     ),
     cte as (
      select at1, at2, at1 as found, 1 as lev
      from aa
      union all
      select cte.at1, aa.at2, concat_ws(',', found, aa.at2), lev + 1
      from cte join
           aa
           on cte.at2 = aa.at1
      where find_in_set(aa.at2, found) = 0 
     )
select dense_rank() over (order by pairs.all_attributes) as group_id, t.user_id, pairs.all_attributes
from (select at1, group_concat(at2) as all_attributes
      from cte
      group by at1
     ) pairs join
     (select user_id, min(attribute) as min_attribute
      from task
      group by user_id
     ) t
     on t.min_attribute = pairs.at1


group_id
user_id
all_attributes


1
8
626c61636b2c626c61636b2c6d696c6b2c6d696c6b2c6d696c6b2c63726f697373616e742c6d696c6b2c626c61636b

2
1
636f666665656d616b65722c707572706c652c7265642c677265792c707572706c652c677265792c636f666665656d616b65722c636f666665656d616b65722c677265792c7265642c79656c6c6f772c677265656e2c7265642c707572706c652c7265642c79656c6c6f772c79656c6c6f77

2
2
636f666665656d616b65722c707572706c652c7265642c677265792c707572706c652c677265792c636f666665656d616b65722c636f666665656d616b65722c677265792c7265642c79656c6c6f772c677265656e2c7265642c707572706c652c7265642c79656c6c6f772c79656c6c6f77

3
5
677265656e2c7265642c79656c6c6f772c7265642c79656c6c6f772c677265656e2c677265792c7265642c677265792c7265642c677265656e2c636f666665656d616b65722c677265792c79656c6c6f772c677265792c707572706c652c636f666665656d616b65722c636f666665656d616b6572

4
4
677265656e2c79656c6c6f772c677265656e2c677265792c79656c6c6f772c707572706c652c79656c6c6f772c636f666665656d616b65722c636f666665656d616b65722c707572706c652c636f666665656d616b65722c707572706c652c7265642c677265656e2c7265642c707572706c652c726564

5
6
6d696c6b2c6d696c6b2c63726f697373616e742c63726f697373616e742c63726f697373616e742c6d696c6b2c6d696c6b2c626c61636b

5
7
6d696c6b2c6d696c6b2c63726f697373616e742c63726f697373616e742c63726f697373616e742c6d696c6b2c6d696c6b2c626c61636b

6
3
79656c6c6f772c677265792c707572706c652c677265792c707572706c652c677265792c7265642c707572706c652c79656c6c6f772c677265656e2c79656c6c6f772c677265656e2c7265642c677265656e2c7265642c636f666665656d616b65722c67726579

user_id	attribute
1	red
1	green
2	green
2	yellow
3	grey
3	coffeemaker
4	grey
4	purple
5	purple
5	red
6	black
7	black
7	milk
8	milk
8	croissant

group_id	user_id	all_attributes
1	8	626c61636b2c626c61636b2c6d696c6b2c6d696c6b2c6d696c6b2c63726f697373616e742c6d696c6b2c626c61636b
2	1	636f666665656d616b65722c707572706c652c7265642c677265792c707572706c652c677265792c636f666665656d616b65722c636f666665656d616b65722c677265792c7265642c79656c6c6f772c677265656e2c7265642c707572706c652c7265642c79656c6c6f772c79656c6c6f77
2	2	636f666665656d616b65722c707572706c652c7265642c677265792c707572706c652c677265792c636f666665656d616b65722c636f666665656d616b65722c677265792c7265642c79656c6c6f772c677265656e2c7265642c707572706c652c7265642c79656c6c6f772c79656c6c6f77
3	5	677265656e2c7265642c79656c6c6f772c7265642c79656c6c6f772c677265656e2c677265792c7265642c677265792c7265642c677265656e2c636f666665656d616b65722c677265792c79656c6c6f772c677265792c707572706c652c636f666665656d616b65722c636f666665656d616b6572
4	4	677265656e2c79656c6c6f772c677265656e2c677265792c79656c6c6f772c707572706c652c79656c6c6f772c636f666665656d616b65722c636f666665656d616b65722c707572706c652c636f666665656d616b65722c707572706c652c7265642c677265656e2c7265642c707572706c652c726564
5	6	6d696c6b2c6d696c6b2c63726f697373616e742c63726f697373616e742c63726f697373616e742c6d696c6b2c6d696c6b2c626c61636b
5	7	6d696c6b2c6d696c6b2c63726f697373616e742c63726f697373616e742c63726f697373616e742c6d696c6b2c6d696c6b2c626c61636b
6	3	79656c6c6f772c677265792c707572706c652c677265792c707572706c652c677265792c7265642c707572706c652c79656c6c6f772c677265656e2c79656c6c6f772c677265656e2c7265642c677265656e2c7265642c636f666665656d616b65722c67726579