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

Рет қаралды 11,761

2 ай бұрын

In this video we will discuss solution of a SQL interview problem asked in Honeywell interview. Here is the script:
CREATE TABLE movies (
id INT PRIMARY KEY,
genre VARCHAR(50),
title VARCHAR(100)
);
-- Create reviews table
CREATE TABLE reviews (
movie_id INT,
rating DECIMAL(3,1),
FOREIGN KEY (movie_id) REFERENCES movies(id)
);
-- Insert sample data into movies table
INSERT INTO movies (id, genre, title) VALUES
(1, 'Action', 'The Dark Knight'),
(2, 'Action', 'Avengers: Infinity War'),
(3, 'Action', 'Gladiator'),
(4, 'Action', 'Die Hard'),
(5, 'Action', 'Mad Max: Fury Road'),
(6, 'Drama', 'The Shawshank Redemption'),
(7, 'Drama', 'Forrest Gump'),
(8, 'Drama', 'The Godfather'),
(9, 'Drama', 'Schindler''s List'),
(10, 'Drama', 'Fight Club'),
(11, 'Comedy', 'The Hangover'),
(12, 'Comedy', 'Superbad'),
(13, 'Comedy', 'Dumb and Dumber'),
(14, 'Comedy', 'Bridesmaids'),
(15, 'Comedy', 'Anchorman: The Legend of Ron Burgundy');
-- Insert sample data into reviews table
INSERT INTO reviews (movie_id, rating) VALUES
(1, 4.5),
(1, 4.0),
(1, 5.0),
(2, 4.2),
(2, 4.8),
(2, 3.9),
(3, 4.6),
(3, 3.8),
(3, 4.3),
(4, 4.1),
(4, 3.7),
(4, 4.4),
(5, 3.9),
(5, 4.5),
(5, 4.2),
(6, 4.8),
(6, 4.7),
(6, 4.9),
(7, 4.6),
(7, 4.9),
(7, 4.3),
(8, 4.9),
(8, 5.0),
(8, 4.8),
(9, 4.7),
(9, 4.9),
(9, 4.5),
(10, 4.6),
(10, 4.3),
(10, 4.7),
(11, 3.9),
(11, 4.0),
(11, 3.5),
(12, 3.7),
(12, 3.8),
(12, 4.2),
(13, 3.2),
(13, 3.5),
(13, 3.8),
(14, 3.8),
(14, 4.0),
(14, 4.2),
(15, 3.9),
(15, 4.0),
(15, 4.1);
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

Пікірлер: 64

@Apromit 2 ай бұрын

Superb explamation. Love your content

@dfkgjdflkg 2 ай бұрын

always great content, thanks

@Ashu23200 Ай бұрын

Kya baat! jabardast

@sravankumar1767 2 ай бұрын

Superb explanation 👌 👏 👍

@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

@user-pz5sl9tt5z 2 ай бұрын

amazing explanation sir

@Ilovefriendswebseries Ай бұрын

excellent explanation

@bankimdas9517 2 ай бұрын

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 2 ай бұрын

Most welcome 😊

@ishitasrivastava5923 2 ай бұрын

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

@ankitbansal6 2 ай бұрын

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

@suryakanthbhullha7425 2 ай бұрын

Rating for this channel *****😊

@VedanshiReddy131 2 ай бұрын

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 2 ай бұрын

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

@akhtarazad4746 2 ай бұрын

You are genius 😊😊😊

@vamsivamsi2029 2 ай бұрын

Thanks @ankit

@user-dw4zx2rn9v 2 ай бұрын

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

@nikbaigho8661 2 ай бұрын

wow , explained it very well !!

@ankitbansal6 2 ай бұрын

Glad you liked it

@florincopaci6821 2 ай бұрын

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.

@suvrajitdeb3839 2 ай бұрын

Thanks u very much

@ankitbansal6 2 ай бұрын

Most welcome😊

@poornimasaravanan9389 2 ай бұрын

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

@hariikrishnan 2 ай бұрын

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

@vejandlaharikrishna5391 2 ай бұрын

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

@satyakighosh8156 2 ай бұрын

Please Start a Tsql Course Sir

@macx8360 2 ай бұрын

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

@ankitbansal6 2 ай бұрын

Sure 😊

@apexemperor 2 ай бұрын

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

@rahulkushwaha6469 2 ай бұрын

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

@Mr.Aman13.. 2 ай бұрын

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 2 ай бұрын

Yes that will be a good idea 🙂

@navaneeth6414 2 ай бұрын

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

@sunnykaushik9637 2 ай бұрын

Very good explanation Ankit

@grim_rreaperr 2 ай бұрын

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 2 ай бұрын

Same doubt I have

@abhisekrajput9113 2 ай бұрын

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 2 ай бұрын

There would be alternatives to replicate . Just Google 😊

@noufalrijal9811 2 ай бұрын

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.

@user-fc8dz5kn2y 2 ай бұрын

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 2 ай бұрын

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

@vishalsonawane.8905 2 ай бұрын

upload 1 video on daily basis.

@Dgytruuv 2 ай бұрын

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

@manish7897 2 ай бұрын

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

@ankitbansal6 2 ай бұрын

Which question?

@wordswisdomandmotivation4799 2 ай бұрын

replicate did't work in my sql workbench ?

@ankitbansal6 2 ай бұрын

Repeat works

@PritamPriyadarshan-qx1is 2 ай бұрын

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

@ankitbansal6 2 ай бұрын

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

@007SAMRATROY 2 ай бұрын

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 2 ай бұрын

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 2 ай бұрын

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

@meropahad7537 2 ай бұрын

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 2 ай бұрын

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 2 ай бұрын

@@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 2 ай бұрын

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

@user-fc8dz5kn2y 2 ай бұрын

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 2 ай бұрын

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

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

@vikas261196 2 ай бұрын

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;

@user-rr9ro8xu4w 2 ай бұрын

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