3 SQL Queries Asked in Interview for Business Analyst - Solved
ฝัง
- เผยแพร่เมื่อ 22 ก.ค. 2024
- In this video, we shall solve 3 Intermediate level SQL Queries that were asked during an interview for the role of Business Analyst.
We shall first understand the problem statement and then solve it one by one.
You can download the dataset and SQL queries from my blog below:
techtfq.com/blog/solving-sql-...
Timestamp:
00:00 Intro
00:17 First SQL Query
09:48 Second SQL Query
23:33 Third SQL Query
Join my live Python Bootcamp:
techtfq.graphy.com/courses/Py...
Join my SQL Bootcamp:
techtfq.graphy.com/courses/Re...
Thanks for watching!
Thoufiq (techTFQ)
![[TH] 2024 PMWC x EWC Group Stage Day 3 | PUBG MOBILE WORLD CUP x ESPORTS WORLD CUP](http://i.ytimg.com/vi/VnOqutfqWqU/mqdefault.jpg)
hands down!! what a genius you are ! specially the third problem and the explaination just wow,,, taufiq sir because of you , i have cleared many concepts of sql...may god bless you taufiq
No one can make SQL look so easy! Totally worth learning SQL from your recent boot camp cohort 2. The way you approach and solve complex queries by breaking them down into smaller parts was the most important thing I learned from you! ♥
Keep bringing such content, this is what raises my confidence even more in SQL!! 😅🙌
Excellent explanation!!💯 Thankyou for making our life easy. God bless you!
Greetings brother, from Singapore. As a working adult who is doing self-study, such exercises with step-by-step solutions are extremely priceless! Thank you Taufiq.
The way of approach is easy to understand and follow , Thank you so much.
It's really awesome explanation and this is the best SQL channel i have ever got,thanks a lot for sharing the knowledge. Looking forward for the more videos with SQL examples.
Thank you Toufiq bhai, the way that you explained queries is so good and i hope who don't have any knowledge on sql they will easily to understand
Hi Thoufiq, really loved your videos and learned a lot from you. It will be beneficial if you can share the Cheat Sheet of SQL syntaxes.
Query 3 ,MSSQL
with echo as (select state,candidate_id,count(candidate_id) as seatcount_byid from results_tab
group by state,candidate_id),
rt as (
select *,dense_rank() over (partition by candidate_id order by seatcount_byid desc) as position from echo),
final1 as (select * from rt
where position
Explaining step by step clearly. Good Video 😊
Toufiq bhai what explains yaar . Everyone should understand what you want to give us from this video.great Bhai
Thank you so much bro 😀
Such great SQL content videos! I can always learn things from your videos. Shoutout to techTFQ!
Happy to hear that! Thank you so much
Bro u r a gem... Keep doing many more contents. This help me understand to solve the queries in a more better way. As I have also attempted these queries but failed to solve it. These are from hacker rank got it in a meesho 1st round. These video help me understand much better. Thanks a lot broo...
Hi, toufiq your videos on SQL are very good and your explanation is very detailed. It would be helpful if you post more content on SQL like this and share some roadmaps how we can grow with SQL as primary skill.
Please suggest some good certification courses for SQL that are available online
❤good one
Super useful. I’m relating everything I learn about SQL to New Relic which is what my company uses for observability.
Thank you very much 😊
1st question my way of approach
with cte as(
select * from candidate c
inner join results r
on c.id=r.candidate_id)
select party,count(*) as no_of_seats from(
select *,dense_rank()over (partition by constituency_id order by votes desc) as rn from cte ) dt
where rn=1 group by party
The third one was the most challenging for me to get my head around.
You are absolutely amazing sir!!!!
Thanks for efforts. Your content is very useful 🥰
After a week of watching your lessons, I was able to solve these queries for myself, thanks a lot.
Great 👍
Thanks for awesome session.
I understand the interview questions. What I don't understand is how that political query would apply to the job. I have a friend that works in Soccer, and he's asked to query seat sales by seat type or unsold or whatever, and sends a spreadsheet with the answer.
Are these interview questions like just practice sandbox type queries?
Thanks for educating us.
Hi @techTFQ in the second query you have use group by clause on alias customer and status, but as per execution order group by is executed before select, so when I tried running query you explained it throws me error invalid identifier, Cn you please let me know what to do. I am using Oracle SQL Developer
Wow, you are the best🎉
You are the best tech teacher
Hi I am a professional accountant, and do loads of business analysis, reporting etc in Excel. I would like to learn Business SQL for Data Analytics, kindly share the road map and learning source. Thanks
Thanks Man..
very very useful..
very easy to understand
Big Thx......
You are an excellent teacher.
Hi toufiq really hats off to your afforts and explanation.plz start an exclusive course on data analytics.
Thank you bro, I will try my best
First one my approach
with cte as(select *,
min(votes) over (partition by constituency_id) as lowest_vote
from result),
cte2 as(
select *,
(votes-lowest_vote)as vote_diff
from cte),
cte3 as (
select *,
row_number() over (partition by constituency_id order by vote_diff desc) as rnk
from cte2)
select concat(c.party,' ',count(*)) as party_seats from cte3
join candidate c
on cte3.candidate_id=c.d
where rnk=1
group by c.party
Amazing stuff. Really enjoyed it and learned a lot. 5 stars!
Thank you
Hi Sir
Please do one video for how to retrieve specific data from XML column in Oracle SQL
brilliant explanation
Oh great video and insightful
Upload more video about stored procedure and udf functions with exception handling
Thank you and noted bro
Dear sir ,when your next SQL live bootcamp start?eagerly waiting for this
Great video ❤
Thanks !
Glad you liked it!
this is not intermediate level but yet it is very useful there are so many things to get from this video
good to know
what level is this then?
@@vishalgoswami7512i would say it’s advanced
Excellento.
In SQL Server string_agg not supporting distinct then how to solve 2nd one
You are as always best 💯 God bless
Thank you so much 😀
08:48 I did not understand why we used count(1). Could you help me understand?
You are Amazing
why have you removed order by clause within cte? in 3rd query? please anyone explain
This is a great video. I can only imagine how it is to solve if you had to wtite this on a simple paper as a candidate
Thanks:)
Here is the query with out window function : Select concat(party,' ',count(*)) from (select constituency_id,max(votes)as votes from candidates c inner join results r on c.id=r.candidate_id group by constituency_id)as temp inner join results r on temp.constituency_id=r.constituency_id and temp.votes=r.votes inner join candidates c on r.candidate_id=c.id group by party;
Ur the best❤
Super bro
Do you have any idea of starting PlSql bootcamp?
Thank you so much
@TechTFQ: This question is for which company? In India or North America?
Hi taufiq i want to buy sql course but payment portal is not able to accept my card
itrat ali from NJ USA
with cte as
(select c.id, c.party,r.constituency_id,r.votes
from candidates as c
inner join
results as r on r.c_id=c.id),
cte1 as(
select *
, rank() over (partition by constituency_id order by constituency_id, votes desc) as rn
from cte)
select party, count(1) as won_seat
from cte1
where rn=1
group by party
Can you please share next boot camp slots for SQL
thank you
Thanks for this!
You're welcome
how to do string_agg fcn in mysql ?
Hi sir string_agg function in postgress but i am using mssql server i could not find the aternative function for it
try going with group_concat()
Why did we join when we already used cte?
will using an order by in a cte slow down query performance? or will it just be ignored
order by is unnecessary inside a cte because the purpose of cte is to return a resultset, how the resultset is ordered is not necessary. And yes order by will consume some resources so better to avoid it when its not required
Thanks ❤
You're welcome 😊
My age is 32 can I enter into data analyst job if I acquire skills required for this job role
select * from(select c.party, count(c.party), rank() over(partition by r.constituency_id order by votes desc) as rnk from candidates c join results r on c.id=r.candidate_id group by c.party) x where x.rnk=1; is this good?
Hi,Can you make some sql interview questions for data engineers.
let me see if I can find any
Hi which software are you using to write this SQL code?
PostgreSQL DB, PG Admin tool
Hi Taufiq, I am your fan ! You have become the ultimate destination to clear doubts & concept. I want to share 2 sql problems which I couldn't solve in interview. How should I share ? Can't find your email in this chat .
Thank you 🙏
Glad to hear that .. my email techtfq@gmail.com
String_arg() inside distinct is not a valid in ms sql please give me alternative code😢 19:32 also the order by inside strinģ arg 35:48
Plsql bootcamp please
Can we use count(*) instead of count(1) in my sql?
Same question. Did you find any solution?
S man.. It is good
Thank you
Please do more interview questions on mysql
Noted
I am seriously gonna laugh at people if they opt for any paid course of SQL even after coming across your channel.
Hands down I would choose you over anyone to learn SQL from.
I know this is from hacrkrank interview questions for ETL Testing I faces this all 3
nice, good to know
Which company bro ?
@@sarvesht7299meesho
I know these are the basic qns but i need proper queries plz ...( if anybody see my mes... u can also rply )
Hai....sir.... please write the querys 1) how to find highest salary of the employee?
2) query to find 2nd ,3rd,4th ....higest salaries?
And query to find nth highest salary?
use rank over order by salary
@@vishalgoswami7512 if u don't mind....can you write?
Sir please make the video in Hindi because Hindi is familiar for us
with cte as
(select row_number() over(partition by party ) rn, c.id, party, constituency_id, votes
from candidates c
join results r on r.candidate_id = c.id),
cte2 as
(select c1.id, c2.id, c1.party, c1.constituency_id,c2.constituency_id, c1.votes dvot, c2.votes rvot
from cte c1
join cte c2 on c1.constituency_id = c2.constituency_id and c1.rn = c2.rn and c1.party c2.party),
cte3 as
(select *,
case when dvot > rvot then 1 else 0 end flag
from cte2)
select concat(party, ' ', total) wons
from
(select party, sum(flag) total
from cte3
group by 1)
Appreciate it. @techTFQ