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.

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!

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

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

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

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;

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;

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

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

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

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

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;

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)

Good question. Pls make more such videos

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

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;

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 !

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

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;

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

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

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

You are awesome. I can think SQL now.

Great videos u make. Keep going!

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

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

DISTINCT resources without the CTE works for me on PostgreSQL

Thanks for sharing Ankit just loved it

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

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;

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;

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;

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

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

This is another way of writing

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

Loved it! Thanks for sharing!

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

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

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

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

Outstanding.

amazing video

Thanks for this questions

You are just awesome

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

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;

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;

This is my solution :- select name,floor,count(floor) as floor_count,group_concat(distinct resources) as resou from entries group by 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

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

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

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

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

Informative

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;

#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

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;

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;

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

Nice Video

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

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

amazing as always

good one !

finished watching

Great

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

thanks sir

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

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;

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;

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;

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

Thank you!

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

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

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.

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;

insightful

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;

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

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

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

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

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

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)

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;

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

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)

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

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

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