Learn how to write SQL Queries(Practice Complex SQL Queries)

Learn how to write SQL Queries(Practice Complex SQL Queries)
In this video, I will explain the thinking behind writing an SQL query. We shall go through five different complex SQL queries and try to solve it. The intention is to practice complex SQL queries. The idea is to not just write SQL Queries but also to explain you the thinking behind how to choose a particular approach to solve a SQL query and how to choose different SQL functions or concepts to solve a problem.

Link to my blog where I have posted all of these SQL Queries:

In this blog, you will find 9 SQL queries along with all the scripts to create the table, load data into these tables and also the solved SQL Queries. Out of these 9 SQL Queries, In this video, I have only solved 5 of them in order to reduce the video length.

Timeline:
00:00 Intro
01:36 Intro about the blog
03:25 SQL Query to fetch duplicate records from a table using window function
09:27 SQL Query to fetch doctors working in same hospital using self join
15:55 SQL Query to fetch users who logged in consecutively 3 or more times
25:10 SQL Query to fetch days when the temperature was very cold for consecutively 3 or more days
36:13 SQL Query to fetch top 2 accounts with maximum patients per month

Learning SQL Concepts and practically writing SQL queries are two different things. You may be familiar with the concept such as window function but until and unless you apply these concepts to write SQL queries, you will never truly understand SQL.

May of the queries I am going to solve in this video, I will be using window function such as LEAD, LAG, RANK, ROW_NUMBER etc. However, some of the queries are solved using self join, sub queries or inner joins.

The idea behind making this video, is to explain the thinking behind solving an SQL question. How to read the question and understand the problem statement and then analyze the existing data so that we can come up with an approach to write a query which can return the required result. These steps are very important in order for us to determine the different SQL concepts or functions to be used to solve the problem.

Please comment below your feedback if you liked this kind of video or if you have any doubts on any other queries mentioned in the blog.

🔴 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:

THANK YOU,
Thoufiq

[magicmedia]

Who else thought that they knew SQL until they came across this and then realized that they have a long way to go?😅

[presidentbrown]

After going through this I realised how much we can do with SQL. I thought I knew SQL but now I know there's a lot to learn.

[mounikakondrapu]

I have cleared one of my interviews with top MNC company because of this training video. Thank you so much.

[konstantindemchenko]

15:27 the first condition d1.id <> d2.id is redundant since its more narrow than d1.speciality <> d2.speciality

[mohammedidris]

Its not at all a tutorial video its a master or an expert explaining. You dont explain the approach or idea you just make everything clear in all the concepts you explain.
Awesome

[mrmuranga]

Anyone starting out or wishing to improve there SQL, this is definitely for us. Great job👏👏👏

[mdsaidulislamsayed]

you are so well spoken! no unnecessary talk, straight to the point, in depth explanation!

[rodneyoganga]

I wish i could be as good as you at SQL, am struggling, God help me, thank you for such a well explained tutorial with amazing dedicastion.

[yrrep]

Just want to say first, this channel is invaluable.

Thought I'd offer up a solution for problem 7 that's a little more concise. This assumes that as in the example data, consecutive increasing id numbers correspond to consecutive increasing dates, with each id number corresponding to a unique day.
The approach is:
1. Remove records with temperatures above 0 using a WHERE statement.
2. Of the remaining rows check each one to see if it is part of a consecutive triplet-- we could use the date column for this with a specialized date function, but it's easier to use the id column, if the assumption stated above is true
3. A row in a consecutive triplet will meet one of three criteria: Either A-- the two leading rows have consecutive id numbers, B. The lagging row and the leading row both have a consecutive id, or C. The two lagging rows have consecutive ids.
4. The CASE statement checking for these criteria are then packaged in a subquery returning just the id of qualifying rows.
5. The main query uses IN to return only rows having the ids returned by the subquery.

SELECT *
FROM weather
WHERE id IN
(SELECT
CASE
WHEN id + 1 = (LEAD(id) OVER ()) AND id + 2 = (LEAD(id, 2) OVER ())
OR id - 1 = (LAG(id) OVER ()) AND id + 1 = (LEAD(id) OVER ())
OR id - 1 = (LAG(id) OVER ()) AND id - 2 = (LAG(id, 2) OVER ())
THEN id
END as check
FROM weather
WHERE temperature < 0)

[sreerams]

Hi, Thank you for your time and effort. Please continue these complex queries as a series. Thank you once again, i am getting a hold on sql after watching your videos especially on CTE's and sub queries which plays a major role in getting required outcome.

[fathimafarahna]

Nothing like your contents on this whole YouTube. Genuinely appreciate your efforts in churning out such beneficial n functional contents🙏Also, the blog is just amazing. Clean, clear, authentic. Kudos to your efforts n intention👏🙌

[NitinKumar-vwgb]

Thank you so much sir, I watched your video and able to solve multiple query in interview, now I'm selected 😊 as software developer trainee

[laurak]

I’ve finally watched the whole thing and this is definitely my favorite kind of your videos. I learn the most by watching you and hearing your thought process and the nice bonus is then I can go and download your script, create an identical table and then go try and do it myself. That’s where I learn the most, bc I can try my own ideas and just make little changes and see if that completely throws an error and then I try and understand why that error was thrown so I can avoid that problem in the future. Thank you again for your help and your time investment. Over 113K views on here shows I am not the only one this is helping. 🙏🏻 Many blessings to you and your family, TFQ

[swatisingh-vsbx]

This is by far the best video for understanding SQL queries, the way you have explained by running small parts first and than solving to get the end output and also the pace was very much decent. Thanks man ! and please continue to make more of such videos :)

[vijaypalmanit]

superb, had been looking for someone for very long who teaches complex queries in simplest manner, finally search ends here.

[SagarKumar-dbxy]

Watched tons of SQL contents, this is by far best and quality content. Keep adding more. Please also share the SQL for entry level data analyst

[abdullahmusheer]

Every other SQL tutorial youtuber just shares the basics which is good, but going through these examples and approach is really well thought of.. Appreciate the work! Thank you

[amsalespush]

So far, the best video I have found on complex SQL. Great examples of practical value, very well explained. Thanks a lot.

[fathimafarahna]

1M views… Masha Allaah… You deserve much more.. Keep growing n benefiting 👍👍👍

[marciojrtorres]

Thank you Thoufiq, I learned a lot from the examples you've provided. Greetings from Brazil 👋