Complete SQL in One Video | Crack SQL Interview in 50 Qs | Leetcode Study Plan | SQL One Shot #sql

Want to crack SQL interviews? Check out our latest video!!!

A 50-questions SQL study plan to ace any interview.
This tutorial will walk you through the solution in easy steps. This is an easy-level question from Leetcode.

Consistency is what transforms average into excellence.
So be consistent & keep Coding💻!!

Chapters-
00:00 - Introduction
01:07 - Recyclable and Low Fat Products
05:08 - Find Customer Referee
10:03 - Big Countries
15:07 - Article Views I
19:48 - Invalid Tweets
23:20 - Replace Employee ID With The Unique Identifier
32:50 - Product Sales Analysis I
38:48 - Customer Who Visited but Did Not Make Any Transactions
47:03 - Rising Temperature
53:22 - Average Time of Process per Machine
01:01:48 - Employee Bonus
01:06:00 - Students and Examinations
01:13:42 - Managers with at Least 5 Direct Reports
01:20:01 - Confirmation Rate
01:28:52 - Not Boring Movies
01:20:01 - Average Selling Price
01:41:17 - Project Employees I
01:46:48 - Percentage of Users Attended a Contest
01:52:42 - Queries Quality and Percentage
01:59:44 - Monthly Transactions I
02:08:14 - Immediate Food Delivery II
02:17:40 - Game Play Analysis IV
02:26:41 - Number of Unique Subjects Taught by Each Teacher
02:29:35 - User Activity for the Past 30 Days I
02:35:21 - Product Sales Analysis III
02:40:46 - Classes More Than 5 Students
02:44:39 - Find Followers Count
02:47:52 - Biggest Single Number
02:48:53 - Customers Who Bought All Products
02:56:19 - The Number of Employees Which Report to Each Employee
03:06:31 - Primary Department for Each Employee
03:15:05 - Triangle Judgement
03:19:17 - Consecutive Numbers
03:25:28 - Product Price at a Given Date
03:35:58 - Last Person to Fit in the Bus
03:45:35 - Count Salary Categories
03:50:35 - Employees Whose Manager Left the Company
03:55:36 - Exchange Seats
04:03:00 - Movie Rating
04:13:16 - Restaurant Growth
04:23:49 - Friend Requests II: Who Has the Most Friends
04:31:53 - Investments in 2016
04:39:27 - Department Top Three Salaries
04:49:45 - Fix Names in a Table
04:56:05 - Patients With a Condition
05:02:14 - Delete Duplicate Emails
05:06:12 - Second Highest Salary
05:10:17 - Group Sold Products By The Date
05:16:46 - List the Products Ordered in a Period
05:23:21 - Find Users With Valid E-Mails

Connect With Me -

#coding
#leetcode
#mysql
#sql
#interview
#sqlinterview
#sqlinterviewquestionsandanswers
#sql_server
#learnwithchirag
#email
#valid
#user

Relatable Search Terms:-

Crack SQL Interviews in 50 Qs
Crack SQL in 50 questions
These 50 Questions Are Enough to Crack Every SQL Interview
sql interview questions and answers
sql interview questions
sql study plan
sql leetcode study plan

[learnwithchirag]

Hey there! 👋 For more interesting content, tutorials, and updates, Feel free to connect with me on

Instagram Handles :-



Let's stay connected and keep the creativity flowing! 💡

[tauqueer]

"Wow, this video is a game-changer! 🚀 The comprehensive coverage of 50 SQL interview questions in one video is exactly what I needed to boost my preparation. This 'Complete SQL in One Video' is a gem for anyone gearing up for SQL interviews. Kudos to the instructor for creating such a valuable resource! 👏 #SQLInterviewPrep #SQLMastery #LeetcodeStudyPlan"

[nimbu_]

bhai maja aa gaya sql seekh gaya puri mein to kehta hu, ye maja sabko milna chahiye

[MohammedHasmi]

Allah pak give you more success in your life

[pragatiaggarwal]

Thank you so much chirag for this session. 🥰Vey helpful

[ImranAnsari-bpfs]

Thank you so much sir aise hi video dhundh rha tha mai YouTube pr 😊

[debassss]

Thankyou so much... revise hogya sab hehe🙌🙌

[TheAnimeSagaX]

I Successfully solved 50 SQL Questions, Thanks to you man

[PracticePrecision]

Amazing Bhai, your expalnation is AWESOME

[AditiArora-vdng]

Amazing Work🎉
Keep up the good work❤❤

[thevagabondyt]

58:40 For question like these always start as shown below TO GET THE FEELING OF WHAT IS GOING UNDER THE HOOD :

SELECT *
# l.machine_id as machine_id,
# ROUND(( SUM(r.timestamp-l.timestamp) / COUNT(DISTINCT(l.process_id)) ), 3) as processing_time
FROM Activity l JOIN Activity r
ON
l.machine_id = r.machine_id AND
l.process_id = r.process_id
AND l.timestamp < r.timestamp
#GROUP BY l.machine_id

1:40:25 A point worth considering while performing SUM() on column containing some or all values as NULL is :

# SUM() function ignores NULL values when calculating the sum of a set of values
# SUM(null) returns NULL
# null/null returns NULL

DEMO :

mysql> select * from num;

| id | val |

| 1 | NULL |
| 4 | NULL |

2 rows in set (0.00 sec)

mysql> select SUM(val) from num;

| SUM(val) |

| NULL |

1 row in set (0.00 sec)

mysql> select SUM(val)/SUM(val) from num;

| SUM(val)/SUM(val) |

| NULL |

1 row in set (0.00 sec)

After inserting a valid number :

mysql> select * from num;

| id | val |

| 1 | NULL |
| 2 | 3 |
| 4 | NULL |

3 rows in set (0.00 sec)

mysql> select SUM(val) from num;

| SUM(val) |

| 3 |

1 row in set (0.00 sec)


1:52:00 in this solution DISTINCT is redundant. why ?
(contest_id, user_id) is the primary key and final answer is grouped by (contest_id) . after LEFT JOIN's ON condtion each of the group is going to have distinct user_id.
there cannot be two (208, 5)

1:57:30
SELECT
query_name,
ROUND(AVG(rating/position), 2) AS quality,
ROUND(SUM(rating<3)*100/COUNT(*), 2) AS poor_query_percentage
FROM Queries q
GROUP BY query_name
HAVING query_name IS NOT NULL

2:06:38 alternate way to find column approved_total_amount
SUM(amount *(state='approved') ) AS approved_total_amount

2:08:57 1174. Immediate Food Delivery II

Those who is having hard time understanding how WHERE is working with IN clause using 2 columns can try following Alternative:

SELECT
)/ COUNT(t.customer_id), 2) AS immediate_percentage
FROM
(SELECT customer_id, MIN(order_date) as first_order_date
FROM Delivery
GROUP BY customer_id) AS t
JOIN Delivery d ON (t.customer_id = d.customer_id AND first_order_date = order_date)

which can also be written as below using WITH clause (better readability)

WITH MinDate AS
(
SELECT customer_id, MIN(order_date) as first_order_date
FROM Delivery
GROUP BY customer_id
)

SELECT
)/ COUNT(t.customer_id), 2) AS immediate_percentage
FROM
MinDate t
JOIN Delivery d ON (t.customer_id = d.customer_id AND first_order_date = order_date)

2:34:35 1141. User Activity for the Past 30 Days I
No need to manually substract days from given date to find start date ... if day range was not 30 but 43 how difficult it ud have been to calculate the start date. Instead use DATE_SUB(date, INTERVAL 30 days) learned in some previous question.

SELECT
activity_date AS day,
COUNT(DISTINCT user_id) AS active_users
FROM
Activity
WHERE
activity_date>DATE_SUB('2019-07-27', INTERVAL 30 DAY) AND activity_date <='2019-07-27'
# '2019-06-28'<= activity_date AND activity_date <='2019-07-27'
GROUP BY
activity_date
#ORDER BY day

3:12:00 1789. Primary Department for Each Employee

use of DISTINCT is completely "reduntant" here... because employee_id filtered by primary_flag='Y' are mutually exclusive with employee_id filtered by subQuery.

SELECT
employee_id,
department_id
FROM
Employee
WHERE employee_id IN
(
SELECT employee_id
FROM Employee
GROUP BY employee_id
HAVING COUNT(*)=1
)
OR primary_flag='Y'

3:41:00 1204. Last Person to Fit in the Bus : ALTERNATE SOLUTION

# In the table try adding a new Column - Cumulative Weight
/* First try this : this is later used as SubQuery
SELECT
*,
SUM(weight) OVER (ORDER BY turn)AS CumulativeWeight
FROM Queue
*/

# Now filter the rows based on Cumulative Weight
SELECT person_name
FROM Queue
WHERE person_id = (
SELECT person_id #, max(cwt)
FROM (
SELECT
*,
SUM(weight) OVER (ORDER BY turn)AS cwt
FROM Queue
) AS Q
WHERE cwt <= 1000
GROUP BY person_id
ORDER BY cwt DESC
LIMIT 1
)

# JOIN
SELECT q1.person_name #, SUM(q2.weight) AS cwt
FROM Queue q1 JOIN Queue q2
ON q1.turn >= q2.turn
GROUP BY q1.turn
# now filter using HAVING clause
HAVING SUM(q2.weight)<=1000
# now arrage in Descending order
ORDER BY SUM(q2.weight) DESC
# now chose topmost row
LIMIT 1

3:49:46 1907. Count Salary Categories - ALTERNATE SOLUTION

WITH Result AS
(
SELECT
(CASE
WHEN income < 20000 THEN "Low Salary"
WHEN income>50000 THEN "High Salary"
ELSE "Average Salary"
END) AS category,
COUNT(*) AS acc_cnt
FROM
Accounts
GROUP BY category
),
Categories AS
(
SELECT 'Low Salary' AS category
UNION
SELECT 'Average Salary'
UNION
SELECT 'High Salary'
)

SELECT
C.category,
IF(ISNULL(acc_cnt), 0, acc_cnt) AS accounts_count
FROM Categories C LEFT JOIN Result R
ON C.category = R.category
GROUP BY
C.category

4:14:36 Resturant Growth
No need of executing SUB-QUERY twice - once for each COLUMN-amount & avg_amount

WITH TwoRows AS
(
SELECT
visited_on,
(
SELECT SUM(amount)
FROM Customer
WHERE visited_on
BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY)
AND c.visited_on
) AS amount
FROM
Customer c
WHERE
visited_on >=
(
SELECT DATE_ADD( MIN(visited_on), INTERVAL 6 DAY)
FROM Customer
)
GROUP BY
visited_on
)

# USING COLUMN amount, calculate derived COLUMN avg_amt
SELECT *, ROUND((amount/7), 2) AS average_amount
FROM TwoRows

5:32:30 1484. Group Sold Products By The Date
#even without ordering by Products it got accepted . weired.
SELECT
sell_date,
COUNT(DISTINCT product) AS num_sold,
GROUP_CONCAT(DISTINCT product) AS products
FROM Activities
GROUP BY sell_date
ORDER BY sell_date

[sonalijain]

Please make leetcode solution for pandas and python

[thevagabondyt]

23:34:44 HAVING CLAUSE instead of WHERE clause also works :

SELECT
activity_date AS day,
COUNT(DISTINCT user_id) AS active_users
FROM Activity
GROUP BY activity_date
HAVING '2019-06-28' <= activity_date AND activity_date <= '2019-07-27'

[MHb]

Loving your explanation on making steps clear.Would love if you make more videos on solving sql problems.
Subscribed!!❤
Sir, at what level of sql solving skills required before applying for junior data analyst?How can I build logic developing skills ?

[unveiledPJ]

THANKS SO MUCH FOR THIS VIDEO!!! JUST ONE QUERY, HOW WOULD WE KNOW WHICH JOIN TO USE, OR WHEN NOT TO USE JOINS, AND WHEN TO USE SUB QUERIES OR SOMETHING ELSE???

[thevagabondyt]

cant believe showing only 224 likes.... should be 2M likes

[ajaharuddinmohd]

*Queries Quality and Percentage*
In question 19, all test cases are passing with your solution but one is failing which has NULL values in the query_name column. To pass all test cases:
SELECT query_name, ROUND(AVG(rating/position), 2) quality, ROUND(AVG(IF(rating <3, 1, 0)*100), 2) poor_query_percentage
FROM Queries
WHERE query_name IS NOT NULL
GROUP BY query_name;

[AbhishekTiwari-bkbt]

@ 53.17 Bhai Rising temperature i am using ADDDATE(W1.RECORDDATE, 1) = W2.RECORDDATE but output only give id 2 and not 4 please guide i tried but not getting logic

[ajaharuddinmohd]

*Immediate Food Delivery*
Why am I getting an error when I use AVG() function in the query?
SELECT ROUND(AVG(IF(MIN(order_date) = customer_pref_delivery_date, 1, 0)), 2) immediate_percentage
FROM Delivery
WHERE (customer_id, order_date) IN
(SELECT customer_id, MIN(order_date) first_order_date
FROM Delivery
GROUP BY customer_id)

[ajaharuddinmohd]

Second Highest Salary: with Sub query
SELECT
(SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1
OFFSET 1) AS SecondHighestSalary;