SQL Interview Questions And Answers Part 61 | SQL questions for Product based companies

SQL Interview Questions And Answers Part 61
This question has been asked in many company and it will be helpful in cracking any SQL interview
Problem Statement : Device Table consists of Device_id and Locations.
Write a SQL query to get the output as shown in video :-
-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :
/ itjunction4all
*Twitter:
/ sunilkr5672
-------------------------------------------------------------------------
Table and Insert SQL Script :
-------------------------------------------------------------------------
Create Table Device(
Device_id int,
Locations Varchar(25)
)
Insert into Device (Device_id,Locations) values
(12,'Bangalore'),
(12,'Bangalore'),
(12,'Bangalore'),
(12,'Bangalore'),
(12,'Hosur'),
(12,'Hosur'),
(13,'Hyderabad'),
(13,'Hyderabad'),
(13, 'Secunderabad'),
(13, 'Secunderabad'),
(13, 'Secunderabad')
#WALMARTInterview #FAANG #SQLInterviewQuestionsandanswers #sqlInterviewQuestions #MAANG #sqlInterviewQuestionsForTesting

[@abhishekkukreja6735]

Great approach Sunil ji , my approach : with t1 as ( select device_id , locations , count( locations) as ln from Device group by device_id , locations ), t2 as( select *, count(locations) over(partition by device_id) as cnts, row_number() over(partition by device_id order by ln desc) as rnk , sum(ln) over(partition by device_id) as total_cnts from t1 ) select device_id , cnts as no_of_location , locations as max_signal_location, total_cnts as no_of_signals from t2 where rnk = 1

[@ItJunction4all]

Thanks for alternate solution 👍👍

[@anshusharaf2019]

My Approch: with cte as ( select device_id, count(DISTINCT locations) as no_of_location, locations, count(locations) as cnt_siginal_location, count(locations) as no_of_signal, dense_rank() over(partition by device_id order by count(locations) desc) as rn from Device group by device_id, locations ) select device_id, sum(no_of_location) total_location, locations, sum(no_of_signal) as total_signal from cte group by device_id

[@subhashreekarmakar2822]

with cte1 as ( select device_id, count(locations) as no_of_signals, count(distinct locations) as no_of_locations from device group by device_id), cte2 as ( select device_id,locations, rank () over (partition by device_id order by count(device_id) desc ) as rn from device group by device_id,locations ) select a.*,b.locations from cte1 a, cte2 b where a.device_id=b.device_id and rn =1

[@AbhijitPaldeveloper]

My approach in mysql SELECT device_id, no_of_location, locations as `max_signal_location`, total_sum as `no_of_signal` FROM(SELECT *, DENSE_RANK() OVER(PARTITION BY device_id ORDER BY distinct_total DESC) as rnk FROM( SELECT *, SUM(distinct_total) OVER(PARTITION BY device_id) as 'total_sum', MAX(distinct_total) OVER(PARTITION BY device_id) as 'final_maximum', COUNT(distinct_total) OVER(PARTITION BY device_id) as 'no_of_location' FROM(SELECT *, COUNT(device_id) as distinct_total FROM `device` group by locations) as e) as e1) as e2 WHERE e2.rnk = 1;

[@ItJunction4all]

Thanks for providing solution in SQL !

[@satishchaurasia84ya]

Awesome approach🎉

[@ItJunction4all]

Thanks a lot Satish !

[@user-gq6cg3ls7f]

My Approach: with cte as( select Device_id, count(*) no_of_signals from Device group by Device_id ), cte2 as( select Locations, Device_id, count(*) cnt, count(locations) over (partition by Device_id) no_of_locations from Device group by Device_id, Locations ) select c.Device_id, c2.no_of_locations, c2.Locations as max_signal_location, c.no_of_signals from cte c inner join cte2 c2 on c.Device_id=c2.Device_id where cnt in (3,4)

[@dhyeypatel1335]

with cte as( select distinct locations,device_id, count(locations) over(partition by locations) as no_of_locations from device), cte2 as( select *, rank() over(partition by device_id order by no_of_locations desc) as rank_ from cte ) select c.locations,c.device_id,d.no_ofsignals,d.no_oflocations from cte2 c join (select device_id,count(*) as no_ofsignals, count(distinct locations) as no_oflocations from device group by device_id) d on c.device_id = d.device_id where c.rank_ = 1

[@notavi78]

My Approach with cte as ( select device_id, dense_rank() over (partition by device_id order by locations) a, locations, count(*) over (partition by device_id, locations) b, count(*) over (partition by device_id) c from device ), cte_2 as ( select device_id, max(a) a, max(b) b, max(c) c from cte group by device_id ) select t.device_id, t.a as no_of_locations, f.locations, t.c as no_of_signals from cte_2 t left join (select distinct device_id, locations, b from cte) f on t.device_id = f.device_id and t.b = f.b ;

[@ItJunction4all]

Thanks for providing alternative approach. The SQL query looks good to me.

[@pavankamalvadigi516]

Create and Insert for Oracle: Create Table Device( Device_id int, Locations Varchar(25) ); Insert into Device (Device_id,Locations) values(12,'Bangalore'); Insert into Device (Device_id,Locations) values(12,'Bangalore'); Insert into Device (Device_id,Locations) values(12,'Bangalore'); Insert into Device (Device_id,Locations) values(12,'Bangalore'); Insert into Device (Device_id,Locations) values(12,'Hosur'); Insert into Device (Device_id,Locations) values(12,'Hosur'); Insert into Device (Device_id,Locations) values(13,'Hyderabad'); Insert into Device (Device_id,Locations) values(13,'Hyderabad'); Insert into Device (Device_id,Locations) values(13, 'Secunderabad'); Insert into Device (Device_id,Locations) values(13, 'Secunderabad'); Insert into Device (Device_id,Locations) values(13, 'Secunderabad');

[@ItJunction4all]

Thank you Pavan 😊

[@florincopaci6821]

select a.device_id , (select b.locations from (select device_id, locations, dense_rank ()over(partition by device_id order by count(*) desc )as rnk from Device group by device_id, locations)b where rnk=1 and b.Device_id=a.device_id)as max_locations, count(distinct locations)as no_locations, count(100)as no_signals from device a group by a.Device_id

[@ItJunction4all]

Thanks for providing alternative approach. The SQL query looks good to me.

[@satishchaurasia84ya]

Can you please tell me why this FIRST_VALUE is not working in 2008 sql server. In which version we can use this function

[@ItJunction4all]

FIRST_VALUE works in SQL Server 2012 and above version.

[@prajjwaljaiswal4950]

-- Without using any advanced Function with cte_temp as ( select device_id, count(distinct locations) as no_of_location, count(*) as no_of_signals from device group by device_id ), cte_max_count as( select device_id, locations, count(*) as ct, rank() over(partition by device_id order by count(*) desc) as rk from device group by device_id,locations ) select a.device_id, a.no_of_location, m.locations as max_signal_location, a.no_of_signals from cte_temp a left join cte_max_count m on a.device_id = m.device_id and m.rk = 1 ;

[@prabhatgupta6415]

WITH a AS (SELECT device_id, Count(DISTINCT locations) AS number_of_locations, Count(device_id) AS no_of_signals FROM device GROUP BY device_id), b AS (SELECT device_id, locations AS max_signal_location FROM (SELECT *, Max(location) OVER( partition BY device_id) AS mx FROM (SELECT device_id, locations, Count(locations) AS location FROM device GROUP BY 1, 2)x)y WHERE location = mx) SELECT a.device_id, number_of_locations, max_signal_location, no_of_signals FROM a JOIN b ON a.device_id = b.device_id; Its quite long :D will try to optimize

[@grzegorzko55]

WITH cte AS (SELECT device_id, Count(DISTINCT locations) AS no_of_locations FROM device GROUP BY device_id), cte2 AS (SELECT device_id, locations FROM (SELECT device_id, locations, rn, Max(rn) over ( PARTITION BY device_id) AS max_rn FROM (SELECT device_id, locations, Row_number() over( PARTITION BY device_id, locations ORDER BY device_id) AS rn FROM device)) WHERE rn = max_rn) SELECT x.device_id, x.no_of_locations, y.locations, z.count_loc FROM cte x inner join cte2 y ON x.device_id = y.device_id inner join (SELECT device_id, Count(1) AS count_loc FROM device GROUP BY device_id) z ON x.device_id = z.device_id

[@ItJunction4all]

Thanku you for posting sql query.