title: Can this sql query be written better
body: <p>I have got this SQL query and I was wondering if there's a better way to write the queries.</p>
  
  <p>The query below creates the first table and selects attributes where cacount=1, second_ca selects attribute where cacount= 2, exams select attributes from the exams table. Finally, create the total score table that combines the scores from first_ca and second_ca.</p>
  
  <pre><code>WITH
    first_ca AS (
    SELECT
      studentid,
      name,
      subject,
      classroom,
      session,
      term,
      cacount,
      score
    FROM
      analysis.catable
    WHERE
      cacount = 1 ),
    second_ca AS (
    SELECT
      studentid,
      name,
      subject,
      classroom,
      session,
      term,
      cacount,
      score
    FROM
      analysis.catable
    WHERE
      cacount = 2 ),
    exam AS (
    SELECT
      studentid,
      name,
      subject,
      classroom,
      session,
      term,
      score
    FROM
     analysis.exams ),
    total_scores AS (
    SELECT
      fca.studentid AS Studentid,
      fca.name AS Name,
      fca.subject AS Subject,
      fca.classroom AS Classroom,
      fca.term AS Term,
      fca.session AS Session,
      fca.score AS First_CA,
      sca.score AS Second_CA,
      ROUND(fca.score + sca.score, 2) AS Total_CA,
      ex.score AS Exam,
      ROUND(fca.score + sca.score + ex.score, 2) AS Total_Score
    FROM
      exam AS ex
    JOIN
      first_ca AS fca
    ON
      ex.studentid = fca.studentid
      AND ex.name = fca.name
      AND lower(ex.subject)=lower(fca.subject)
      AND ex.session=fca.session
      AND ex.term=fca.term
    JOIN
      second_ca AS sca
    ON
      ex.studentid = sca.studentid
      AND ex.name = sca.name
      AND lower(ex.subject)=lower(sca.subject)
      AND ex.session=sca.session
      AND ex.term=sca.term
    ORDER BY
      ex.classroom )
  
  </code></pre>
  
  <p>schema for both tables are </p>
  
  <pre><code>table_name:catable
  
  id          INTEGER NULLABLE    
  name        STRING  NULLABLE    
  studentid   INTEGER NULLABLE    
  score       FLOAT   NULLABLE    
  subject     STRING  NULLABLE    
  account     INTEGER NULLABLE    
  classroom   STRING  NULLABLE    
  term        INTEGER NULLABLE    
  session     STRING  NULLABLE
  
  table_name:exams
  
  id           INTEGER    NULLABLE    
  name         STRING     NULLABLE    
  studentid    INTEGER    NULLABLE    
  score        FLOAT      NULLABLE    
  subject      STRING     NULLABLE    
  classroom    STRING     NULLABLE    
  term         INTEGER    NULLABLE    
  session      STRING     NULLABLE    
  
  </code></pre>