SQL Interview Query for Data Analyst

In this video, let's solve a real SQL interview query asked by a Data Analytics firm for a Data Analyst Intern role.
We are given a problem statement with data provided in 4 csv file. We need to analyze the given data and then solve the problem using simple SQL concepts.

Download the dataset and scripts from my blog below:

Timestamp
00:00 Intro
00:18 Understanding the problem statement and input data
04:03 Skillshare promo
05:20 Understanding the requirement
07:28 Solution the given problem

🔴 My Recommended courses 👇

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

✅ Git and GitHub Tutorial:

✅ Data Analytics Projects:

FTC disclaimer: This video was sponsored by Skillshare.

THANK YOU,
Thoufiq

[dj_oteng]

Your tips on SQL has really improved my scripting skills in my line of work. Very practical examples. Thank you for the good work

[luckykumar]

at 17:54 no need to add sr.option_marked <> "e" since we already have sr.option_marked = ca.correct_option condition because "e" is available in ca.correct_option table.

[deepakkumars]

The way you are analysing the problem 💯 💯🔥

[minhmuic]

thank, what i learn from your youtube channel is greater than 4 year in college which i need to pay around 50.000 usd for tuition fee :(

[kevinboodhoo]

with cteScore
as (
select r.roll_number
, r.question_paper_code
, sum(case when r.option_marked = c.correct_option then 1 else 0 end) as correct
, sum(case when r.option_marked not in ('e') and r.option_marked <> c.correct_option then 1 else 0 end) as wrong
, sum(case when r.option_marked = 'e' then 1 else 0 end) as yet_learn
from student_response as r
inner join correct_answers as c on r.question_paper_code = c.question_paper_code
and r.question_number = c.question_number
group by r.roll_number, r.question_paper_code
)
, cteTotalScore
as (
select s.roll_number
, q.[subject]
, s.correct
, s.wrong
, s.yet_learn
from cteScore as s
inner join question_paper_code as q on s.question_paper_code = q.paper_code
)
, cteCorrect
as (
select roll_number, [Math], [Science]
from (
select roll_number, [subject], correct
from cteTotalScore
) as s
pivot
(
max(correct) for [subject] in ([Math], [Science])
) as x
)
, cteWrong
as (
select roll_number, [Math], [Science]
from (
select roll_number, [subject], wrong
from cteTotalScore
) as s
pivot
(
max(wrong) for [subject] in ([Math], [Science])
) as x
)
, cteYetLearn
as (
select roll_number, [Math], [Science]
from (
select roll_number, [subject], yet_learn
from cteTotalScore
) as s
pivot
(
max(yet_learn) for [subject] in ([Math], [Science])
) as x
)
select c.Roll_number
, s.Student_name
, s.Class
, s.Section
, s.School_name
, c.Math as Math_correct
, w.Math as Math_wrong
, l.Math as Math_yet_to_learn
, c.Math as Math_score
, as decimal(19, 2)) as Math_percentage
, c.Science as Science_correct
, w.Science as Science_wrong
, l.Science as Science_yet_learn
, c.Science as Science_score
, as decimal(19, 2)) as Science_percentage
from cteCorrect as c
inner join cteWrong as w on c.roll_number = w.roll_number
inner join cteYetLearn as l on c.roll_number = l.roll_number
inner join student_list as s on c.roll_number = s.roll_number
--where c.roll_number in (10159, 10114, 10215, 10052, 10201)
order by roll_number;

[VishalSingh-xfbh]

The way you have explained the concept is solutable boss thanks for the video

[vishalsonawane.]

Your video content always it's like new gift for all learners 🏆❣️😍

[hyderali]

Toufiq bro thanks to you for making this format of video which is even understood well by beginners also. keep updating us in this way...we don't even need to join in any other classes....😜

[rajkumarrajan]

You are my virtual master in learning SQL.
You are AMAZING my dear friend!! Thanks a lot

[namanmehta]

Bhai hats off for the patience and pace you took to solve this problem. Really man🎉 impressive

[rohitsethi]

create table student_list
(
roll_number int,
student_name varchar(50),
class int,
section varchar(2),
school_name varchar(40)
)


create table correct_answer
( question_paper_code int ,
question_number int,
correct_option varchar(1)
)

create table student_response
(
roll_number int,
question_paper_code int,
question_number int,
option_marked varchar(1)
)



create table question_paper_code
( paper_code int,
class int,
Subject varchar(10)
)


use etl to store data from excel file

[prathameshjoshi]

This was really an informative video. Thank you so much

[mohammedvahid]

well explained sir thnk u so mch its very usefull to me with realtime scenarios...

[harichandrajaiswal]

I am glad that... This problem is shared with all the people.. U helped me out of this. Thank you so much 🙏 🙏

[nagaprasadreddy]

It can't be better than this. No words!

[sungkeum]

Thank you for sharing knowledge as always, Thoufiq.

[avi]

Detailed process and explanation
Thanks a lot sir!

[luckykumar]

At 26:34, we can assume ((math_correct * 1) - (math_wrong * 0.25) ) as math_Score.

[kalheer]

Awesome clarification - ive not used “cte” before.. makes life easier 👍🏻👍🏻

[Kishor-ai]

Thanks for making this video @techTFQ ❤