LeetCode 1211 Interview SQL Question with Detailed Explanation | Practice SQL

[@tejaswaniguttula5961]

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

[@justcodeitbro1312]

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

[@ritwiksingh5943]

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

[@containthis4450]

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,,

[@containthis4450]

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;

[@VikashKumar-sn8zj]

thanks

[@sharankarchella2688]

this code does'nt satisfy all test cases if any query_name(dog or cat) is null

[@ChandraTanikonda]

@@sharankarchella2688 write( where query_name is not null) before group by

[@containthis4450]

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

[@k_aryal]

The pace and explanation is perfect.

[@AakifKhan-h9e]

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

[@adityams1659]

without CTE SELECT query_name, round(avg(rating::decimal / POSITION),2) AS quality, round(count(CASE WHEN rating

[@sohailkan17]

does using CTE impacts execution performance by any means ?

[@baldevsingh-b5h9j]

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);

[@parikshitgupta343]

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

[@laumatthew71]

Clear explanation, thank you sir !

[@EverydayDataScience]

Glad that you found it useful, Lau.

[@VikashKumar-sn8zj]

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

[@VikashKumar-sn8zj]

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

[@adarshjha5126]

kismat pe chhod do....khud mehnat mat karna bas

[@dared2sing904]

best explanation

[@ajaykumargaudo6685]

Very good explanation love it

[@EverydayDataScience]

Glad that you found it helpful.