BigQuery: SQL Query to merge Aggregated data with unique data

Question

I have a small Dataset as shown below:

The Computed Points computation is as follows:

Numerator = Score/SUM(Score) Denomenator = IF(Points = 0 SELECT Score ELSE SELECT Points)/SUM(Score)

Computed Points = Numerator/Denomenator

How to I write a Sql query for the same in BigQuery? Here is how my query looks like:

Select Date, Activity, Model (Score/Sum(Score))/(CASE Points 
                                              WHEN 0 THEN Score
                                              ELSE Points
                                              END)/(SUM(Points)) as `Computed_Points` from samples.test;

But this throws an error saying:

SELECT list expression references column Impressions which is neither grouped nor aggregated at [1:9]

Can someone please help me achieve this?

Answer 1

You want to use sum() as a window function ... sth like

Select Date, Activity, Model (Score/Sum(Score) OVER ())/(CASE Points 
                                              WHEN 0 THEN Score
                                              ELSE Points
                                              END)/(SUM(Points) OVER ()) as `Computed_Points` 
from samples.test;

OVER () means that the window is unbounded, over the whole table

see https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts

Answer 2

Below is for BigQuery Standard SQL and is a cleaned up version of previous answer

#standardSQL
select Date, Activity, Model, Score, Points, 
  if(Points = 0, 1, Score / Points)
  * sum(Points) over() / sum(Score) over() as `Computed_Points`  
from `samples.test`   

when applied to sample data from question output is