The Test_data_dump is the main pool of data that contains data of the students who've attempted various tests. The rest are all lookup tables all of which have unique rows. Assignee_id refers to student ID, whereas Assessment_ID refer to the test_ID's. However they're not the required ID's for the analysis. The required ID's (PID and Test Title) are circled in the lookup tables PID_LOOKUP and TEST_ID_LOOKUP.
An ideal attendance pivot table for me would have Test Titles in the rows, with 'CMS Batch ID's in subrows. The values would be a count(PID's)/'Batch count' to get an attendance of how many students attempted the test out of the total possible value from that specific batch for that test.
How to achieve this output?
