title: Creating matrix in bigquery efficiently
body: <p>My input table looks like this with key and value columns</p>
  <p><a href="https://i.stack.imgur.com/KEalF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KEalF.png" alt="input_table" /></a></p>
  <p>Step 1 : I want to construct a table which looks like matrix, with column key as a column and column headers, with values as count of overlapping/intersecting values</p>
  <p><a href="https://i.stack.imgur.com/XRg8c.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XRg8c.png" alt="matrix_table" /></a></p>
  <p>Step 2 : Another matrix like this,</p>
  <p><a href="https://i.stack.imgur.com/uwV9j.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uwV9j.png" alt="distance_matrix" /></a></p>
  <p>To explain a cell [x2,x1], the value is &quot;yes&quot; because it satisfies both the below conditions,</p>
  <ol>
  <li>(count of x2 values, which is 5) - (value in the previous matrix in the same position [x2,x1], which is 3 in this example) &lt;=3</li>
  <li>(count of x1 values, which is 5) - (value in the previous matrix in the same position [x2,x1], which is 3 in this example) &lt;=3</li>
  </ol>
  <p>To explain a cell [x3,x1], the value is &quot;no&quot; because it does not satisfy both the below conditions,</p>
  <ol>
  <li>(count of x3 values, which is 7) - (value in the previous matrix in the same position [x3,x1], which is 1 in this example) &lt;=3</li>
  <li>(count of x1 values, which is 5) - (value in the previous matrix in the same position [x3,x1], which is 1 in this example) &lt;=3</li>
  </ol>
  <p>Step 3: Output table should be the ones with &quot;yes&quot;</p>
  <p><a href="https://i.stack.imgur.com/LoX1d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LoX1d.png" alt="enter image description here" /></a></p>
  <p>What is the most efficient way to do this in google bigquery, I am exploring arrays, any thoughts?</p>