How to select pair of userID that have at least 3 reviewed productID with a product score of at least 4/5 in sql

I have a dataset like this:

I want to have an output like this:

but I’m only able to get the first two column with this query:

CREATE TABLE score_greater_than_3 AS
SELECT userid, productid, score
FROM reviews
WHERE score >= 4;

SELECT s1.userid as userid1, s2.userid as userid2
FROM score_greater_than_3 s1 
INNER JOIN score_greater_than_3 s2 ON s1.productid=s2.productid AND s1.userid<s2.userid
GROUP BY s1.userid, s2.userid
HAVING count(*)>=3;

How can i get the matching products? i’m ok with an output like this too if its more easy

>Solution :

You can try with the following query:

WITH cte AS (
    SELECT r.userid,
           r.productid
    FROM reviews r
    WHERE r.score > 3
)
SELECT r1.userid,
       r2.userid,
       GROUP_CONCAT(r1.productid SEPARATOR ' ')       
FROM       cte r1
INNER JOIN cte r2
        ON r1.productid = r2.productid
       AND r1.userid < r2.userid
GROUP BY r1.userid,
         r2.userid
HAVING COUNT(*) >= 3

It uses a Common Table Expression that allows you to allocate your table in a temporary space that lasts till the end of the query. Your next step using the self join is correct, though it lacks the GROUP_CONCAT aggregation function, that allows you to aggregate on a string-like field. You can set the "separator" parameter to decide which string you want to use to concatenate your values.

Try the full query here.