Complex SQL 3 | Scenario based Interviews Question for Product companies

[@ankitbansal6]

Master the art of SQL @ Rs 1999 with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch.
www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
The course contains 2 portfolio projects and a bonus session on Tableau.
100+ interview problems to crack any SQL INTERVIEW.
You will also get a premium subscription to a website to practice SQL problems worth Rs 5000.

[@bhuwansaoji5307]

another approach would be like this
select name, count(*) as total_visits, (select floor from entries b where b.name=a.name group by floor order by count(*) desc limit 1) as most_floor_visited,
group_concat(distinct resources) as resources_used
from entries a
group by name

[@vikasvk9174]

Thank you Ankit
Because of you am able to think and write such complax sql
.........................................................
with concat_resources as (
select name,count(1) as total_count,group_concat(distinct resources) as resources_used from entries group by name
), most_visit_floor as
(
select name ,floor,count(1) as no_of_floor_visit,rank() over(partition by name order by count(1) desc) as rn from entries group by name,floor
)
select cr.name,cr.total_count,mv.floor,cr.resources_used from concat_resources cr inner join most_visit_floor as mv on cr.name=mv.name and mv.rn = 1;

[@ritika29]

Great question. Thanks!
Below is my version of SQL in Oracle
with cte as(
select name,floor,count(floor) as floor_visit_count
,rank() over(partition by name order by count(floor) desc) rn
from entries
group by name,floor)
select e.name, count(e.name),cte.floor most_visired_floor
,listagg(distinct e.resources, ',') within group(order by e.resources) as resources_used
from entries e
inner join cte on (e.name=cte.name)
where rn=1
group by e.name,cte.floor;

[@ankitbansal6]

Good effort. Keep going 😊

[@weshallbe]

I'm just in awe the things you can output using SQL! I just started preparing for my future SQL interviews. Just love your videos and the way you approach a problem. One thing I'm learning from you is, a complex query needs to broke down into simpler queries. Thank you, Ankit!

[@ankitbansal6]

You got this!

[@varaprasadpurum389]

Great to see your efforts making this Ankit. I really appreciate you making the challenging tasks and solving them at the same time

[@ankitbansal6]

Thanks a ton

[@vaibhavverma1340]

I solved 50% and not getting further , so watch it your videos it's too complex question but you did in a easy way. One of the best sql channel till now . No doubt keep uploading videos and soo get 1M subscribers sir:)

[@ankitbansal6]

Thank you Vaibhav 😊

[@devrajpatidar5927]

Hello nice video and problem statement
Here is my approach to this problem ✅✅
with cte as (
select *
,count(floor) over(partition by name,floor order by name) as cnt
from entries_tbl
),
cte1 as (
select name,count(*) as total_visits, max(case when cnt = 2 then floor else null end) as most_visited_floor, resources
from cte
group by name,resources
)
select name ,sum(total_visits) as total_visits,min(most_visited_floor) as most_visited_floor,STRING_AGG(resources,',') as product from cte1
group by name;

[@indergaming3053]

Ankit bhai tumhare questions solve karke really bahut achi practise hoti ha really love your content >>>>>

[@ankitbansal6]

Keep going 💪

[@kavyaanand1229]

Another approach :
select name, count(name) as total_visits, floor as most_visited_floor,
group_concat(distinct resources) as abd from entries group by name
order by sum(floor)

[@bharathm6284]

floor as most_visited_floor wont work as group by is only on name

[@DilipKumar-of3jk]

Thanks Ankit for the video!! Very useful as always.
Here is my Oracle SQL version of the solution.
with t1 as
(select name,floor,
count(floor) over (partition by name) as total_visits,
count(floor) over (partition by name, floor) as most_visit_floor,
listagg(distinct resources,',') within group (order by resources) over
(partition by name) as resource_used
from entries),
t2 as
(select name,total_visits, floor,resource_used, rank () over (partition by name order by most_visit_floor desc) as rnk from t1)
select name,total_visits,floor as most_visited_floor,resource_used from t2 where rnk=1;

[@ankitbansal6]

Thanks for posting 👏

[@rishabhdhupar4579]

I solved this in Postgressql using following query
select name, sum(count) as total_visits,first_value as most_visted_floor,
array_to_string(array_agg(resources),',') as resource_used
from(select name, floor, resources, count(*),
first_value(floor) over(partition by name order by count(*) desc)
from entries
group by name, floor, resources) a
group by name, first_value

[@shahakunal1]

Hi Ankit ,
This is the way we can implement as well
select t.name ,total_visits, floor as most_used,string_agg as resources_used from (select name,floor from ( select name,floor ,count,rank() over (partition by name order by count desc) as rnk from ( select name,floor,count(*) from entries group by name,floor) as a) as b where rnk = 1) as t JOIN (select name,count(*) as total_visits , STRING_AGG(distinct resources,',') from entries group by name ) as q on t.name = q.name;

[@zeeshanahmed2594]

Thank you very much, Sir. After watching your two videos from the playlist, I was able to solve this on my own.

[@ankitbansal6]

Great job!

[@krishnapavanayitha5749]

Might be below provided is easiest solution. Have a look at it.
select * from entries;
select name,count(name),floor,GROUP_CONCAT(DISTINCT resources)
from
(
select name,floor,count(floor) over(partition by name,floor order by floor) as c,resources
from entries ) ta
group by name having c=max(c);
-- GROUP_CONCAT concats all the string in a group
-- string_agg is almost equivalent function to GROUP_CONCAT in sql server

[@ankitbansal6]

Having c= max(c) won't work.

[@nishchaysharma5904]

Hi Ankit, thanks for the question. Below is my solution -
WITH CTE_floor_visit AS (
SELECT name, floor, RANK() OVER(PARTITION by name ORDER BY COUNT(1) DESC) as Floor_Rank FROM entries
GROUP BY name, floor),
CTE_floor_resources AS (
SELECT name, COUNT(1) as total_visits, GROUP_CONCAT(DISTINCT resources) AS resources_used FROM entries GROUP BY name
)
SELECT t1.name, t2.total_visits, t1.floor AS most_visited_floor, t2.resources_used FROM CTE_floor_visit t1
JOIN CTE_floor_resources t2 on t1.name = t2.name
WHERE Floor_Rank = 1

[@ankitbansal6]

Thanks for posting 👏

[@satyajitbiswal6162]

nice one

[@aaravkumarsingh4018]

with cte as(
select name ,floor,count(floor) as max_visited_floor ,resources from entries
group by name,floor)
select name,sum(max_visited_floor) as total_visits,
case when max_visited_floor=max(max_visited_floor) then floor else Null end most_visited_floor,
group_concat(resources) as resources
from cte group by name;

[@satyajitbiswal6162]

please once check .its showing eror

[@sujaa1000]

Thank you very much, please make more such videos, very very helpful!

[@manjunathreddy5566]

Hi Ankit thanks for uploading videos ..Need same way datascience videos it's helpful

[@TradeGyan5m]

with table_A as (select name ,floor_1,floor_2,resources from (select name,resources,case when floor = 1 then 1 else 0 end as floor_1,
case when floor =2 then 1 else 0 end as floor_2 from entries)A )
,table_b as (select name ,count(*) as total_visits,sum(floor_1) as floor_1,sum(floor_2) as floor_2,group_concat(distinct resources order by resources separator ',')
as resources_use from table_A group by name)
select name,total_visits,case when floor_1>floor_2 then 1 else 2 end as most_visited_floor, resources_use from table_b;

[@learnandapplywithMohit]

Another approach to solve it :
with b as
(select name,count(name) Total_visits
from entries
group BY 1
order by 1),
d as
(select name,floor as most_visited_floor from
(select name,floor,cnt,RANK()over(partition by name order by cnt desc) rnk from
(select name,floor,count(floor) as cnt
from entries
group by 1,2
order by 1,2)
order by 1)
where rnk=1
order by 1),
e as
(select name, (resources || ',' || resources_next) as resources_used from
(select * from
(select name,resources,lead(resources)over(PARTITION by name order by resources) resources_next
from entries
order by 1)
where resources resources_next and resources_next is not NULL))
select b.name,b.Total_visits,d.most_visited_floor,e.resources_used
from b join d on b.name=d.name
join e on b.name=e.name
order by 1

[@ajaxaj6749]

Good question. Pls make more such videos

[@ankitbansal6]

Sure.

[@shivangisharma592]

with cte1 as
(
select distinct name,floor,count(1) over (partition by name,floor order by name) as rnk from entries
)
,floor_max as
(
select name,floor as floor_max from cte1 where rnk = (select max(rnk) from cte1)
)
,
tot_vis as
(
select a.name,b.floor_max,count(1) as tot_visits,group_concat(resources) as tot_visits from entries a join floor_max b on a.name=b.name
group by 1,2
)
select * from tot_vis

[@SarmisthaDebRoy]

with y as(
select name, floor, resources, count(distinct email) visited_floors from employee group by 1,2,3)
select name,group_concat(resources) resources_used,
case when visited_floors=max(visited_floors) then floor else null end as most_visited_floor,
sum(visited_floors) as total_visits
from y group by 1;

[@shahinulhaque6320]

Select T1.name, floor most_vistied_floor, t2.total_visits, t2.resources_used from (
Select name, floor, rank() over(partition by name order by count(1) desc) as tt
from entries
group by name, floor
) as T1
inner join ( Select name, count(1) total_visits, STRING_AGG(resources, ',') as resources_used from entries group by name) T2 on T1.name = T2.name
Where tt = 1
order by T1.name
Great videos you make. Keep going please !

[@ankitbansal6]

Thank you 😊

[@sobhiksaha7140]

Thanks Ankit Sir for such an interesting problem.
Here's my solution in MySQL:
with floor_count as (SELECT name, floor, count(*) as floor_count from entries
group by 1,2),
cte as (select e.*,floor_count from entries e join floor_count f on e.name = f.name and e.floor = f.floor )
select e.name, count(e.name) as total_visists,
case when e.floor_count = max(f.floor_count) then e.floor end as most_visited_floor,
group_concat(distinct resources) as resources_used
from cte e
join floor_count f
on e.name = f.name and e.floor = f.floor
group by 1
order by 1;

[@ankitbansal6]

Great effort 😊

[@mihirsamant9352]

I want to take a moment to thank @Ankit Bansal for all great stuff.
I cleared couple of tech rounds because of your videos.
Love the way you solve problems and share the knowledge.
Thank you so much
Keep it up

[@ankitbansal6]

Congratulations. Keep rocking 🥳🥳

[@anthonychilaka]

Day 3.
thank you Ankit for this scenario sql session.

[@anuragshukla9480]

I tried this Solution,
Select name, count(name), case when count(floor)>1 then floor else 0 end as most_visited_floor, group_concat(resources) as resources_used from entries group by name

[@abhishekbhowmick2590]

Thanks Ankit for the video. It is very help full.
Here is mysql solution:-
select name, count(*) Total_visited_floor, floor as most_visited_floor, group_concat(distinct resources) used_resources
from entries
group by name

[@Tushar-xe6is]

Very interesting Question Ankit, keep uploading videos like that it helps alot.

[@ankitbansal6]

Thank you, I will

[@temitopeolagoke]

DISTINCT resources without the CTE works for me on PostgreSQL

[@prajwalkunder2760]

select name,count(*) as total_visits,
max(floor) over(partition by name order by name) as most_visited_floor,
group_concat(distinct resources ) as resources_used
from entries
group by name
order by name;

[@VivekKumar-dz7ie]

You are awesome. I can think SQL now.

[@ankitbansal6]

Wow, thanks! if you can think then you can write :)

[@sailajatumu3682]

with CTE as
(
select a.*
,row_number() over(order by customer_id,order_date) as Rn
,order_Date - row_number() over(order by customer_id,order_date) as rp
from EIM_QA.customer_orders a
)
,noofcust as
(
select customer_id,count(rp) as cnt
from CTE
group by customer_id
)
select
customer_id,cnt
,case when cnt=1 then 'New cust' ---end Newcust
when cnt> 1 then 'Rep crusomter' end cust
from noofcust
group by customer_id,cnt

[@gothams1195]

with cte as
(
select name,
sum(case when floor=1 then 1 else 0 end) as 1st,
sum(case when floor=2 then 1 else 0 end) as 2nd,
count(1) as total_visits,
group_concat(distinct(resources)) as total_resources from entries group by name
)
select
name,
case when 1st>2nd then 1 else 2 end as m,
total_visits,
total_resources
from cte

[@ft_peakhd2921]

Best one yet! very good solution bro

[@satyajitbiswal6162]

this logic is not good as because we are using hardcorded.if 2/3 more input will come we need to change the querry

[@user-zx1ii2cx2j]

(enjoy my simple ans)
with cte_1 as
(select name,count(*) no_of_times_visited,
group_concat(distinct resources) resources_used
from entries group by name),
cte2 as
(select name,floor,max(c) from
(select name,floor,count(*) c from entries group by name,floor order by count(*) desc) a
group by name)
select cte_1.*,floor from cte_1 join cte2 using (name)

[@sivaganesh3224]

Select name,count(floor)total visist,max(floor)most visits,max(resources) || ' ,' || min(resources) resources used from table name group by name

[@exam_prep_cat5605]

with floor_visits as (select * from (select name ,floor, row_number() over(partition by name order by floor_visit_count desc ) as rk from (select name , floor, count(*) as floor_visit_count from entries group by 1,2)t)temp where rk=1)
select name ,(select floor from floor_visits where name=entries.name ) as _most_floor_visited ,count(*) as freq_visit, group_concat(distinct resources) as resources_used from entries group by 1

[@ankitbansal6]

Thanks for posting 👏

[@shalinimeena7863]

select name,sum(visits),(case when max(cnt) then floor end) as floor, STRING(distinct resources,',') as res
from(select name,count(1) as visits,floor,count(floor) as cnt,resources
from entries
group by name,floor)aa
group by name;

[@vikhyatjalota2213]

My Solution :
with cte_1 as (
select name,count(name) as total_visits,
group_concat(distinct resources) as resources
from
entries
group by name),
cte_2 as(
select name,floor as max_visited_floor from(
select floor,name,count(floor) as floors,dense_rank() over(partition by name order by count(floor) desc) as rnk
from
entries
group by 1,2) new
where rnk =1
)
select name,total_visits,max_visited_floor,resources
from cte_1
join cte_2 using(name)

[@nihal2725]

Easy to read solution:
with visits as
(select name,floor,
count(1) as no_of_floor_visits,
rank()over(partition by name order by count(1) desc) as rankd
from entries
group by name,floor)
select v.name,total_visits,floor as most_visited_floor,used_resources from visits v
join
(select name,string_agg(resources,',') as used_resources from (select distinct name,resources from entries)x
group by x.name)B
on
v.name=B.name
join
(select name,count(*) as total_visits from entries group by name)C
on v.name=C.name
where rankd=1

[@Aman-lv2ee]

with entries_cte as (
select name, floor, resources,
count(name)over(partition by name) as total_count,
count(name)over(partition by name, floor) as floor_count,
listagg(distinct resources, ',') over(partition by name) as resources_used
from entries
)
select name,total_count,floor,resources_used,
row_number()over(partition by name order by floor_count desc) as rn
from entries_cte
qualify rn = 1

[@lakshmanlee3579]

Code :
with cte as (select name,resources,
count(name) over (partition by name order by name) as no_of_visits,
floor,
count(floor) over (partition by name,floor order by name) as most_visited_floor
from entries),
final as (
select name,no_of_visits,
first_value(floor) over (partition by name order by most_visited_floor desc) as most_visited_floor,
resources
from cte)
select name,no_of_visits,most_visited_floor,group_concat(distinct resources) as resource_used
from final
group by name,no_of_visits,most_visited_floor

[@Gaurav-wy2wm]

This is my solution :-
select name,floor,count(floor) as floor_count,group_concat(distinct resources) as resou from entries group by name ;

[@ankitbansal6]

We need the most visited floor

[@sailajatumu3682]

This is another way of writing

[@stat_life]

#easiest #solution #mysql
with cte as(
select *,
row_number() over(order by floor desc) as most_visited_floor
from entries
)

select
name,
count(name) as total_visits,
most_visited_floor,
group_concat(distinct resources) as resouces_used
from cte
group by 1

[@mrrathore55]

Outstanding.

[@ankitbansal6]

Thank you.

[@Hope-xb5jv]

with cte as
(
select name,
floor,
count(floor) as visit
from
entries
group by
name,floor
)
select a.name,
c.Total_visit,
a.Most_visited_floor,
b.Resource_used
from
(select name,
STRING_AGG(resources,',') as Resource_used
from
( select distinct name,resources from entries) as query
group by name) b
join
(select name,
floor as Most_visited_floor
from cte
where visit = (select max(visit) from cte)
group by name,floor ) a
on a.name = b.name
join
(select
name,
count(Name) as Total_visit
from
entries
group by
name) c
on a.name = c.name

[@msd7451]

with table1 as
(
select t.name,t.floor as most_visited_floor
from
(select e.name,e.floor,count(*) as count_floor_visited,
row_number() over(partition by e.name order by count(*) desc) as rn
from entries as e
group by e.name,e.floor
) as t
where t.rn=1
),
table2 as (
select `name`,count(*) as total_visits,group_concat(distinct resources) as resources
from entries
group by `name`)
select t1.name,t2.total_visits,t1.most_visited_floor ,t2.resources
from table1 as t1
join table2 as t2
on t1.name=t2.name

[@shankrukulkarni3234]

with tmp_tbl1 as
(
select name,floor as most_visited_floor from
(select name,floor,count(*),
rank() over(partition by name order by count(*) desc) as rn
from entries
group by name,floor)a
where rn=1),
tmp_tbl2 as (
select name,group_concat(distinct resources) as resource_used,
count(*) as total_visited from entries
group by name)
select a.name, b.total_visited, a.most_visited_floor, b.resource_used
from tmp_tbl1 a
inner join tmp_tbl2 b
on a.name=b.name;
using group_concat(distinct resources) in mysql

[@basab4797]

with A as (
select name,count(1) as total_visit
from [Practice].[dbo].[entries]
group by name),
B as (
select [name],[floor]
from
(
select [name],[floor] ,rank() over(partition by [name] order by cnt desc) rnk
from
(
select [name],[floor],count(1) as cnt
from [Practice].[dbo].[entries]
group by [name],[floor]
) sub) sub1
where rnk=1
),
C as (
select name , string_agg(resources,',') as resource_used
from
(
select distinct [resources],name
from [Practice].[dbo].[entries]
) sub
group by name
)
select A.name,
A.total_visit,
B.[floor] as floor_most_visted,
C.resource_used
from A
join B
on A.name=B.name
join C
on B.name=C.name

[@Abhisheksingh-vd6yo]

with cte_rank as
(select name,
floor,
count(floor) as most_visited
from entries
group by name,floor
)
select e.name,count(e.name) as total_visit,i.floor as most_visited_floor,group_concat(distinct e.resources)
from entries as e
inner join (select name,floor
from (select *,Rank() over(partition by name order by most_visited desc) as rnk from cte_rank)as r
where r.rnk=1) as i
on i.name=e.name
group BY e.name,i.floor;

[@psyche5184]

I mostly work with Impala/Hive, so here is a solution:
select
t3.name, t3.total_visits, t3.floor,t4.resources_used
from (select * from (select *,
row_number() over(partition by name order by floor_visits desc) as rn
from (select
name,
count() over(partition by name) as total_visits,
count() over(partition by name,floor) as floor_visits,
resources,floor
from entries) t1)t2
where rn=1) t3
left join
(select name, group_concat(distinct resources) as resources_used from entries group by 1)t4
on t3.name=t4.name;

[@rahulbansal3811]

Thanks for sharing Ankit just loved it

[@ankitbansal6]

Welcome

[@aishwaryabhangarshettra984]

amazing video

[@ankitbansal6]

Thank you 😊

[@kanchankumar3355]

Informative

[@ankitbansal6]

Thank you.

[@VishalSharma-hv6ks]

Great

[@ankitbansal6]

Thank you 😊

[@discovery4808]

Nice Video

[@ankitbansal6]

Thanks Alok bhai 🙂

[@SaurabhKumar-ic7nt]

with temp as(select name, floor,
count(1) as total_visit_per_floor, dense_rank() over(partition by name order by count(1) desc) as rn
From entries
group by name, floor)
select t.*, temp.floor as most_visited_floor from temp, (Select name, count(1) as total_visits, group_concat(distinct resources order by resources separator ',') as resources_used
from entries
group by name) as t where t.name = temp.name and temp.rn = 1;

[@AsifAnas-nu6iu]

SELECT name, count(*) as number_of_visit, group_concat(distinct resources) as used_resources FROM entries group by name;

[@satyajitbiswal6162]

left one column that most_visited_floor

[@jimitshah7636]

select name,
count(*) --times visited each floor,
mode(floor), --if you recall mean,median,MODE
GROUP_CONCAT(DISTINCT resources) --distinct to remove dups
from entries
group by name;

[@pavitrashailaja850]

Great videos u make. Keep going!

[@sakshamratra4199]

Loved it! Thanks for sharing!

[@ankitbansal6]

Thank you 😊

[@mrx7148]

Good explanation Ankit. Can you also try to provide equivalent queries supported in other database systems like Oracle, Postgres, MySQL etc. I know we can get the same using ChatGPT but sometimes it's not very accurate. Anyways kudos to your knowledge. Also what are your other recommended sources to improve on SQL knowledge including performance tuning.

[@somyasingh6968]

I think this will also work:
select name,count(*) as total_visits,
(select e1.floor from entries e1 where e1.name=e.name group by floor order by count(e1.floor) desc limit 1) as frequent_floor
,group_concat(distinct resources) as used_resources
from entries e
group by name;

[@playtrip7528]

nice solution, can u pls elaborate (select e1.floor from entries e1 where e1.name=e.name group by floor order by count(e1.floor) desc limit 1) as frequent_floor

[@shrinjaysoni99]

grt!!

[@zeeshansalim673]

select *
from entries;
with cte as
(select name,count(email) as total_visits,group_concat(distinct resources,',') as resources_used
from entries
group by name),
cte2 as
(select name,floor,count(floor) as floor_visit,
rank() over(partition by name order by count(floor)desc) as rn
from entries
group by name,floor)
select cte.name,cte.total_visits,cte2.floor as most_visited_floor ,cte.resources_used
from cte join cte2 on cte.name=cte2.name
where rn=1

[@kunalberry5776]

Thanks for this questions

[@sathwikdhanya5400]

with gr as ( select name,floor,count(1) as no_visit,
STRING_AGG(resources,',') as res
from entries
group by name ,floor
),
rnked as (select *,
sum(no_visit) over(partition by name) as tot_vis,
rank() over(partition by name order by no_visit) as rnk from gr),
dis_res as (select distinct name,resources from entries),
res_agg as (select name , STRING_AGG(resources,',') as res from dis_res group by name)
select a.name,a.floor as most_visited_floor,a.tot_vis as total_visits,b.res from rnked a
INNER JOIN res_agg b on a.name=b.name
where rnk=(select max(rnk) from rnked)

[@rishabhagarwal4164]

with temp as (select * , count(*) as floor_visit from entries group by name, floor),
temp2 as (select name, group_concat( distinct resources) as resources_used from entries group by name)
select t.name, sum(t.floor_visit) as total_visit, (case when t.floor_visit = max(t.floor_visit) then t.floor else 0 end)
as most_visit, t2. resources_used from temp t inner join temp2 t2 on t.name = t2.name
group by name;

[@ankitbansal6]

Thanks for posting 👏

[@satyajitbiswal6162]

WOULD IT WORK t.floor_visit = max(t.floor_visit)?

[@swamybittu7548]

I feel rank is giving just based on the name but not based on how many times he visited the floor

[@amithgshetty8171]

Rank needs to be applied on the count of floors visited in desc order

[@abhimistry9226]

thanks sir

[@nidhisingh4973]

You are just awesome

[@prabhatgupta6415]

with a as
(select name ,count(name) as total_visits from entries group by name),
b as(select name,floor as most_visited_floor from entries group by name,floor having count(floor) >1), c as
(select name,group_concat(distinct resources) as resources_used from entries group by name)
select a.name,total_visits ,most_visited_floor,resources_used from a join b on a.name=b.name join c on a.name=c.name

[@satyajitbiswal6162]

Would this work if the person visited floor 2 twice and floor 3 thrice?

[@mrs.vadivuvigram6397]

select Name,visited,Resources_used,
case when visited>1 then floor else 0 end as freq_visited from
(select name,floor,count(*) as visited,group_concat( distinct resources)
as Resources_used from entries group by name)a
group by name;

[@mrs.vadivuvigram6397]

select name,max(floor) as frequent_visit,count(*) as total_visited,
group_concat( distinct resources)
as Resources_used from entries group by name;

[@anirvansen5024]

Another MYSQL Solution
with base as (
select *,count(1) over(partition by name,floor) as repeatation from entries ),
visited_floor as (
select *,first_value(floor) over(partition by name order by repeatation desc) as most_visited_floor
from base order by name )
select name, count(1) as no_of_visits,most_visited_floor,group_concat(distinct resources) as resources_used
from visited_floor group by name,most_visited_floor;

[@anuragsekhri2315]

insightful

[@monasanthosh9208]

MYSQL Solution
Select Name,Total_Visit,Most_Visit_Floor,group_concat(distinct(Resources)) as Resources from
(Select *,first_value(floor) over (Partition by Name order by Visit desc) as Most_Visit_Floor from
(Select Name,Count(Floor) over (Partition by name) as Total_Visit,floor,
Count(Floor) over (Partition by name,Floor) as Visit,Resources from entries)N)N1
group by Name;

[@sinhaapurva25]

select Y.name,
Y.total_visits,
X.most_visited_floor,
Y.resources_used
from (select * from (select name,floor as most_visited_floor,count(floor) as count_of_most_visited_floor from entries
group by name,floor) S
group by S.name) X
join (select name,
count(*) as total_visits,
group_concat(distinct resources) as resources_used
from entries
group by name) Y
on X.name = Y.name;

[@ankitbansal6]

Where is the filter for most visited floor ?

[@sinhaapurva25]

@@ankitbansal6 It's in the lines 6-10.
I have created two tables. First found out the most_visited_floor for each name and created a table, aliased as X. And a second table to get the rest of the columns, aliased as Y. Then I have joined these two tables on the name column to obtain the final table.

[@ankitbansal6]

You have taken the count but where you are filtering for most visited floor by each person..

[@sinhaapurva25]

@@ankitbansal6 Got you. Thanks. I inserted another name 'C' to get this right. I used join on join on join.
select Y.name,
Y.total_visits,
X.most_visited_floor,
Y.resources_used
from (select S1.name,
S1.visited_floor as most_visited_floor
from (select name,
floor as visited_floor,
count(floor) as number_of_times
from entries
group by name,floor) S1
join (select name,
max(S.number_of_times) as number_of_times
from (select name,
floor as visited_floor,
count(floor) as number_of_times
from entries
group by name,floor) S
group by S.name) S2
on S1.number_of_times = S2.number_of_times
where S1.name = S2.name) X
join (select name,
count(*) as total_visits,
group_concat(distinct resources) as resources_used
from entries
group by name) Y
on X.name = Y.name;

[@ramakumarguntamadugu1299]

good one !

[@fog2640]

finished watching

[@abhishekp7375]

with cte as
(
select *
,count(*) over(partition by name) as total_visits
--to find how many times each floor the person visited
,row_number() over(partition by name,floor order by floor) as rn
,concat_ws(',',collect_set(resources) over(partition by name)) as resources_used
from entries
)
,cte2 as
(
select distinct name
,total_visits
,floor
,rn
--to find max among each floor he visited
,max(rn) over(partition by name,floor) as max
--to find max among all floor which he visited
,max(rn) over(partition by name) as person_max
,resources_used
from cte
)
select distinct
name
,total_visits
,floor as most_visted_floor
,resources_used
from cte2
where max == person_max

[@parthibanr2119]

@ankitbansal6, please write a query for the below scenario
Input : Ankit
Output:
A
n
k
i
t

[@jitheshj6764]

with cte as (
select name, floor, count(floor) as visited_floors, rank() over(partition by name order by count(floor) desc) as rnk
from entries
group by name, floor
)
select e.name, count(e.name) as total_visits, group_concat(distinct e.resources)as agg_reources, cte.floor
from entries e inner join cte on
e.name = cte.name
where rnk = 1
group by name

[@florincopaci6821]

Thank you!

[@ankitbansal6]

Glad you liked it 😊

[@amithgshetty8171]

Hello ankit sir, just wanted your opinion on one thing. Can we use mode() function on the floor number to get most visited floor? And just do group_concat on the resources to get the list of resources used? Please correct me if im wrong

[@sowbhagyathota2833]

with temp as (
select name, count(name) visits, listagg(distinct(resources),',') rsce from entries group by name),
visits as (select name, floor, count(floor) visits,
dense_rank() over(partition by name order by count(floor) desc) rnk
from entries group by name, floor order by name)
select t.name, t.visits, v.floor as most_visited_floor, t.rsce as resource_used
from temp t inner join visits v on t.name = v.name where v.rnk = 1

[@devangsareen8755]

this is mysql version
select name,count(email)as total_visits,max(floor)as most_floor_visited,distinct(resources)
from employee
groupby name;

[@vishnujatav6329]

Useful

[@Insights-hb1rv]

WITH CTE AS(
SELECT DISTINCT resources ,
name,COUNT(*) no_of_visited,floor,
(CASE WHEN COUNT(floor) > 1 THEN floor ELSE NULL END )as most_visited_floor
FROM entries
GROUP BY name,floor,resources
)
SELECT name,Count(*)no_of_visited,MAX(most_visited_floor) AS most_visited_floor, STRING_AGG(resources,',') AS resources FROM CTE
GROUP BY name

[@siddheshkalgaonkar2752]

I developed a solution without looking at yours. Great question though. The below snippet works with Postgres. The only change required is to replace the string_agg method with group_concat while running on MySQL.
with except_most_visited_floor as(
select name, count(*) as no_of_visits,
string_agg(distinct resources,',') as resources_used from entries
group by name
),
only_most_visited_floor as
(
select name,floor from entries group by name,floor
having count(floor) > 1
),
res as
(
select a.name,a.no_of_visits as total_visits,b.floor as most_visited_floor,
a.resources_used
from except_most_visited_floor a
join only_most_visited_floor b
on a.name=b.name
)
select name, total_visits, most_visited_floor,resources_used from res;

[@ankitbansal6]

Thanks for posting. Good stuff 👏

[@SS-lt1nf]

Would this work if the person visited say floor 2 twice and floor 3 thrice?

[@siddheshkalgaonkar2752]

@@SS-lt1nf Yes, I don't see a problem here. I am calculating it separately and then joining it so the number of visits is handled in a separate block.

[@satyajitbiswal6162]

@@siddheshkalgaonkar2752 no it will not work if the person visited say floor 2 twice and floor 3 thrice .then we need to change the querry

[@siddheshkalgaonkar2752]

@@satyajitbiswal6162 Can you show me how it will not work? or with an example considering my solution?

[@vinayghildiyal2727]

with visits as
(
select name,floor as most_visited_floor,
rank() over(partition by name order by count(*) desc) as rnk
from entries
group by name,floor
),cte as
(
select name,count(*) as total_visits,group_concat(distinct resources) as resources_used
from entries
group by name
)
select c.name,c.total_visits,v.most_visited_floor,c.resources_used
from cte as c
inner join visits as v
on c.name = v.name and v.rnk=1;

[@rajunaik8803]

**without distinct resources used**
with cte2 as(
select name,count(*) as total_visits from entries GROUP by name
),
cte1 as(
select name,floor,count(1) as no_of_floor_visit,rank() over(partition by name order by count(1) desc) as rnk from entries
GROUP by name,floor
),
cte3 as (select name,STRING_AGG(resources,',') as resources_used from entries group by name)
select cte1.name,cte1.floor as most_visted_floor,cte2.total_visits,cte3.resources_used from cte1 as cte1,cte2 as cte2,cte3 as cte3
where cte1.name=cte2.name and cte1.rnk=1 and cte3.name=cte1.name

[@ytofsaluja]

IN POSTGRES this DISTINCT is working with STRING AGG.
select entries.name,count(*) as total_visits,
STRING_AGG(DISTINCT entries.resources,',') as resources_used,t2.floor as max_floor
from entries
JOIN t2 on entries.name=t2.name
WHERE t2.rnk=1
Group by entries.name,t2.floor
order by entries.name asc
create temporary table t2 as
(select e.name,count(e.*) as no_of_floor_visits,e.floor,
rank() over(partition by e.name order by count(e.*) desc) as rnk
From entries e
group by e.name,e.floor)

[@jayosreedas7673]

I tried to solve the query using following approach
with alias as
(SELECT DISTINCT NAME, COUNT(*) as total_visits FROM EMP
GROUP BY NAME),
alias2 as
(select *, group_concat(distinct resources order by resources) as resources_used
from emp
group by name)
select name,total_visits,floor as most_vistited_floor,resources_used from(
select x.*,a.total_visits,a2.resources_used from (
select *, count(*) as counts from emp
group by name, floor) as x
join alias a
on x.name=a.name
join alias2 a2
on x.name=a2.name
group by x.name
having counts=max(counts)
) as y;
let me know if you think this approach will be effective with larger datasets, or do you foresee any potential limitations.

[@blazerider200]

Simplest Solution Available:
with abc as(
select name ,floor,count(floor) as floor_visited_count,resources as resources_used from entries group by name,floor,resources)
select name,floor as most_visited_floor,sum(floor_visited_count) as total_visits,GROUP_CONCAT(resources_used,',') as resources_using from abc
group by name having max(floor_visited_count)

[@user-sv6hh4gt4z]

is string_agg and group_concat the same thing, In my case string_agg is not working I do not why

[@pravinmahindrakar6144]

Hi Ankit,
Will that works
select name,
count(1) as no_of_visits,
floor as most_visited_floor,
group_concat(distinct resources) as resources
from entries group by name having count(floor)>1

[@playtrip7528]

amazing as always

[@apurvasaraf5828]

with cte as(select name , max(floor) as m from (SELECT *,RANK() OVER(PARTITION BY NAME ORDER BY C DESC) as r FROM
(select * ,COUNT(FLOOR) OVER(PARTITION BY NAME,FLOOR) AS C from entries)A)P
where r=1 group by name)
(select e.name,count(*) , string_agg(resources,',') ,
max(c.m) as mk from entries e join cte c on c.name=e.name group by e.name)

[@subimalkhatua2886]

with t_floor_cte as
(select name, sum(total_visits) total_visits,group_concat(floor) most_visited,group_concat(resource) resources
from
(
select name,address,floor,count(address) total_visits,
resource,row_number() over(partition by name,address order by count(floor) desc) as rnk
from entries group by floor,name,address,resource
)
t
group by name)
select name,total_visits,substring_index(most_visited, ',' ,1) as most_visited_floor,resources From t_floor_cte;

[@ankitbansal6]

Thanks for posting 👏