BigQuery calculation to count how many unique combinations of 4 items there are across 6 columns

Question

I have the following table (I have covered up the current_holder column for privacy reasons):

For each row, I want to find the number of times we could pull 4 unique objects out of a bag (the objects being keys, skulls, gems, coins, daggers and crowns) without return. For example, in row 1 I could do this 6 times since once all of the skulls are taken out there are now only 3 unique objects left in the bag. For row 2 there is no way of pulling out 4 unique objects. For row 3 I could do this 5 times since after all the skulls are taken out there would still be one more combination of 4 unique objects left.

Does anyone know how I could calculate this number in BigQuery?

Answer 1

You could select the sum of the 3 lowest values.

• row 1: the lowest 3 are [0, 0, 6] so the sum is 6.
• row 2: [0, 0, 0] -> 0
• row 3: [0, 4, 5] -> 9

This query calculates that:

WITH
  table_1 AS (
  SELECT
    15 AS key,
    6 AS skull,
    34 AS gem,
    0 AS coin,
    0 AS dagger,
    12 AS crown,
    1 AS row_num
  UNION ALL
  SELECT
    5 AS key,
    0 AS skull,
    0 AS gem,
    0 AS coin,
    0 AS dagger,
    0 AS crown,
    2 AS row_num
  UNION ALL
  SELECT
    9 AS key,
    4 AS skull,
    12 AS gem,
    0 AS coin,
    5 AS dagger,
    5 AS crown,
    3 AS row_num),
  arrays AS (
  SELECT
    ARRAY(
    SELECT
      x
    FROM
      UNNEST([key, skull, gem, coin, dagger, crown]) AS x
    ORDER BY
      x
    LIMIT
      3) AS arr,
    row_num
  FROM
    table_1 )
SELECT
  SUM(x) AS num_unique,
  row_num
FROM
  arrays,
  UNNEST(arr) x
GROUP BY
  row_num;

(It is a little verbose...you could probably do this simpler but it works to calculate the number you are looking for.)