LeetCode 1211 Interview SQL Question with Detailed Explanation | Practice SQL

Previous Video: LeetCode 1113 Reported Posts

In this video I solve and explain a leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question is about queries quality and percentage and also includes points to keep in mind to develop SQL queries. You will also learn about COMMON TABLE EXPRESSIONS and CASE WHEN THEN statements.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[containthis]

I am new to DA and SQL, you are awesome.. you are one of the few that can actually explain and code at the same time. Some people can code but cant teach and then there is you. you can coach and code at the same time and make practical explanation.. well done again, ,

[jayaprakashs]

select query_name, round(sum(rating/position)/count(*), 2) as quality, round((sum(rating<3)/count(*))*100, 2) as poor_query_percentage
from queries
group by query_name
having query_name is not null;

try this. It worked for me.

[tejaswaniguttula]

This is how I solved the problem sir. It seems like tricky when I tried myself to solve it.


with cte1 as
(select
query_name,
count(rating) as total_ratings,
round((sum(rating/ position) / count(position)), 2) as quality
from
queries

group by query_name ),
cte2 as
(
select
query_name,
count(rating) as req_rating
from queries
where rating < 3

group by query_name
)

select
cte1.query_name as query_name,
cte1.quality as quality,
case when
round((cte2.req_rating / cast(cte1.total_ratings as float) ) * 100, 2) is null
then 0
else round((cte2.req_rating / cast(cte1.total_ratings as float) ) * 100, 2)
end as poor_query_percentage
from
cte1 left join cte2

on cte1.query_name = cte2.query_name
where cte1.query_name is not null;


But the way you taught it is splendid sir. Thank you so much sir . Thanks for the approach ❤sir

[justcodeitbro]

there is one test case for query_name = Null
just put
where query_name is not null
group by query_name
you will pass all the test cases

[containthis]

always remember that this is an easy question, but this code is not simple to a beginner.
select query_name,
round (sum(rating/position)/count(*), 2) as quality,
Round (sum(case when rating < 3 then 1 else 0 end)/count(*)*100, 2) as poor_query_percentage
from Queries
group by query_name;

[containthis]

I love the way you explain code but please make the code simple for us new guys..

[ritwiksingh]

This solution misses a test case. Try the below query to get the answer submitted on leetcode:

select query_name, round(avg(rating/position), 2) as quality,
round(100*(avg(case when rating<3 then 1 else 0 end )), 2) as poor_query_percentage
from queries
group by query_name
having query_name is not null;

[VikashKumar-snzj]

i don't understand anything what do I to understand to easily

[mickyman]

select x.query_name, round(sum(x.rating/x.position)/count(*), 2) as quality, round((sum(x.rating_num)/count(*))*100,2) as poor_query_percentage
from (select distinct *, case when rating<3 then 1 else 0 end as rating_num
from Queries where query_name is not null) x
group by x.query_name;

[adityams]

without CTE

SELECT
query_name,
round(avg(rating::decimal / POSITION), 2) AS quality,
round(count(CASE WHEN rating<3 THEN 1, 2) AS poor_query_percentage
FROM
Queries
GROUP BY
query_name;

[AakifKhan-he]

Yeh question Easy mae dal rakha hai, its should be in moderate

[parikshitgupta]

WITH cte as
(SELECT query_name, rating/position as ratio, CASE
WHEN rating < 3 THEN 1
ELSE 0 END AS percent
FROM Queries
WHERE query_name IS NOT null)

SELECT query_name, ROUND(AVG(ratio), 2) AS quality, ROUND(SUM(percent) / COUNT(*) * 100, 2) AS poor_query_percentage
FROM cte
GROUP BY query_name

[baldevsingh-bhj]

CREATE TABLE query (
Query_name varchar(255),
Result varchar(255),
position Int,
Rating Int,

);

INSERT INTO query (Query_name, Result, position, Rating)
VALUES
('Dog', 'Golden_retriver', 1, 5),
('Dog', 'German shepherd', 2, 5),
('Dog', 'mule', 200, 1),
('Cat', 'shirazi', 5, 2),
('Cat', 'siamese', 3, 3),
('Cat', 'sphynx', 7, 4);

[VikashKumar-snzj]

मुझे कुछ भी समझ में नहीं आ रहा है कि मैं क्या करूं जो आसानी से समझ आ जाए