title: BigQuery: Weighted average
body: <p>Table:</p>
  
  <pre><code>| User_ID |  Red | Blue | Green |  Rating |
  |   a     |   23 |  33  |   42  |    99   |
  |   a     |   56 |  45  |   62  |    45   |
  |   a     |   23 |  49  |   28  |    67   |
  |   b     |   39 |  59  |   10  |    87   |
  |   b     |   18 |  28  |   59  |    38   |
  |   b     |   40 |  50  |   38  |    94   |
  </code></pre>
  
  <p>The result I want to obtain is a distinct row of user_id, with a weighted average of red, blue and green - based on the rating column. </p>
  
  <p>colour * rating/(sum of rating for a or b)</p>
  
  <p>//Edit </p>
  
  <p>Can't wrap my head around how to do this. Tried the following but it was a futile attempt </p>
  
  <pre><code>   WITH
        averages AS (
        SELECT
          User_ID,
          SUM(rating) AS average
        FROM
  `       project.dataset.table` 
        GROUP BY
          1)
      SELECT
        averages.User_ID,
        Red*(Rating/average),
        Blue*(rating/average),
        Green*(rating/average)
      FROM
        `project.dataset.table` a
      LEFT JOIN
        averages
      ON
        a.user_id = averages.user_id 
  </code></pre>