Calculate the overall attendance rate for each grade on each day?

Question

Given the following table schemas, how would you figure out the overall attendance rate for each grade on everyday?

Table 1: attendance_details

Column	Data Type	Description
date	string	date of log per student_id, format is 'yyyy-mm-dd' student_id
student_id	integer	id of the student
attendance_status	string	Possible values are ['present', 'absent']

Table 2: student_details

Column	Data Type	Description
student_id	integer	id of the student
grade_level	integer	will be a value between 0-12
date_of_birth	string	Student birth date, format is 'yyyy-mm-dd'

I have comeup with the below solution and it is working fine.

SELECT C.grade_level, C.date, CAST (D.present as float)/ C.TOTAL as attendance_rate
From 
(SELECT B.grade_level ,A.date, Count(*)  as TOTAL
from test.attendance_details A, test.student_demographic B
where A.student_id = B.student_id 
GROUP BY B.grade_level, A.date) as C,
(SELECT B.grade_level ,A.date, Count(*)  as present
from test.attendance_details A, test.student_details B
where A.student_id = B.student_id and attendance_status ='present'
GROUP BY B.grade_level, A.date) as D
WHERE C.grade_level = D.grade_level
and C.date = D.date 

Answer 1

Something like this should work:

SELECT SD.GRADE_LEVEL, AD.DATE
,SUM(CASE WHEN AD.ATTENDANCE_STATUS = 'present' THEN 1 ELSE 0 END) ATT_CNT
,COUNT(AD.STUDENT_ID)TOT_CNT
,ATT_CNT/TOT_CNT ATTENDANCE_RATE
FROM ATTENDANCE_DETAILS AD
INNER JOIN STUDENT_DETAILS SD ON AD.STUDENT_ID = SD.STUDENT_ID
GROUP BY SD.GRADE_LEVEL, AD.DATE