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.

Great videos u make. Keep going!

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;

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

10 ай бұрын

You got this!

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

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 😊

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

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

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

2 жыл бұрын

Thank you Vaibhav 😊

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

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

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

@ankitbansal6

9 ай бұрын

Great job!

Loved it! Thanks for sharing!

@ankitbansal6

2 жыл бұрын

Thank you 😊

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

You are just awesome

Thanks for sharing Ankit just loved it

@ankitbansal6

2 жыл бұрын

Welcome

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

@ankitbansal6

11 ай бұрын

Keep going 💪

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

@ankitbansal6

Жыл бұрын

Thank you, I will

You are awesome. I can think SQL now.

@ankitbansal6

2 жыл бұрын

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

Good question. Pls make more such videos

@ankitbansal6

2 жыл бұрын

Sure.

Thanks for this questions

amazing as always

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

2 жыл бұрын

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

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;

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 👏

This is another way of writing

good one !

Outstanding.

@ankitbansal6

2 жыл бұрын

Thank you.

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;

insightful

amazing video

@ankitbansal6

2 жыл бұрын

Thank you 😊

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

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.

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 🥳🥳

I am just wondering.. in your solution ..in cte floor_visit ..we are getting correct ranking of most visited floors because of group by name,floor ..so no need of adding name in "partition by" of rank function ..and anyways this grouping is on name,floor level ..so only "partition by" at name level will give wrong result ..here I believe "order by count(1) desc" itself is ranking groups of each name,floor combination count wise in descending order.

DISTINCT resources without the CTE works for me on PostgreSQL

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

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 😊

Informative

@ankitbansal6

2 жыл бұрын

Thank you.

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

thanks sir

Nice Video

@ankitbansal6

2 жыл бұрын

Thanks Alok bhai 🙂

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

Great

@ankitbansal6

2 жыл бұрын

Thank you 😊

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

finished watching

Useful

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

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

Thank you!

@ankitbansal6

2 жыл бұрын

Glad you liked it 😊

'STRING_AGG' is not a recognized built-in function name .iam getting this error ankit. i tried in 2016 and 2012 version.?

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;

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

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;

Thanks brother!!!!!! For these videos.

@ankitbansal6

11 ай бұрын

My pleasure!!

I initially tried below query (wanted to use window functions instead of group by) but it gives duplicate rows. Can you please help me correct that? Distinct is something if required then I think there's some issue in the logic. with temp as ( select name, count(floor) over(partition by name) vis_cnt, floor, count(floor) over(partition by name,floor) vis_cnt_per_emp_per_flr, resources, listagg(distinct resources,',') over(partition by name) resoueces_used from input ), temp1 as ( select name, vis_cnt, floor, dense_rank() over(partition by name order by vis_cnt_per_emp_per_flr desc) rnk , resoueces_used from temp ) select name,vis_cnt,floor,resoueces_used from temp1 where rnk=1;

@exanode

Жыл бұрын

Here's my query. --Just use a subquery for listagg WITH RESOURCES_LIST AS ( SELECT NAME, LISTAGG(RESOURCES, ',') WITHIN GROUP (ORDER BY RESOURCES) RESOURCES FROM( SELECT DISTINCT NAME,RESOURCES FROM ENTRIES GROUP BY NAME, RESOURCES ) GROUP BY NAME ), MOST_VISITED_FLOOR AS ( SELECT NAME, FLOOR FROM ( SELECT NAME,FLOOR, DENSE_RANK() OVER(PARTITION BY NAME ORDER BY COUNT(FLOOR) DESC) 1_IF_MOST_VISITED_FLOOR FROM ENTRIES GROUP BY NAME,FLOOR ) WHERE 1_IF_MOST_VISITED_FLOOR = 1 ) SELECT E.NAME, COUNT(E.NAME) VISITS,RL.RESOURCES,MVF.FLOOR FROM ENTRIES E INNER JOIN RESOURCES_LIST RL ON E.NAME = RL.NAME INNER JOIN MOST_VISITED_FLOOR MVF ON MVF.NAME = E.NAME GROUP BY E.NAME,RL.RESOURCES, MVF.FLOOR ;

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

2 жыл бұрын

Thanks for posting 👏

@satyajitbiswal6162

Жыл бұрын

nice one

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

Hello All , String_agg function is not working in SSMS , it is being displayed as "No such inbuilt function Available", can anyone plz guide me on this. Thanks

GROUP_CONCAT also tried its saying not a built in function.

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

hello sir, i have some questions from your test. I can mail you those. can you please solve them in a video. Atleast a few of them

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

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

2 жыл бұрын

Thanks for posting 👏

#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

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)

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

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;

hi, Can anyone help me understand, how long should it take to solve these Questions in Interviews.

Ankit Sir..... is there any condition or a rule perhaps that using too much cte will lead to bulky queries??

@ankitbansal6

8 ай бұрын

If data volume is high try temp tables

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

2 жыл бұрын

Thank you 😊

(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)

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;

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

@ankitbansal6

11 ай бұрын

We need the most visited floor

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

@amithgshetty8171

2 ай бұрын

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

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;

Is there any specific reason for using count(1) instead of count(*)?

@ankitbansal6

10 ай бұрын

It's the same. Anything is fine.

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!!

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.

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

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

2 жыл бұрын

Thanks for posting 👏

@satyajitbiswal6162

Жыл бұрын

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

how to select max visited floor with out using rank funtion.......?

@playwithvihaan7601

Жыл бұрын

use co-related nested query.

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)

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;

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

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;

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

2 жыл бұрын

Thanks for posting. Good stuff 👏

@SS-lt1nf

2 жыл бұрын

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

@siddheshkalgaonkar2752

2 жыл бұрын

@@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?

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

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

6/141

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;

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;

**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

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

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?

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)

here is solution for MYSQL - select name, sum(visitCount) as total_visit, case when visitCount = max(visitCount) then floor end as most_visited_floor, GROUP_CONCAT (resources ) as resources from ( select name,floor,count(1) as visitCount,GROUP_CONCAT ( distinct resources ) as resources from Entries group by name,floor )data group by name;

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

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

6 ай бұрын

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

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

2 жыл бұрын

Where is the filter for most visited floor ?

@sinhaapurva25

2 жыл бұрын

@@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

2 жыл бұрын

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

@sinhaapurva25

2 жыл бұрын

@@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;