SQL Interview Query for Data Analyst

TL;DR

Analyzing a SQL interview problem involving calculating student scores and percentages for a math and science Olympiad exam.

Transcript

hey everyone welcome back in this video let's try to solve one more real SQL interview problem this time the problem was given to me by Hari Chandra he faced this problem when he was giving an internship interview process in one of the data analytics firm so straight away let's look at the problem and try to solve it so as you can see this was a pr... Read More

Key Insights

• 💯 The problem involves joining and aggregating data from multiple tables in order to calculate student scores and percentages.
• 🧑‍🎓 The tables provided include student list, student response, correct answers, and question paper code.
• ⁉️ The solution requires filtering records based on subject and option marked, as well as counting correct answers and total questions.
• 🧑‍🎓 The solution utilizes case statements and joins to compare student responses with correct answers.

Questions & Answers

Q: What is the objective of the problem?

The objective is to validate student responses for a math and science Olympiad exam and present the scores and percentages in a single table using SQL.

Q: How are the correct answers determined?

The correct answers are provided in the correct answers table for all math and science questions.

Q: What does the "yet to learn" column represent?

The "yet to learn" column indicates the number of questions for which the student chose the option "E," indicating they have not learned the concept yet.

Q: How is the math score calculated?

The math score is calculated by summing the number of correct answers for each student.

Q: How is the math percentage calculated?

The math percentage is calculated by dividing the number of correct math answers by the total number of math questions and multiplying by 100.

Q: How is the total number of math questions determined?

The total number of math questions is counted by summing the instances where the subject is "math."

Q: How is the science score calculated?

The science score is calculated by summing the number of correct answers for each student in the science subject.

Q: How is the science percentage calculated?

The science percentage is calculated by dividing the number of correct science answers by the total number of science questions and multiplying by 100.

Key Insights:

• The problem involves joining and aggregating data from multiple tables in order to calculate student scores and percentages.
• The tables provided include student list, student response, correct answers, and question paper code.
• The solution requires filtering records based on subject and option marked, as well as counting correct answers and total questions.
• The solution utilizes case statements and joins to compare student responses with correct answers.
• The final output is a table that displays the roll number, student name, class, section, school name, math and science scores, and percentages for each student.

Summary & Key Takeaways

• The video discusses a SQL interview problem involving calculating scores and percentages for a math and science Olympiad exam.

• Four input tables are provided: student list, student response, correct answers, and question paper code.

• The problem requires joining and aggregating data from these tables to calculate scores and percentages for each student in math and science.