Honeywell SQL Interview Question | Print Movie Stars (⭐ ⭐ ⭐ ⭐⭐) For best movie in each Genre

[@simadas9133]

Ankit Sir, this is my solution: I tried avoiding the use of Window Function here with cte_1 as ( SELECT A.genre Genre, A.title Title, ROUND(AVG(B.rating),0) Average_Rating FROM movies A JOIN reviews B ON A.id = B.movie_id GROUP BY A.genre, A.title), cte_2 as (SELECT *, CASE WHEN Average_Rating > 4 THEN '*****' WHEN Average_Rating > 3 THEN '****' WHEN Average_Rating > 2 THEN '***' WHEN Average_Rating > 1 THEN '**' ELSE '*' END Rating FROM cte_1) SELECT * FROM cte_2 A WHERE A.Title IN (SELECT B.Title FROM cte_2 B WHERE B.Genre = A.Genre AND Average_Rating = (SELECT MAX(Average_Rating) from cte_2 C WHERE B.Genre = C.Genre))

[@bankimdas9517]

Before printing stars i was to solve but Ankit sir made it quite easy to move to the next step. Thanks a lot Ankit sir🙏

[@ankitbansal6]

Most welcome 😊

[@ishitasrivastava5923]

@@ankitbansal6 Can you tell which function to use in place of 'REPLICATE' in Snowflake ?

[@ankitbansal6]

@@ishitasrivastava5923 repeat : docs.snowflake.com/en/sql-reference/functions/repeat

[@rampavan9521]

i think dense_rank should be better than row_number .there might be having duplicate values with cte as( select m.genre,m.title,avg(r.rating) mn from movies m join reviews r on m.id = r.movie_id group by m.genre,m.title ) , cte2 as( select *,dense_rank() over(partition by genre order by mn desc) pn from cte ) select *,repeat('*',cast(round(mn) as int))from cte2 where pn=1 order by mn desc

[@VedanshiReddy131]

Hi Ankit,I am working on Sql for more than 9 yrs..I never come across the REPLICATe function..new learning today .Thanks for this. If possible solve this without using REPLICARe function.

[@ankitbansal6]

Yes using case when or by creating another static table

[@vanshhans5676]

Nice question 😃. Here is my solution - with cte1 as (select movie_id,AVG(rating) as avg_rating from reviews group by movie_id), cte2 as ( select m.id,m.genre,m.title,cte1.avg_rating,ROW_NUMBER() over(partition by m.genre order by cte1.avg_rating desc) as rn from movies as m inner join cte1 on cte1.movie_id=m.id) select genre,title,round(avg_rating,0),REPLICATE('*',round(avg_rating,0)) from cte2 where rn=1

[@jatendra4925]

Excellent Video...below is my contri with cte as ( select m.genre, m.title, avg(r.rating) as avg_rating, replicate('*',round(avg(r.rating),0)) as stars, rank() over(partition by m.genre order by avg(r.rating) desc) as rnk from movies m join reviews r on m.id= r.movie_id group by m.genre, m.title ) select genre, string_agg(title,', ') as title, max(stars) as stars from cte where rnk = 1 group by genre order by genre

[@rahulkushwaha6469]

with cte as ( select a.genre,a.title,round(b.avg_rating) as avg_rating, case when round(b.avg_rating) = 5 then '*****' when round(b.avg_rating) = 4 then '****' when round(b.avg_rating) = 3 then '***' when round(b.avg_rating) = 2 then '**' when round(b.avg_rating) = 1 then '*' end as stars ,dense_rank() over(partition by a.genre order by b.avg_rating desc) as rnk from movies a left join (select movie_id, avg(rating) as avg_rating from reviews group by 1 ) b on a.id=b.movie_id )select genre,title,avg_rating,stars from cte where rnk = 1; Have used Dense_rank() , as we can have tie in the ratings. Also if we don't know replicate() func.. we can use case. However not feasible for large distribution of cases.

[@jatendra4925]

we need top value, rank would be enough

[@macx8360]

please increase the frequency of your videos...we miss ur sql videos

[@ankitbansal6]

Sure 😊

[@apexemperor]

Looks like keeping all videos now for paid batch😅 ​@@ankitbansal6

[@MubarakAli-qs9qq]

Very nice. Vedio

[@Mr.Aman13..]

Hi Ankit, Awesome explanation! Thank you. I was thinking that we can also have a separate star table which will have numeric value and the stars concatened corresponding to that value. We can join that star table by avg_rating. This is because during interviews sometimes it becomes very hard remember some special/rarely used functions.

[@ankitbansal6]

Yes that will be a good idea 🙂

[@navaneeth6414]

with cte as with cte as ( select movie_id,round(avg(rating ),2) as average_rating from reviews group by movie_id), cte2 as ( Select id,genre,title,average_rating,ROW_NUMBER() over(partition by genre order by average_rating desc) as rn, star_representation from movies inner join cte on cte.movie_id = movies.id inner join star s on cast(cte.average_rating as int) = s.star_number ) select * from cte2 where rn =1 Hi Aman, See if this will work. I made another table called star with 1 to 5 numbers and its corresponding representation

[@suryakanthbhullha7425]

Rating for this channel *****😊

[@noufalrijal9811]

After completing the query, waited to see how the "*"'s were getting populated 😀 There is one concern regarding - whether to use ROW_NUMBER() or RANK() function, as for the genre comedy there is a tie between 2 titles.

[@NickJohns-q8c]

since we need top 1 so we can use any of this either rank() or row_number() answer will be same. you can read the diff b/w rank, dense_rank and row_number for clarity.

[@satyasaivarunhanumanthu6370]

@@NickJohns-q8c you are wrong bro, in genre comedy the 2 titles have the same average rating, in this case, we need to use rank only which gives the same rank to both titles but row_number assigns a unique value to each row

[@DesiGlamGaze-lc3bx]

Tell me one thing, What happen if I use DENSE_RANK instead of Row_Number, as I see there are same rating for two movies for genre = comedy

[@akashgoel601]

thanks, one small query.. for genre(comedy) with rating 4, there are 2 entries(mentioned below).. i am not sure if there was assumption in question to take the first one. Comedy Bridesmaids 4.000000 Comedy Anchorman: The Legend of Ron Burgundy 4.000000 considering above assumption posting my query: with cte as ( select distinct m.id,m.genre,m.title ,avg(r.rating) as avg1, ROW_NUMBER() OVER(partition by m.genre order by avg(r.rating) desc) as rn from movies m join reviews r on m.id=r.movie_id group by m.id,m.genre,m.title ) select genre,title,round(avg1,0) as rnd,REPLICATE('*',round(avg1,0)) rnd2 from cte where rn=1;

[@AmanRaj-p8w]

My Sql Solution: with cte as ( select m.*, r.*, round(avg(rating) over (partition by title, genre order by id)) as aver_by_gen from movies as m inner join reviews as r on m.id = r.movie_id order by id ) , cte2 as( select genre, title, rating, aver_by_gen,row_number() over (partition by genre order by aver_by_gen desc) as rw from cte ) select genre, title , repeat('*', aver_by_gen) AS rating from cte2 where rw = 1

[@florincopaci6821]

Hello solving with one select: select top (select count(distinct m.genre) from movies m) m.genre , m.title, replicate('*',round(max(avg(r.rating))over (partition by m.genre order by avg(r.rating) desc ),0))As stars from movies m join reviews r on m.id=r.movie_id group by m.genre, m.title order by row_number()over(partition by genre order by avg(r.rating) desc) Hope it helps.

[@abhisekrajput9113]

Hi Ankit ..thank you for great content ..one request when you are providing solution could you please use sql generic function as an example not all db supports replicate function..

[@ankitbansal6]

There would be alternatives to replicate . Just Google 😊

[@hariikrishnan]

Using this query i got only 1 record from Comedy Genre with cte as (select genre, title,rating, avg(rating) over (partition by genre, title order by genre rows between current row and unbounded following) as avg1 from movies left join reviews on id = movie_id order by genre), cte2 as (select genre, title, round(avg1) as avg1 from cte where (genre, avg1) in (select genre, max(avg1) from cte group by genre)) select * from

[@poornimasaravanan9389]

Instead of movie title we can use movie id also in group by right?

[@satyakighosh8156]

Please Start a Tsql Course Sir

[@Ilovefriendswebseries]

excellent explanation

[@Ashu23200]

Kya baat! jabardast

[@wordswisdomandmotivation4799]

replicate did't work in my sql workbench ?

[@ankitbansal6]

Repeat works

[@PritamPriyadarshan-qx1is]

Ankit, can we use case when statement to replace the ratings with * ?

[@ankitbansal6]

Yes that also works but imagine if the rating is out of 10 then it will be a long statement

[@Apromit]

Superb explamation. Love your content

[@grim_rreaperr]

Hi Ankit Bhai, at 3:47, we have ties for movie :- Bridesmaids and Anchorman : The Legend of Ron Burgundy which belong to comedy genre(see result row number 1 and 3). Should I use rank() over () and show them both as top rated or use row_number() over() and only show 1?

[@satyasaivarunhanumanthu6370]

Same doubt I have

[@tanisha.j16]

Can you validate this? Select genre,title,max(avg_rating),replicate('*',avg_rating) as starts from (Select a.genre, a.title,round(avg(b.rating)) as avg_rating From movies a left join reviews b on a.id=b.movie_id Group by 1,2 ) a group by 1 order by 1;

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@hairavyadav6579]

Sir I have one question if we can't use order by desc then how we get highest avg rating

[@hairavyadav6579]

Got it sir we use order by in row in function got it..........

[@manish7897]

Hi Ankit, can you provide video explanation of your dynamic pricing question, as it's quite a good and intuitive question.

[@ankitbansal6]

Which question?

[@SourishBiswas-t5u]

amazing explanation sir

[@vinothkumars7421]

Repeat function for postgres

[@vejandlaharikrishna5391]

Hi Ankit, I have issue. I have field1 and field2 whenever field2 getting Null value next column it will display field1 short name which we have null value in field2. Could please help me out on same

[@dfkgjdflkg]

always great content, thanks

[@akhtarazad4746]

You are genius 😊😊😊

[@vikas261196]

Can we also solve it like : WITH CTE AS ( SELECT m.genre, m.title, ROUND(AVG(r.rating)) AS avg_ratings, CASE WHEN ROUND(AVG(r.rating)) = 1 THEN '*' WHEN ROUND(AVG(r.rating)) = 2 THEN '**' WHEN ROUND(AVG(r.rating)) = 3 THEN '***' WHEN ROUND(AVG(r.rating)) = 4 THEN '****' ELSE '*****' END AS avg_ratings_in_stars FROM movies m INNER JOIN ratings r ON r.movie_id = m.movie_id GROUP BY m.genre, m.title ) SELECT genre, title, MAX(avg_ratings) AS max_avg_ratings, avg_ratings_in_stars FROM CTE GROUP BY genre;

[@nikbaigho8661]

wow , explained it very well !!

[@ankitbansal6]

Glad you liked it

[@007SAMRATROY]

with cte as ( select *, dense_rank() over(partition by m.genre order by r.avg_rating desc) as ranks from [samdb].[dbo].[movies] m inner join (Select movie_id, AVG(rating) as avg_rating from [samdb].[dbo].[reviews] group by movie_id) r on m.id = r.movie_id ) select genre, title, avg_rating, REPLICATE('*',round(avg_rating,0)) as stars from cte where ranks = 1 ; I used the above code. Why is SQL server not supporting QUALIFY clause in the way Teradata does? Is there any special settings for enabling QUALIFY clause in SQL Server? Can you please let me know?

[@shubhamjazz2012]

Hi, Actually Teradata and SQL Server has diff in syntax usage. Just like for teradata you can use column alias name directly in where clause but in SQL SERVER alias doesn't work in where clause.

[@007SAMRATROY]

@@shubhamjazz2012 yes. and I went through an article yesterday where it stated that QUALIFY can be used in SQL Server but the analytical function column must be defined in the Select statement. But I could not get it to work in my SQL Server 2022.

[@vishalsonawane.8905]

upload 1 video on daily basis.

[@meropahad7537]

at 3:16 Why are we taking both genre and title while doing group by please explain. why not only genre is sufficient since titles are already different

[@SunilKumar-kz2hg]

Every movie in each genre will be watched by multiple people before giving a rating. So, in order to get the average rating for every movie, we need to group by at a genre, movie level and not just at movie leve. Simply put, the level of data is at Genre and MovieTitle for ratings and not just at Genre, that is why we are taking both these at the group by level. Hope you understood this now.

[@meropahad7537]

@@SunilKumar-kz2hg I understood 😊thanks for the reply , if we group by using id column then it will also do the same thing just wanted to know because somewhere I read that using multiple columns in group by reduces performance so just thinking from performance point of view if data in table is large?

[@SunilKumar-kz2hg]

@@meropahad7537 See, as long as the Genre and ID are one to one mapped which means, the one column can be used instead of another, it won't be a problem. But if the ID column is not one to one mapped, then the group by will give different results. Yes, more the columns in group by the performance may get impacted but I don't think it will be too much that we should be worried about. I hope it helps

[@Dgytruuv]

Hello sir please help me...can I get job as data analyst having 4+ year career gap?? And how

[@suvrajitdeb3839]

Thanks u very much

[@ankitbansal6]

Most welcome😊

[@vamsivamsi2029]

Thanks @ankit

[@NickJohns-q8c]

sir i got diff. movie name in comedy genre. 🤔🤔🤔🤔🤔🤔🤔 with cte as( select distinct m.title as title,m.genre as genre ,avg(r.rating) as ag_rating , ROW_NUMBER() over ( partition by m.genre order by avg(r.rating) desc) as rn from movies m inner join reviews r on m.id=r.movie_id group by m.title, genre) select genre, title, round(ag_rating,0) as avg_rating, REPLICATE('*',round(ag_rating,0)) as stars from cte where rn=1; ans- Action The Dark Knight 5.000000 ***** Comedy Anchorman: The Legend of Ron Burgundy 4.000000 **** 🤔🤔🤔 Drama The Godfather 5.000000 *****

[@shubhamjazz2012]

Because in comedy genre two movies have same avg rating ==4.0000

[@kanchanmathur-o9d]

Hi Ankit, why have we not used dense_rank. dense_rank gives a different output as there are 2 movies in the comedy genre who have highest rating as 4 Action The Dark Knight 5 Comedy Bridesmaids 4 Comedy Anchorman: The Legend of Ron Burgundy 4 Drama The Godfather 5

[@sunnykaushik9637]

Very good explanation Ankit

[@monasanthosh9208]

MYSQL Solution With CTE as (Select *,dense_rank() over (Partition by Genre Order by AVG_Rating desc) as RN from (Select *,Round(AVG(Rating),1) as AVG_Rating from (Select M.id,M.Genre,M.Title,R.Rating from Movies M Left Join Reviews R on M.id=R.Movie_Id)N Group by id)N) Select Genre,group_concat(Title),repeat("*",round(Avg_Rating,0)) as Rating from CTE Where Rn=1 Group by Genre; Instead of row_Number We can Use Rank Function Because More than a Movie can Have Same Ratings.