LeetCode 1294 Interview SQL Question with Detailed Explanation | Practice SQL

[@Sajitha_Alisheik]

Hy sir! Yur teaching techniques are quite awesome...for the very first time I'd solved a problem on my own. Am very happy.It's due to the knowledge I've gained from you. Continue doing your great work and inspire every one.😊

[@kgtw5506]

Perfect explanation... Thank you for highlighting details .... It seems watching all ur videos would b sufficient to land up job.. as DA

[@EverydayDataScience]

Glad that you are finding the videos useful 😊 Thanks for such kind words. I’d say consistency, be it following a channel, studying on your own etc and just getting better everyday is what would help you in the long run. All the best

[@yassinbenyahia6801]

good explanation thank you sir

[@sanchitmehta2275]

Can't we use an inner join in this query? I have done it like this please check if there's something wrong: SELECT C.COUNTRY_NAME, CASE WHEN AVG(W.WEATHER_STATE) = 25 THEN 'HOT' ELSE 'WARM' END AS WEATHER_TYPE FROM COUNTRIES C INNER JOIN WEATHER W ON C.COUNTRY_ID = W.COUNTRY_ID WHERE DATE_FORMAT(W.DAY, '%Y-%m') = '2019-11' GROUP BY C.COUNTRY_NAME ;

[@abhisheksharma6617]

I used a cte for this : with cte1 as (select c.country_id, c.country_name, w.weather_state, w.weather_day from countries c join weather w on c.country_id=w.country_id ), cte2 as (select *, case when weather_state=25 then 'Hot' else 'Warm' end as type_of_weather from cte1) select country_name, type_of_weather, month(weather_day) months from cte2 group by country_name, type_of_weather, months having months=11;

[@abhinavtiwari6186]

select c.country_name, (case when avg(w.weather_state) =25 then 'Hot', else 'Warm' end as weather_type group by c.country_name) from country c left join weather w on c.country_id = w.country_id where month(day)='11' and year(day)='2019'; what's wrong in this?

[@irislynn4970]

very helpful thank you

[@EverydayDataScience]

Glad you found it useful, Iris.

[@mlvprasadofficial]

i have done the same select c.country_name , case when avg(weather_state) = 25 then 'Hot' else 'Warm' end as weather_type from Countries c left join Weather w on c.country_id = w.country_id where month(w.day) = '11' and year(w.day) = '2019' group by c.country_name order by avg(weather_state) answer got accepted but submission failed

[@EverydayDataScience]

There is no reason for the submission to fail, it looks good to me. Maybe you can try submitting again.

[@kavyabanka4482]

​@@EverydayDataSciencesir whe we left join weather there should be countries table first

[@mlvprasadofficial]

21

[@sukumar-m4t]

LeetCode Premium

[@vamsimadugula8524]

WITH cte AS( SELECT country_id,AVG(weather_state) AS average_weather FROM Weather WHERE day BETWEEN "2019-11-01" AND "2019-11-30" GROUP BY country_id ) SELECT c.country_name, CASE WHEN a.average_weather = 25 THEN "Hot" ELSE "Warm" END AS weather_type FROM Countries c INNER JOIN cte a ON c.country_id = a.country_id