Slicing with Where clause in BigQuery

Question

I am having an issue with my query, trying to calculate the change in score between terms and session and each time I try to slice by the session as shown in the query below, the change column returns 'null' in all rows but if I do remove the session slice, the change column returns the needed values.

ca table

name  id  subject session   term classroom ca_cat  score
one   1    maths  2018/2019   1    level1    1       10
one   1    maths  2018/2019   1    level1    2       6
two   2    maths  2018/2019   1    level1    1       9
two   2    maths  2018/2019   1    level1    2       7
one   1    maths  2018/2019   2    level1    1       9
one   1    maths  2018/2019   2    level1    2       8
two   2    maths  2018/2019   2    level1    1       7
two   2    maths  2018/2019   2    level1    2       5        

exam table

name    id    course session   term classroom    score
one     1      maths 2018/2019   1    level1      50
two     2      maths 2018/2019   1    level1      49 
one     1      maths 2018/2019   2    level1      50
two     2      maths 2018/2019   2    level1      50  

query

select
    studentid,
    name,
    subject,
    classroom,
    session,
    term,
    round(avg_score,0) as average_score,
    round(avg_score -  lag(avg_score, 1) over(partition by name, subject order by session) / 100*100, 0) as change,
from (
    select studentid, name, subject, classroom, session, term, avg(totalscore) over(partition by name, classroom, session, term) avg_score
    from (
    SELECT name, studentid, subject, classroom, session, term, caone, catwo, exam,
  caone + catwo + exam AS totalscore
FROM (
  SELECT name, studentid, subject, classroom, session, term, 
    MAX(IF(cacount = 1, ca.score, NULL)) AS caone,
    MAX(IF(cacount = 2, ca.score, NULL)) AS catwo,
    ANY_VALUE(ex.score) AS exam
  FROM `exam` ex
  JOIN `catable` ca
  USING (name, studentid, subject, classroom, session, term) 
  GROUP BY name, studentid, subject, classroom, session, term
)  
    ))
    where studentid=1 and session='2018/2019' and term=2
group by studentid, name, subject, classroom, session,term, avg_score

The expected output I meant to look like this

name, id, subject, classroom, session,    term, totalscore change
one    1    math    level1     2018/2019   1        66       null
one    1    math    level1     2018/2019   2        67        1 
two    2    math    level1     2018/2019   1        65       null
two    2    math    level1     2018/2019   2        62       -3 

The change in score is the difference between terms e.g 2nd-1st and 3rd-2nd

Is there a workaround around this? I have tried everything I know can't seem to find why it is responding this way or what the issue might be.

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
SELECT *, 
  totalscore - LAG(totalscore) OVER(PARTITION BY name, id, subject, classroom, session ORDER BY term) AS change
FROM (
  SELECT name, id, subject, classroom, session, term, caone + catwo + exam AS totalscore
  FROM (
    SELECT name, id, subject, classroom, session, term, 
      MAX(IF(ca_cat = 1, ca.score, NULL)) AS caone,
      MAX(IF(ca_cat = 2, ca.score, NULL)) AS catwo,
      ANY_VALUE(ex.score) AS exam,
    FROM `project.dataset.exam` ex
    JOIN `project.dataset.catable` ca
    USING (name, id, subject, classroom, session, term) 
    GROUP BY name, id, subject, classroom, session, term 
  )
)
-- ORDER BY name, id, subject, classroom, session, term   

when applied to sample data from your question - result is

Row name    id  subject classroom   session     term    totalscore  change   
1   one     1   maths   level1      2018/2019   1       66          null     
2   one     1   maths   level1      2018/2019   2       67          1    
3   two     2   maths   level1      2018/2019   1       65          null     
4   two     2   maths   level1      2018/2019   2       62          -3