I have a table that looks like this in Google Big Query:
A B C D ID RUN Date Cost 1 1 3/19/2017 $ 178,827 1 2 5/4/2017 $ 202,691 1 3 4/2/2017 $ 206,537 1 4 3/27/2017 $ 157,909 1 5 3/26/2017 $ 201,743 1 6 4/3/2017 $ 187,651 1 7 4/7/2017 $ 193,278 1 8 3/26/2017 $ 176,151 1 9 4/11/2017 $ 184,132 2 1 3/28/2017 $ 153,991 2 2 3/12/2017 $ 174,312 2 3 3/19/2017 $ 174,634 2 4 4/3/2017 $ 155,554 2 5 4/3/2017 $ 162,906 2 6 4/8/2017 $ 177,955 2 7 5/10/2017 $ 205,902 2 8 4/11/2017 $ 194,955 2 9 4/9/2017 $ 182,976 2 10 5/5/2017 $ 197,049 what i am looking for is a query that will return the “JCL” column like below. the calculation for the JCL column is a countifs function that will return a count partitioned by the ID and on a condition that the Date is greater than or equal the row date and the cost is greater than or equal the row cost, value returned as a percentage of the total count of IDs in the partition.
the excel function would look like this:
JCL =COUNTIFS(A:A,A2,C:C,”>=”&C2,D:D,”>=”&D2) / COUNTIFS(A:A,A2)
A B C D E ID RUN Date Cost JCL 1 1 3/19/2017 $ 178,827 78% 1 2 5/4/2017 $ 202,691 11% 1 3 4/2/2017 $ 206,537 11% 1 4 3/27/2017 $ 157,909 67% 1 5 3/26/2017 $ 201,743 33% 1 6 4/3/2017 $ 187,651 33% 1 7 4/7/2017 $ 193,278 22% 1 8 3/26/2017 $ 176,151 78% 1 9 4/11/2017 $ 184,132 22% 2 1 3/28/2017 $ 153,991 80% 2 2 3/12/2017 $ 174,312 70% 2 3 3/19/2017 $ 174,634 60% 2 4 4/3/2017 $ 155,554 70% 2 5 4/3/2017 $ 162,906 60% 2 6 4/8/2017 $ 177,955 50% 2 7 5/10/2017 $ 205,902 10% 2 8 4/11/2017 $ 194,955 30% 2 9 4/9/2017 $ 182,976 40% 2 10 5/5/2017 $ 197,049 20% can i use a subquery in bigquery to perform this calculation?
