How to make the combination of products that a client has in SQL

I have a table thar contains many row with clients and produts that they have.
It is like that:

How can a write a query to get that resultrs in AWS Athena?
The Athena doesn't support procedure.

Here, we have the following combinations:

AB:  3 cases(clients 1, 2, 3)  
AC:  1 case (client 1)  
AD:  1 case (client 2)  
BC:  1 case (client 1)  
BD:  1 case(client 2)
CD:  0 cases 
ABC: 1 cases (client 1)
ABD: 1 case (client 2)

I have a query that get the combination of AB but to get the other I need to change de filters (WHERE) and run N times. If I have hundred of combinations it is unfeasible to do.

You can try using combinations function if it is available in Athena (it is present in Trino on which Athena is based upon):

WITH dataset (Client, Product) AS (
    values (1,	'A'),
    (1,	'B'),
    (1,	'C'),
    (2,	'A'),
    (2,	'B'),
    (2,	'D'),
    (3,	'A'),
    (3,	'B')
)


select p.comb, count(Client) count
from (select comb
      from (select array_distinct(array_agg(Product)) arr
            from dataset) p,
           unnest(combinations(arr, 2) || combinations(arr, 3)) as t(comb)) p -- build all product combinations
left join (select client, comb
           from (select client, array_distinct(array_agg(Product)) as arr
                 from dataset
                 group by client),
                unnest(combinations(arr, 2) || combinations(arr, 3)) as t(comb)) cp -- build all product combinations for concrete client 
          on cp.comb = p.comb
group by p.comb
order by cardinality(p.comb), p.comb;

Output:

Note that combinations is limited to only 100000 records in output, so if actual number of product combination is higher this will not work (also either way this would be quite expensive query on any relatively big number of products).

Based on what you provided, I understood that you want compute all possible combinations of products per client in Amazon Athena. This is a combinatorial problem and can be approached using self-joins. However, this kind of solutions becomes computationally expensive for large numbers of unique products, as the number of self-joins would grow exponentially with the number of products you want to combine.

For 2 products :

WITH combinations_2 AS (
  SELECT a.client AS client, 
         a.product AS product1, 
         b.product AS product2
  FROM your_table_name a
  JOIN your_table_name b
  ON a.client = b.client
  WHERE a.product < b.product
)
SELECT product1, product2, COUNT(DISTINCT client) AS num_clients
FROM combinations_2
GROUP BY product1, product2;

And for 3 products:

WITH combinations_3 AS (
  SELECT a.client AS client, 
         a.product AS product1, 
         b.product AS product2,
         c.product AS product3
  FROM your_table_name a
  JOIN your_table_name b
  ON a.client = b.client
  JOIN your_table_name c
  ON a.client = c.client
  WHERE a.product < b.product 
  AND b.product < c.product
)
SELECT product1, product2, product3, COUNT(DISTINCT client) AS num_clients
FROM combinations_3
GROUP BY product1, product2, product3;

AWS Athena which don't have the support for stored procedures or user-defined functions to handle recursion or loops, expressing combinations of an arbitrary size is quite challenging. There's no simple, efficient way to generate all combinations of an unknown number of products using pure SQL.

I don't know much about Athena, but I though I'd throw an example of how I might approach that using STRING_AGG and CTE's, just in case it helps.

WITH prod_mix AS (
  SELECT client,
  STRING_AGG(product,'' ORDER BY product) as pmix
 FROM client_product
 GROUP BY client
  ),
combined AS (
  SELECT A.*, B.pmix
  FROM client_product A
  INNER JOIN prod_mix B
  ON A.client = B.client
  ) , 
 client_mix AS (
    SELECT pmix,
    STRING_AGG(DISTINCT client,'' ORDER BY client) AS cmix
   FROM combined
   GROUP BY pmix
 )
 SELECT * FROM client_mix;