Check a set of items within a table and cross with another structure

In my store I can sell items together (packages with a predefined list of products). However, on the website, the system only shows me the product codes separately, it doesn't tell me when the sale was TOGETHER (in a package format).

I have a reference table where it shows me the product codes and the quantity that make up a certain package (which I call set_products):

WITH ReferenceTable AS (
  SELECT 'set123' AS set_products, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 'set123' AS set_products, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemZ' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemY' AS product_id, 1 AS quantity
)
SELECT * FROM ReferenceTable

In this example I know that package 'set123' is composed of 2 products with code 'itemA' plus 1 product with code 'itemB'. And the package 'set456' is composed of 1 product with code 'ItemZ' plus 1 product with code 'ItemY'.

In my sales table, I have the ID of the ticket (transaction) and the list of products sold within each one.

WITH MyTable AS (
  SELECT 153612 AS ticket_id, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemZ' AS product_id, 3 AS quantity UNION ALL

  SELECT 542652 AS ticket_id, 'itemA' AS product_id, 1 AS quantity UNION ALL
  SELECT 542652 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  
  SELECT 625167 AS ticket_id, 'itemA' AS product_id, 4 AS quantity UNION ALL
  SELECT 625167 AS ticket_id, 'itemB' AS product_id, 2 AS quantity
)
SELECT * FROM MyTable

In this example I have 3 different transactions. The first one (153612) sold the product set (2x ItemA 1x ItemB) but had an additional item sold individually (ItemZ). In this case the expected output would be the package ID on the first two rows, but not on the third.

The second transaction (542652) has the two items that make up the package, but the quantity is not the same, so the expected output is NULL.

And finally the third transaction (625167) which is the sale of exactly 2 complete packages (these cases seem to be a little more complicated to identify).

I'm trying to identify these cases within a huge table, where the expected output is the product set code identification. Something like this:

I'm using BigQuery but I accept suggestions in any other SQL. Thanks a lot!

CodePudding user response：

You can compare max/min ratio of the package products in a ticket to get relevant package / ticket pairs. Not sure what you want if a product in a ticket takes part in more than one relevant package.

WITH ReferenceTable AS (
  SELECT 'set123' AS set_products, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 'set123' AS set_products, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemZ' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemY' AS product_id, 1 AS quantity ),

),
MyTable AS (
  SELECT 153612 AS ticket_id, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemZ' AS product_id, 3 AS quantity UNION ALL

  SELECT 542652 AS ticket_id, 'itemA' AS product_id, 1 AS quantity UNION ALL
  SELECT 542652 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  
  SELECT 625167 AS ticket_id, 'itemA' AS product_id, 4 AS quantity UNION ALL
  SELECT 625167 AS ticket_id, 'itemB' AS product_id, 2 AS quantity
)
SELECT rt.set_products, mt.ticket_id, max(mt.quantity * 1.0 / rt.quantity) nmbrOfPkgs
from ReferenceTable rt
left join MyTable mt on mt.product_id = rt.product_id
group by mt.ticket_id, rt.set_products
having max(mt.quantity * 1.0 / rt.quantity) = min(mt.quantity * 1.0 / rt.quantity)
   and count(*) = (select count(*) 
              from ReferenceTable rt1  
              where rt1.set_products = rt.set_products)