Advanced SQL Project | Netflix Data Analysis Using SQL (Guided) - Portfolio Series #4/10 - Datasets

[@zero_analyst]

🎯 Get Latest SQL Guided Project (1M sales records) 🎯topmate.io/zero_analyst/1237072 🎯 Get Latest End to End Advanced SQL Project (Amazon)+ 70 Business Problems kzbin.info/www/bejne/gGrUd3x3fZxrbrM 🐍 All Python Projects: Python ETL - Data Cleaning Project kzbin.info/www/bejne/q3W0gGyuhMxnrsk

[@nowshadktasin]

In question number 4, a TRIM () function should be used wrapping the UNNEST(), or else it will show duplicate values (eg. in your query it shows United States twice)

[@rohit_vora]

que :5 select title, substring(duration, 1,position ('m' in duration)-1)::int duration from Netflix where type = 'Movie' and duration is not null order by 2 desc limit 1

[@pavankumar12121]

I CAN SAY ONE THING: Your channel is one of the Best not, THE BEST channel for SQL; you will cover all. appreciate your efforts for the yt community

[@zero_analyst]

Wow, thanks!

[@MadhuriJana-j7m]

Which one is best channel for Power BI, good project

[@salimmuhammed9400]

Question number 5: select * from (select distinct title as movie, split_part(duration,' ',1):: numeric as duration from netflix where type ='Movie') as subquery where duration = (select max(split_part(duration,' ',1):: numeric ) from netflix)

[@ammartouati5706]

Yes, the longest movie for question 5 is incorrect below my query for it : -- 5 identify the longest movie WITH duration_t AS ( SELECT title, type, CAST(LEFT(duration, PATINDEX('%[^0-9]%', duration + ' ') - 1) AS INT) AS duration_minutes FROM netflix_titles WHERE type = 'movie' ) SELECT TOP 1 title, duration_minutes FROM duration_t WHERE duration_minutes IS NOT NULL ORDER BY duration_minutes DESC; -- Other solution with RANK() WITH duration_t AS ( SELECT title, type, CAST(LEFT(duration, PATINDEX('%[^0-9]%', duration + ' ') - 1) AS INT) AS duration_minutes FROM netflix_titles WHERE type = 'movie' ) SELECT Title, duration_minutes, long_ranking From ( Select title, duration_minutes, rank() over (order by duration_minutes DESC) as long_ranking from duration_t ) as ranked_movies where duration_minutes is not null and long_ranking=1

[@LoneKim98]

Thank you for this project I'm really grateful to you... And I would like to advice you to make your personal playlist as private to make your account more professional ❤

[@zero_analyst]

Thank you noted!

[@echodelta7680]

Good dataset for teaching and well presented. The wording of the query-10 could have been better, and query-14, clearer, but overall nice exposure for candidates. Appreciate your efforts.🥂

[@zero_analyst]

Noted!

[@ManishaSenapati25]

Thankyou fr bringing this out, i was soo scared to learn sql but your way of teaching is making it easier . Thank you for all the efforts and time you have put to bring this 💖💯

[@zero_analyst]

Happy to help

[@andreafrillian9435]

bro i think in #4 we have to "TRIM" our query so that {'United States'} and {' United States'} can be combine into single {'United States'} ~~

[@HandyCliffy]

Alternatively, just add a SPACE after the comma in the UNNEST function i.e. UNNEST(string_to_array(country, ', '))

[@ashwini_gujalwar]

@@HandyCliffy I did the same and it's working 👍

[@shawonalam6780]

Hi @Zero Analyst Your qs 5. "Max duration of the longest Movie " ans(99 min) is wrong.

[@rohit_vora]

there are some space that needed to remove for correct count in que 09 : select *, count(1) cnt from (select trim(unnest(string_to_array(listed_in, ','))) content from Netflix) group by 1 order by 2 desc

[@AmanSharma-tn3dy]

I think you have done a wrong query in Q5. we have a movie name 'Black Mirror: Bandersnatch' which is of 312 min select top 1 title, type, cast(Replace(duration, 'min', '') as int) as duration from Netflix where type='Movie' order by duration desc If I am wrong, pls correct me.

[@gospelmoto2833]

Nice Video! Im learning alot, TYVM. However, I have a question -Why not import directly the csv dataset without needing to define the table columns? Could it be done this way?

[@ArunKumar-oi3tq]

Easy !! but Awesome !! keep doing !!

[@zero_analyst]

Thank you!

[@NaveenNaveen-gd9fi]

It is very useful for us ,put video like this bro

[@zero_analyst]

Ok thank you

[@rajanchaurasiya1734]

Here rating is basically a category

[@ravikanth4715]

Bro I thought you would help me to build logic for sql queries but aap toh dusre hi direction me jaa rahe ho. 😊

[@Handle.smiles]

bro ans 5 is wrong as there are movies with ttime more than 100 min and also there is no data cleaning in show_id 5542, 5795,& 5814 the duration is written on rating column

[@Handle.smiles]

for ans 5 use - select title, max(CAST(SUBSTRING(duration,1,POSITION(' ' IN duration)-1)as INT)) as maximun_lenght from netflix where type = 'Movie' and duration is not null group by 1 order by 2 desc

[@zero_analyst]

We need to use split part functions to take out the number and than select the max

[@ammartouati5706]

@@zero_analyst Yes, the longest movie for question 5 is incorrect below my query for it : -- 5 identify the longest movie WITH duration_t AS ( SELECT title, type, CAST(LEFT(duration, PATINDEX('%[^0-9]%', duration + ' ') - 1) AS INT) AS duration_minutes FROM netflix_titles WHERE type = 'movie' ) SELECT TOP 1 title, duration_minutes FROM duration_t WHERE duration_minutes IS NOT NULL ORDER BY duration_minutes DESC; -- Other solution with RANK() WITH duration_t AS ( SELECT title, type, CAST(LEFT(duration, PATINDEX('%[^0-9]%', duration + ' ') - 1) AS INT) AS duration_minutes FROM netflix_titles WHERE type = 'movie' ) SELECT Title, duration_minutes, long_ranking From ( Select title, duration_minutes, rank() over (order by duration_minutes DESC) as long_ranking from duration_t ) as ranked_movies where duration_minutes is not null and long_ranking=1

[@ArunB-x9j]

What is MySQL equivalent of STRING_TO_ARRAY() and UNNEST() ?

[@Jreltaweel]

thank you habibi

[@bhojrajgaddam749]

Hey bro, could you please adjust your camera and screen so they're aligned? Turning your head so often might strain your neck over time. It would be great if you could set them up in a way where you don’t have to keep turning your head

[@zero_analyst]

Thank you so much mate! will consider it for sure!

[@novicewater]

how can we solve question 4 using mysql, i am not able to find solution to split cell into multiple rows , Pls guide.

[@Pujitha-x8u]

Hii , This was an excellent video . I only have one question : In 13th question , why release_date column is used instead of date_added . In the video you mentioned we can either both of it but the output differs for both, so you could please clarify it

[@zero_analyst]

Release date is the actual film release date! Date added means its added date on Netflix!

[@mubarakshah2865]

bro give the zomato database, i want to perform those complex queries

[@zero_analyst]

Please check the link in the video description! Its a paid datasets for zomato project1

[@sreelekshmib774]

I cant download the dataset link :( . Did you removed it from the description?

[@sambugarisupraja]

in question 2 we did rank partition by and in question number 5 y didnt we do the same thing here as well?

[@sambugarisupraja]

and there are a lot of null values present in the data y didnt we remove them ?

[@MOTIVATIONALQUOTE-wt3de]

hlo sir, can you tell how you move all the table name to right indentation ? like in video you select all the things and then you move it in right. but how?

[@zero_analyst]

Shift Tab --> reverse Tab --> go forward

[@ashishmishra7829]

Dear I have knowledge of MySQL but how can i write query of postgreSQL in MySQL workbench.Does it works?

[@zero_analyst]

Yes you can use same syntax!

[@tensionfree494]

-string function is not working in sql workbench..do u know how to solve this issue? @@zero_analyst array_to

[@hamarabharat1930]

when i'm importing the data it just importing 100 records, why ?

[@AmanSharma-tn3dy]

use ms sql server it allows you to import null values also, but mysql doesn't

[@chalamohamed2013]

Hello Sir, Really appreciate your efforts but I am disappointed beceause you haven't clean your dataset, how could say that 80min is a rating ! You have juste wasted my time downloading the dataset :(

[@zero_analyst]

Appreciated your feedback! This is a 100% sql project shown how you can solve business problems using sql. Further you can clean the data at first place that’s your choice or you can solve problems without cleaning datasets! I didn’t asked you to not clean the datasets! I have covered multiple functions in this project like how to work with text data! I am sure you learned something. Tech is all about fixing problems if you don’t find solutions don’t think you have wasted your time.

[@Gam_199]

can i add this project in my resume ?

[@zero_analyst]

@@Gam_199 Yes you can!

[@chalamohamed2013]

I can't load data, I encoutered errors with the description column, I am using MS SQL, could anyone help me please ?

[@zero_analyst]

I will make a video soon showing to import using MySQL!

[@karthikkumarreddykotha7880]

Hi bro this is Karthik while I'm trying to import the Netflix dataset into the Netflix table it's showing error after giving file path and enabling header after selecting "ok" it showing error 'columns', How to slove this error bro.

[@Morningbcode]

Just refresh and try importing

[@babayaga4459]

I'm getting the same error and it same even after refreshing what should i do

[@zero_analyst]

You can create the table again follow step by step with me in the video it will work.

[@SURAJKUMAR-dx1uz]

when i am loading the data set into mysql workbench it shows onlt 103 rows but the dataset is having 8008 rows please explain

[@zero_analyst]

Hi due to null values you may have issues in mysql wb! Please use Postgres + PgAdmin check my installation guide video!

[@sidraomer5894]

load the data through mysql command line

[@sidraomer5894]

kzbin.info/www/bejne/al7aqJ2uZ818hM0

[@omkarshinde8154]

He i use mysql but unable to load data only 200 records r inserted..what is issue..

[@zero_analyst]

Will make a video on it! i recommend you to use PostgresSQL You can watch my setup video! kzbin.info/www/bejne/rmiWlaOXjt95pbs

[@Yashvlog007]

Can i do these at my sql workbench

[@zero_analyst]

Yes you can!

[@sidraomer5894]

@@zero_analyst but some functions don`t work in mysql like split_part,unest,string_to_array. What are their alernatives?

[@discoveringdreams8677]

Why should not be use Mysql??

[@zero_analyst]

You can use it!

[@UjjwalPatel-mj7wq]

Hello zero analyst I have a question as like you I have a macbook m3 in your video you can simply click on your CSV file and launch it in excel file is this true. My question is are you buying office 365 or any other method you can use plss reply me to solve my problem. I don't have install ms excel or other microsoft apps but I want it for free

[@zero_analyst]

Hello mate i use excel 365

[@UjjwalPatel-mj7wq]

@@zero_analyst is it free or paid

[@NaveenKumar-fm5yg]

in postgres sql you using ilike so my doubt is i am using sql workbench so what i can use i think ilike is not supported in mysql ?

[@mulikinatisiddarthasiddu8245]

U can bro

[@RlUpadhyay-c6o]

koi batayga mere postgres me ye file import hi nhi ho rhi hai..

[@zero_analyst]

Please follow all step by step mentioned along with me make sure all settings are in place

[@yashgawand17]

i can't import the dataset even though the column names are correct. i get the error "columns" while i try to import the data set

[@babayaga4459]

Sameee😢

[@yashgawand17]

@@babayaga4459 restart your device and import the data without opening the query tab

[@zero_analyst]

Thank you

[@babayaga4459]

Huhhhhh 😭😭😭 can you pls share the discord link so that we can share our problem over there

[@yashgawand17]

@@babayaga4459 what worked for me is, I just restarted the pgadmin 4 app and didn't open query editor. Directly right clicked on the Netflix table and imported the file.

[@AmanSharma-tn3dy]

where is your github repo link?

[@zero_analyst]

Check the project for vid link under SQL projects in desc!

[@ankathimamatha8669]

When am importing file into MySQL am getting only 266rows only..can anyone tell..

[@shubham-t5z5n]

increase the limit rows maybe

[@samratdutta3366]

few of my code in not working reason i don't know what is wrong even if i look at the code it as it is

[@zero_analyst]

Please share the code in our discord community

[@mulikinatisiddarthasiddu8245]

is it a beginner level or advanced level project bayya?

[@zero_analyst]

Advanced

[@devyanirathod9799]

Can i not use mysql for the same project?

[@zero_analyst]

You can use same project in MySQL as well! Just few syntax you need to change as per my SQL

[@HarshKoli-r8y]

ilike not work in my sql

[@zero_analyst]

Yeah its for psql!

[@ndumisoradebe5256]

For events where he uses ilike, you can first change the column to lowercase or uppercase then afterward continue using like. For example, in the last problem when he said: description ILIKE '%kill%' You can say: LOWER(description) LIKE '%kill%' I just finished the project using MySQL

[@sidraomer5894]

@@ndumisoradebe5256 in mysql, the functions like string_to_array,unnest and split_part worked?

[@HarshKoli-r8y]

@@ndumisoradebe5256 thanks bro

[@HarshKoli-r8y]

sir your indian?

[@RlUpadhyay-c6o]

"BHAI TUMHARI QUERY EXECUTE HO GYI MERI NHI HUI TUM ERROR NHI BATATE HO DIRECT SOLUTION batate ho it is not right thing". that's why i am not following you and i will recommended dont follow his channel. - 6. Find content added in the last 5 years SELECT * FROM netflix WHERE TO_DATE(date_added, 'Month DD, YYYY') >= CURRENT_DATE - INTERVAL '5 years'; ERROR: The given value did not match any of the allowed values for this field.invalid value "25-Sep-21" for "Month" ERROR: invalid value "25-Sep-21" for "Month"

[@zero_analyst]

Thanks for sharing your feedback!

[@FABSBGMI]

bhai chasma mt phen tu bhot bura lg rha hai

[@muhammedibrahim5421]

Question no. 5 answer approach is wrong as the data type for duration is string so it just gave the longest no. of characters I have solved by the following approach: ---------------------------------------------------------------------------------------------------------------------------------------------- -- Longest Movies SELECT MAX(CAST(REPLACE(duration, ' min', '') AS INTEGER)) AS duration_in_minutes FROM movies_shows_data WHERE type = 'Movie'; ----------------------------------------------------------------------------------------------------------------------------------------------- We can get also the longest TV Shows: -- Longest TV Show with respect to no. of Seasons SELECT MAX(CAST(REPLACE(duration, ' Seasons', '')AS INTEGER)) FROM movies_shows_data WHERE type = 'TV Show' AND duration LIKE '%Seasons'; and lastly thank you a lot for this amazing project @zero_analyst