Bosch Scenario Based SQL Interview Question | Solving Using 3 Methods | Data Analytics

In this question I am going to solve an SQL interview question using 3 methods. You will learn a lot of concept in this video.

00:00 Understanding the problem
01:53 CTE and where clause
06:30 Magic of having clause
09:09 CTE and inner join

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

create table call_details (
call_type varchar(10),
call_number varchar(12),
call_duration int
);

insert into call_details
values ('OUT','181868',13),('OUT','2159010',8)
,('OUT','2159010',178),('SMS','4153810',1),('OUT','2159010',152),('OUT','9140152',18),('SMS','4162672',1)
,('SMS','9168204',1),('OUT','9168204',576),('INC','2159010',5),('INC','2159010',4),('SMS','2159010',1)
,('SMS','4535614',1),('OUT','181868',20),('INC','181868',54),('INC','218748',20),('INC','2159010',9)
,('INC','197432',66),('SMS','2159010',1),('SMS','4535614',1);

#sql #bosch #interview

[apurvasaraf]

with cte as(select call_number,
sum(case when call_type='INC' then call_duration end) as inty,
sum(case when call_type='OUT' then call_duration end ) as outy
from call_details
group by call_number)
select call_number from cte where outy>inty

[smitadebata]

you are a Maestro of SQL. Your way of explaining is so so easy to follow. Showing ways to solve a single problem by 3 different ways shows the depth of your knowledge and more importantly your genuine interest in empowering others to think in SQL in true terms

[Happusingh]

All the solutions were superb..
My approach:
with CTE as (
Select number, call_type, sum (call_duration) as total_duration from #Call_Table
Group by number, call_type
)
Select * from CTE e
Inner join cte f
on e.number = f.number
where (e.call_type = 'Incoming' and f.call_type = 'Outgoing')
and e.total_duration < f.total_duration;

[ecubewithme]

That's great 🙌🙌 i was able solve these type of sql questions after practicing from your initial videos on sql thank you 🙌

[shekharagarwal]

Thanks ank(IT), you are pro Man in SQL . You have made SQL little more interesting. I have tried in bit other way but your approach was tricky one especially the last one .

with cte1 as (
select call_number, call_type, sum(call_duration) as duration
, rank() over ( partition by call_number order by call_type) as rnk
from call_details
where call_type <> 'SMS' group by call_number, call_type)

, cte2 as (Select *,
lead(duration, 1, 0) over ( partition by call_number order by call_type ) as out_duration from cte1 )

Select * from cte2 where out_duration > duration;

[armanmardhani]

with cte as (select call_number from call_details where call_type in ('INC', 'OUT')
group by call_number having count(distinct call_type) = 2)
, cte2 as (select call_number, sum(case when call_type = 'INC' then call_duration else null end) as inc_duration,
sum(case when call_type = 'OUT' then call_duration else null end) as out_duration
from call_details where call_number in (select * from cte)
group by call_number)
select call_number from cte2 where out_duration > inc_duration

[MixedUploader]

ANKIT method 1 using cte and where clause was superb. Thanks

[yateeshbk]

Hi Ankit, I want to thank you for sharing your videos. It helped me a lot. Below is query. I have written in MYSQL DB.

with cte1 as
(select * from call_details where call_number in (select call_number from call_details where call_type='inc') and call_type='out'
union all
select * from call_details where call_number in (select call_number from call_details where call_type='out') and call_type='inc')
select call_number,
sum(case when call_type='INC' then call_duration else "" end) as Incoming_total,
sum(case when call_type='OUT' then call_duration else "" end) as Outgoing_total
from cte1 group by call_number having

[Artouple]

Great video as always :)), Video request: Indexing and it's types 🙏

[mananagrawal]

Hey Ankit here is my solution using window function:
with cte as(
SELECT call_type, call_number, sum(call_duration) as sum_duration, count(1) over (Partition by call_number) as cnt
from call_details
group by call_type, call_number
having call_type in ('INC', 'OUT')
)
SELECT a.call_number
from cte a join cte b
on a.call_number = b.call_number and a.call_type='INC' and b.call_type='OUT' and
where a.cnt=2

[Datapassenger_prashant]

thank you so much ankit Sir, aapki teachings ke liye..because of that, I was able to solve the problem easily..
here is my sol: please note this is before watching the video.

With OI_Calls as (
Select * from call_details
where call_type != 'SMS'
), CTE1 as (
Select distinct call_number, call_type, sum(call_duration) over(partition by call_type, call_number) as duration_sum
from OI_Calls
), final_CTE as (
Select call_number
, max(case when call_type = 'INC' then duration_sum end) as Incoming_Call
, max(case when call_type = 'OUT' then duration_sum end) as Outgoing_Call
from CTE1
group by call_number
)
Select * from final_CTE
where Incoming_Call is not null
and Outgoing_Call is not null
and Outgoing_Call > Incoming_Call

[shubhamagrawal]

there is 4th approach using corelated subquery and EXISTS clause (but if data set is very huge this approach might took more time)-->
with cte as (
select call_type, call_number, sum(call_duration) call_duration
from call_details
where call_type <> 'SMS'
group by 1, 2)
select call_number
from cte a
where exists (select 1 from cte b where (b.call_type = 'INC') and (b.call_number = a.call_number) and (b.call_duration < a.call_duration))

[ananyaarangarajan]

select call_number,
sum(case when call_type = 'OUT' then call_duration end) as out_goings,
sum(case when call_type = 'INC' then call_duration end) as incomings
from call_details
group by call_number
having out_goings>incomings
order by call_number

[ishika]

with out_inc as(
select * from call_details
where call_type in ('out', 'inc'))
, cte as(
select call_number, call_type, sum(call_duration) as sum from out_inc
group by call_number, call_type)
, cte2 as(
select *,
rank() over (partition by call_number order by sum) as rnk
from cte)
select * from cte2
where rnk=2 and call_type='out';

[pandeyRaman]

pgsql-

with c as
(select
call_type, call_number,
case when sum(call_duration)<
lead
(sum(call_duration)) over(partition by call_number
order by call_number, call_type)
then 'Yes' end as flg
from call_details
where call_type<>'SMS'
group by call_type, call_number
)

select call_number
from c
where flg='Yes'

[sandeepanand]

with cte as (
select call_number,
sum(case when call_type = 'OUT' then call_duration end) as out_sum,
sum(case when call_type = 'INC' then call_duration end) as in_sum
from call_details where call_type in ('INC', 'OUT')
group by call_number order by call_number
)
select call_number from cte
where (out_sum is not null or in_sum is not null) and out_sum > in_sum

[sahilummat]

select call_number,
SUM(case when call_type='OUT' then call_duration end ) as out_call_sum
, SUM(case when call_type='INC' then call_duration end ) as inc_call_sum
from call_details
where call_type in ('OUT', 'INC')
group by call_number
having count (distinct call_type)=2
and SUM(case when call_type='OUT' then call_duration end )>
SUM(case when call_type='INC' then call_duration end )

[gauravmalik]

with ct as (
select call_number, sum(case when call_type = "INC" then call_duration else 0 end )c1,
sum(case when call_type = "OUT" then call_duration else 0 end) c2
from call_details
where call_number in (
select call_number
from call_details
where call_type = "OUT" or call_type = "INC"
group by 1
having count(distinct call_type) = 2)
group by 1
)
select call_number from ct
where c2 > c1

[sathwikdhanya]

using lag function:
with cte as (select call_number, call_type, sum(call_duration) AS OUT,
lag(sum(call_duration), 1) over(partition by call_number order by call_type) as INC
from call_details
where call_type in ('INC', 'OUT')
group by call_number, call_type)
select call_number, OUT, INC from cte where OUT>INC

[pranavtaparia]

with cte as(
select call_number,
sum(case when call_type='OUT' then call_duration else 0 end) as OUT,
sum(case when call_type='INC' then call_duration else null end) as INC
from call_details
where call_type!='SMS'
group by call_number
)
select * from cte where OUT>INC