LeetCode Medium 1699 Facebook/Meta Amazon Interview SQL Question with Detailed Explanation

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

[sreedevimutyampeta]

select from_id, to_id, sum(duration)
from
(
select from_id, to_id, duration from Calls
union all
select to_id, from_id, duration from Calls) a
where from_id<to_id
group by from_id, to_id

[VikasKumar-tokv]

I got a totally different approach and I think its great.
with cte as(
select *, from_id*to_id as hey, from_id +to_id as dey from calls)
select from_id as person1, to_id as person2, count(from_id) as call_count, sum(duration) as total_duration
from cte group by hey, dey

[ritusantra]

with cte as
(select from_id, to_id, duration from calls
union all
select to_id, from_id, duration from calls)
select from_id as person1, to_id as person2, count(*) as call_count,
sum(duration) as total_duration
from cte where from_id < to_id
group by from_id, to_id;

[florincopaci]

select least(from_id, to_id)as person1,
greatest(from_id, to_id)as person2,
count(*)as call_count, sum(duration)as duration_of_calls
from da
group by least(from_id, to_id), greatest(from_id, to_id)
order by 1, 2;

[HarshTanwar-qn]

Hi. Why is the below query not being accepted? Can someone help in figuring it out.
SELECT SUBSTRING(A.COMMON, 1, POSITION('_' IN A.COMMON)-1) AS "person1",
SUBSTRING(A.COMMON, POSITION('_' IN A.COMMON)+1, LENGTH(A.COMMON)-POSITION('_'IN A.COMMON))
AS "person2", COUNT(*) "call_count", SUM(A.DURATION) "total_duration" FROM
(SELECT *,
CASE WHEN FROM_ID > TO_ID THEN CONCAT(TO_ID, '_', FROM_ID)
ELSE CONCAT(FROM_ID, '_', TO_ID) END COMMON
FROM CALLS) A
GROUP BY COMMON

[sohailshaikh]

SELECT LEAST(a.from_id, a.to_id) AS person1,
greatest(a.from_id, a.to_id) AS person2,
COUNT(1) AS call_count,
SUM(a.duration)
FROM calls a
GROUP BY person1, person2;

[ujjwalvarshney]

select person1, person2, count(*) as total_call, sum(duration) as duration from
(
select min(form_id, to_id) as person1 , max(form_id, to_id) as person2, duration
from calls_table
) group by 1, 2 order by 1

[AbdulRahman-zpbp]

Bro, Please start a playlist of python where you solve interview Questions(of level Easy, Medium to Hard) while explaining logic,
I'll be desperately waiting coz there's no YouTube playlist of solving python problem while explaining logic
Thanks 🙂

[illuminatidaylight]

These questions are locked ....only for subscribed people

[tejaswaniguttula]

@EverydayDataScience

Hi sir,
your videos are very useful to me sir.
Could you please tell me what wrong in my query is

select
case when from_id < to_id then from_id
else to_id
end as person1,
case when from_id < to_id then to_id
else from_id
end as person2,
count(*) as call_count,
sum(duration) as total_duration
from
calls
;

[divyanshusinghchauhan]

WITH cte1 AS (
SELECT
*,
from_id + to_id AS checkValue,
COUNT(*) OVER (PARTITION BY from_id + to_id) AS call_count,
SUM(duration) OVER (PARTITION BY from_id + to_id) AS total_duration
FROM
call_data
),
cte2 AS (
SELECT
MIN(from_id) OVER (PARTITION BY checkValue) AS min_from_id,
MAX(to_id) OVER (PARTITION BY checkValue) AS max_from_id,
call_count,
total_duration
FROM
cte1
)


SELECT min_from_id, max_from_id, call_count, total_duration FROM cte2 group BY min_from_id, max_from_id, call_count, total_duration