N1QL Query Help - I cannot for the life of me figure out how to do this. Details inside gist

SQL++
1

I think the gist describes it pretty well. I’m not even sure how I would word the question (if you have suggestions, let me know).

How would I perform this check? I’ve been able to unnest but I can’t get all the way toward tying things together and doing the comparison I’m trying to do. Any help is appreciated!!!

2 
SELECT t1.studentId, t1.nsubmitted , t2.ntestscores
FROM ( SELECT s.studentId,
              ARRAY_SUM(ARRAY 1 FOR v IN s.notifications WHEN v.type = "test_submitted" END) AS nsubmitted
       FROM default AS s
       WHERE s.entityType = "student" AND s.studentId IS NOT NULL) AS t1
UNNEST (SELECT t.studentId, COUNT(1) AS ntestscores
        FROM default AS t
        WHERE t.entityType = "test" AND t.studentId IS NOT NULL
        GROUP BY t.studentId) AS t2
WHERE t1.studentId = t2.studentId AND t1.nsubmitted != t2.ntestscores;
1 Like
3

This is looking excellent so far. Never would have figured this out on my own. Thank you!