Day #1: SQL Questions

I will try to document, my solutions of the leetcode SQL 50 study plan.

The question for today is https://leetcode.com/problems/students-and-examinations/description/?envType=study-plan-v2&envId=top-sql-50

The Problem

Where we are given 3 tables:

Table: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
student_id is the primary key         

?

Table: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key        

?

Table: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
There is no primary key. It may contain duplicates.        

?

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

The Intuition:

• As there's a reduction in the number of rows, the problem will be solved by group by not window functions mostly.
• Looking at the output we can see subjects that are not mentioned in the Examinations table are counted as 0 exams_attended. So a cross join needs to be implemented.

The Solution:

• Cross-join between Students and Subjects to produce all the combinations
• Left Join between the above table and Examinations to produce nulls in place of no matching entry in Examination Table
• If Clause is used to populate 1 where an exam record is found in the Examinations table otherwise 0
• Grouped by both student_id and subject_id to find exams given per student per subject. But choose carefully columns from cross-joined table to avoid nulls.

select re.student_id,re.student_name,re.subject_name, sum(re.attended) as attended_exams
from
(select 
s.student_id,
s.student_name,
sub.subject_name,
if(e.subject_name is null,0,1) as attended
from 
students s 
cross join
subjects sub
left join
examinations e
on s.student_id=e.student_id and sub.subject_name=e.subject_name
) re
group by re.student_id,re.subject_name
order by re.student_id,re.subject_name;        

The Optimized Solution:

• The count() function is smart enough to only increment for non-null values.

select 
s.student_id,
s.student_name,
sub.subject_name,
count(e.subject_name) as attended_exams
from 
students s 
cross join
subjects sub
left join
examinations e
on s.student_id=e.student_id and sub.subject_name=e.subject_name
group by s.student_id,sub.subject_name
order by s.student_id,sub.subject_name;