LeetCode Medium 585 "Investments in 2016" Twitter Interview SQL Question With Detailed Explanation

No video

LeetCode Medium 585 "Investments in 2016" Twitter Interview SQL Question With Detailed Explanation

Рет қаралды 4,476

Күн бұрын

Question: leetcode.com/p...
In this video I solve and explain a medium difficulty 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 includes points to keep in mind to develop SQL queries.
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

Пікірлер: 22

@vikashkumargupta4876 2 ай бұрын

The explanation was very clear and so in-depth which helped me to understand the concepts very clearly specially the table creation and providing knowledge on that. Thanks and keep up the good work. We all are thankful😊

@adnanafdalahmed 2 ай бұрын

kzfaq.info/get/bejne/iZZgmMiossvWd2g.html

@23cash86 Ай бұрын

Very good explanation

@sougaaat 2 ай бұрын

there's an easy way to do it -> # Write your MySQL query statement below with inv as (select tiv_2015 from insurance group by 1 having count(tiv_2015) > 1), pol as ( select min(pid) as pid from insurance group by concat(lat, ",", lon) having count(concat(lat, ",", lon)) = 1 ) select round(sum(tiv_2016),2) as tiv_2016 from insurance where tiv_2015 in (select tiv_2015 from inv) and pid in (select pid from pol)

@sonalibabar 11 ай бұрын

Another way using windows function: # Write your MySQL query statement below WITH cte AS( SELECT i.* , COUNT(i.pid) OVER(PARTITION BY i.tiv_2015) same_tiv_2015 , COUNT(i.pid) OVER(PARTITION BY i.lat,i.lon) same_loc FROM Insurance i ) SELECT ROUND(SUM(tiv_2016),2) AS tiv_2016 FROM cte WHERE same_tiv_2015 > 1 AND same_loc = 1 ;

@shreehari2589 2 ай бұрын

Awesome explanation bro, keep up the good work

@snehasoni1392 7 ай бұрын

select round(sum(tiv_2016),2) as tiv_2016 from Insurance where tiv_2015 IN ( select tiv_2015 from insurance group by tiv_2015 having count(*) >1 ) AND(lat,lon) IN ( select lat,lon from insurance group by lat , lon having count(*)= 1 )

@yongcui1308 Жыл бұрын

so clear, great explanation!

@EverydayDataScience Жыл бұрын

Glad that you found the video useful 😊

@sanyagandhi6576 Жыл бұрын

Awesome explanation! Thank you😊

@AJ-sw5hx 10 күн бұрын

how come pid no 2 during joins only one row is there? why isn't it joined with row no 1,3,4?

@EUEC_CHARRUNETHRACR 11 ай бұрын

Best explanation ever!! Hats off, Sir !! I tried with my own as a beginner, Sir. I have a small doubt with my code. WITH cte AS (SELECT CONCAT(lat, ',' , lon) AS location FROM Insurance GROUP BY lat, lon HAVING COUNT(pid) > 1) SELECT ROUND(SUM(tiv_2016),2) AS tiv_2016 FROM Insurance GROUP BY tiv_2015 HAVING COUNT(pid) > 1 AND (CONCAT(lat, ',' , lon) NOT IN (SELECT location FROM cte)) Runtime Error Unknown column 'lat' in 'IN/ALL/ANY subquery' Why this code shows run time error, Sir? Would you please explain, so that it might improve my learning, sir ?

@nitinmadan4009 3 ай бұрын

(CONCAT(lat, ',' , lon) NOT IN (SELECT location FROM cte)). . In your code, here seems to be an extra ' (' and an extra ')' at the end and entire line seems to be enclosed under concat therefore. Instead it should be AND CONCAT(lat, ',' , lon) NOT IN (SELECT location FROM cte)