SQL Portfolio Project - Part 1 - Zomato | Analytics | Ashutosh Kumar
HTML-код
- Опубликовано: 10 ноя 2022
- The best technique of learning a skill is completing a project which is based on real life scenario and when it comes to analytics , SQL is the one of the most asked skillset in all the interviews, so friends in this video i have created an end to end project on data analysis using SQL, thi will be relevant to all those people who want to make a career into data analytics, business analytics ,data science , in this video i have analysis on zomato dataset . You can find the code attached below -
drop table if exists goldusers_signup;
CREATE TABLE goldusers_signup(userid integer,gold_signup_date date);
INSERT INTO goldusers_signup(userid,gold_signup_date)
VALUES (1,'09-22-2017'),
(3,'04-21-2017');
drop table if exists users;
CREATE TABLE users(userid integer,signup_date date);
INSERT INTO users(userid,signup_date)
VALUES (1,'09-02-2014'),
(2,'01-15-2015'),
(3,'04-11-2014');
drop table if exists sales;
CREATE TABLE sales(userid integer,created_date date,product_id integer);
INSERT INTO sales(userid,created_date,product_id)
VALUES (1,'04-19-2017',2),
(3,'12-18-2019',1),
(2,'07-20-2020',3),
(1,'10-23-2019',2),
(1,'03-19-2018',3),
(3,'12-20-2016',2),
(1,'11-09-2016',1),
(1,'05-20-2016',3),
(2,'09-24-2017',1),
(1,'03-11-2017',2),
(1,'03-11-2016',1),
(3,'11-10-2016',1),
(3,'12-07-2017',2),
(3,'12-15-2016',2),
(2,'11-08-2017',2),
(2,'09-10-2018',3);
drop table if exists product;
CREATE TABLE product(product_id integer,product_name text,price integer);
INSERT INTO product(product_id,product_name,price)
VALUES
(1,'p1',980),
(2,'p2',870),
(3,'p3',330);
select * from sales;
select * from product;
select * from goldusers_signup;
select * from users;
----------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here
👉 SQL Portfolio Project Indian Census- Part 2
• SQL Portfolio Project ...
👉 SQL Portfolio Project - Shark Tank India
• SQL Portfolio Project ...
👉 How to Learn SQL
• How to learn sql for b...
👉 Complete playlist on Sql Interview questions and answers
• HackerRank SQL problem...
👉 Top free and best resources to learn analytics -
• Free and Best resource...
👉 How to become a data analyst complete roadmap-
• Data Analyst Complete ...
👉 Data analytics internships and job stipend-
• 70,000 + per month dat...
👉 Top 3 you tube channels to learn sql for free for beginners
• Video
👉 How to get data analytics internships-
• How to apply for data ...
👉Check out the roadmap to become a business analyst - • Business Analyst Compl...
👉All about analytics playlist-
• All about analytics
👉 Top 3 you tube channels to learn excel for free for beginners
• Top 3 you tube channel...
____________________________________________________________________
Fill the form below to subscribe yourself to the analytics jobs mailing list to receive regular job opening updates - docs.google.com/forms/d/e/1FA...
Why you should definitely fill the analytics job updates google form - • Job Openings into busi...
_______________________________________________________________________
Connect with me
📸Instagram - / ashutoszh
💻Linkedin- / ashutosh.analytics
_____________________________________________________________________
Comment down if you have any doubts
Please leave a LIKE 👍 and SUBSCRIBE ❤️ to my channel to receive more amazing content in data analytics and data science.
_____________________________________________________________________
🏷️ Tags
sql,
sql for analytics,
sql tutorials for beginners,
portfolio project sql,
sql portfolio project,
portfolio project on sql for resume,
sql interview portfolio project,
sql interview questions and answers,
sql for data analytics,
sql data science,
interview questions on sql,
sql hard questions,
sql problem solving,
ashutosh,
ashutosh kumar,
ashutosh kumar analytics
🏷️ HashTags
#sql #portfolio #project
****About Dataset*****. Like me, some people are getting confuse about the Dataset. If possible pin this message also that *** We have to create dataset based on the codes you have provided in MySQL.*** Thank you, it is very helpful, as we are doing things from scratch.
Thanks a lot, pinning it
@@AshutoshKumaryt bhai PDF file dete iske na ..mai Business analyst fresher apply kar raha tha mere liye project add kar leta
bro how to make dummy dataset??
Bro we have to use foreign key if we manually make the database
where i can get that code i mean in which video
There are a lot of videos explaining sql but the real time examples provided by you made it very clear and easy to understand. Thank you very much sir. Looking forward to see a lot of these.
Thanks a lot
SQL Portfolio Project - Part 1 - Zomato - ruclips.net/video/fRBeYNJ3S8w/видео.html
Advanced SQL playlist ruclips.net/p/PLbTF1OfX62c3RQ_ZFfyNBWVPdz_OWTMLG
thanks so much, nice work 💚
Thankyu bhai bas aise hi projects bana te raho bohot bohot thankyu
thanks a lot
very valuable stuff to understand in simple way thank you !
Thanks
This channel deserves more subscribers
Thanks for your support
your all portfolio projects helped me a lot for understanding different topics and getting clear knowledge of the modules based on different topics ...thank you for such efforts and hope you will continuously upload videos on different portfolio projects...
thx
tere baap ka naukar nhi hai woh
Doing greater work
Thanks a lot
in Q4, if we want to display most purchased product also at the end of the result, how we will do that?
Nice video
Thanks
Thank you sir. Pls launch next video for part-2 very soon.
Will come next week
Thankyou so much for this video ❤
Thanks
the date format in dataset is ddmmyyyy that doesn't work by default in MySQL. it truncated the whole column in 0000-00-00. how to change the date format
I saw the videos of Zomato Project and i grasped all the things so kindly let me know what to describe over resume?
getting error in running your sq codes to create table. this happened with ola project as well
Very helpful and clearly explained. Now it's time to work on Part 2. You earned a new subscriber :) thank you!!
Thanks a lot
Well done
Thanks
HEY BRO, WE SHOULD DIRECTLY USE AGGREGATE FUNCTION IN ORDER BY CLUASE , RIGHT , , WHERE AS U USED IT
Very useful..Thank you!
Thanks
Thank you brother for making this video
Thanks a lot
@@AshutoshKumaryt is this project how much parts are there???
@@akkys97 one more part will come next week
@@AshutoshKumaryt amazing brother thanks for your guidance
Bro limit doesn't work in ssms because limit is my sql command not sql server command, and on the other hand top is sql server command.
how will i put this project in my Resume as experianced candiate ??
Sir can we do this project and run codes in sql and Update in resume?.
Buy taking those Questions/scenarios?.
By taking this same Zomoto dataset?.
Can you make video on how non tech background person manage the data analysis experience in other organisations just in brief or shorts
Can pls share the date set link please sir
Hey I have been following your channel for a while and currently doing this video.I have been struggling to search for projects related to SQL which I can add to my resume.I am afresher.I have been doing data analytics sql virtual internships from Forage and doing yours.Will adding these to my resume give it a boost?I currently have a Masters in Economics with specialization in Econometrics and trying to break into the Data Analytics field.
You can use this for practice purpose
Please make more such videos on regular intervals if possible.Thank you.
Thanks a lot
Thanks a lot
can i include any of the project in the resume like I worked on this project in my previous company as a SQL developer ? please let me know
Thanks 🙃
Thanks a lot
But how to add these on project on resume..i mean what to write and how to write
select * from (select userid,product_id, count(product_id),rank() over(partition by userid order by count(product_id) desc) rnk from sales group by userid,product_id)x where rnk = 1
why am i getting an error for this ?
Brother you make very nice videos can you just tell how you make this video
Can you suggest equipment such as
Camera
Mic
Editing tools etc like this...
Thank you so much Ashutosh,
Am a beginner, have a doubt at, 17.19 in the video,
Why have you updated 'a' while completing the statement
that was an alias given to the derived table,
Thanks
plz make a project using oracle sql devloper.I m facing problem to import data through sql devloper
getting error 1292 incorrect date value: do I have to change the date format manually? or how to use str_to_date() in this case?
U change in 'yyyy-mm-dd' format.
Can i add this projects in my projects in resume?
sir can you please tell me how to cpy data and run into the sql i'm trying to do it but not able to do it
hey you can copy data from the description box of the video and paste on your sql server , run it and see result ,
Thank you so much for sharing this bro... god bless uh
pls help in how to show this project in Resume
Will be making a video soon
HI Ashotosh ji, i am facing error of "subquery must return only one column"
i am using postgresql where top clause not working , so i have used "fetch first 1 rows"
code:
select userid, count(productid) cnt from sales where productid=
(select productid,count(productid) cnt from sales
group by productid
order by count(productid) desc
fetch first 1 rows only)
group by userid;
kindly guide me
SELECT userid, COUNT(product_id) as cnt_id
FROM sales
WHERE product_id = (SELECT MAX(product_id)
FROM sales
GROUP BY product_id
ORDER BY COUNT(product_id) DESC
LIMIT 1)
GROUP BY userid;
Sir for this particular code mentioned below
select *,rank() over(partition by userid order by cnt desc) from
(select userid,product_id,count(product_id) cnt from sales group by userid,product_id)
I'm getting an error
Msg 102, Level 15, State 1, Line 76
Incorrect syntax near ')'.
Can you please help me getting this resolved
Every derived table has its own alias.
Give any name to the derived table.
Try this one:
select *,rank() over(partition by userid order by cnt desc) from
(select userid,product_id,count(product_id) cnt from sales group by userid,product_id) table1;
Awesome
@@jasleenkaur-cw2fw thank you bro
Can u tell me bro? What is the role and responsibilities of ur project.
bhai whenever i m tring to insert the values its showing incorrect data , pleases help
Strange should not happen,what is the error exactly
SIR WHILE INSERTING ANY VALUES IT IS THROWING ERROR
Question no 2. How many days has each customer visited zomato?
SELECT userid, COUNT(*) AS visited FROM sales GROUP BY userid;
This works fine for me? Is using DISTINCT important?
yes, as the question was hw many days not times
hi ,
date data type can only store values as 'yyyy-mm-dd' but in your data set you have stored it in "mm-dd-yyyy" how it is not throwing error but in my case it is throwing error!!!
i am using postgres !!
please help
If error is thrown change the date in the right format and start solving questions, focus on problem solving
Can you please share the dataset link again . Couldn’t find it in the description box
Copy code from description box
At 17:51 what is this "a" that you are using.
Thanks derived table name which is compulsory in SQL server
I think in date format 'yyyy-mm-dd' in MySQL
@AshutoshKumaryt, where is the data please ? Good video
hi ,i'm just new to this
can u please make a short video on importing csv file to ms sql server
Watch this ruclips.net/video/epYmIRdg_qo/видео.html
sir, how can we add this to our portfolio , the dataset is not proper right , can u provide better dataset
See this is for practice purpose , i have tried to focus on solving best problems so in real scenario just the dataset will increase but if you know to solve problems the dataset size won't be a problem
without distinct function in date, second question is giving same result
i am a begginer in sql for data analysis and need to male the first project for me..do which vedios u recommed me ?
indian census
sir i have been facing date problem its saying that incorrect syntax of date
Change the date format
In every insert into table, date formate is wrong . The correct format is 'yyyy-mm-dd'
Hii sir, i learned mysql and practiced in my
sql workbench can i do practice above code on my sql or need to install, do i get same output or any difference i will get thank you.
It will work
For question no.3
can we write Alternative query as ----
select * from sales where created_date=ANY(select min(created_date) from sales group by userid);
will that work universally?? please help and clear me.
@AshutoshKumaryt
pls sir can you share the data set link
Read the pinned comment
we have to change your code's date format to year-month-date in order to access it in Mysql workbench.
Thanks can you paste the code here in the comment section so that it can help everyone using workbench
hey!!!
ashutosh hows going this video is really ASM man!!
Thank you for the video and priceless time for us .
ashutosh i want dataset of #zomato so that i can do practice.
Please Provide me!!
Thanks a lot, Copy code from description box
Sir kaggle s download krke MySQL pr kese load kare pls make video
Agar data set hum koi resource se download krte hai to kya SQL me insert krne ke baad data cleaning ni krte ?
Sir plzz reply my query
Karte h
@@AshutoshKumaryt sir to wo kaise krte hai
I have one doubt, which is if the interviewer ask , where did you get the Data from , so what should be the good answer ??
You created the data from scratch by navigating through the application that's it.
Hi Pl help me to understand how to import data from excel to sql, As you've uploaded the dataset from excel to sql
I have discussed this here
ruclips.net/video/epYmIRdg_qo/видео.html
@@AshutoshKumaryt can't we do the same in sql workbench?
Btw thanks for creating an amazing tutorial, Found one of the best channel for sql portfolio project...
@@radhikagarg9913 yes we can there might be a different process
@@radhikagarg9913 thanks a lot
Where I can find the dataset of this ?
Check description box of the video for the code of the dataset
where we can get the same excel file?
no need of excel file the code in the description box the video
I didn't get any practice file in discription please help
There's no file there is the code in the description box simply cop and paste
Okay thank you so much 😊 for help and this amazing video ❤️
Please sir share also dataset link in the description box so that I can access it and practice on it.
Check the pinned comment
I want the product name and price as well in last question i trien many things but not working please help
select * from
(select *,rank() over(partition by userid order by cnt desc) as rnk from
(select s.userid, s.product_id, count(s.product_id)cnt from sales as s left join product as p on (s.product_id = p.product_id) group by s.userid, s.product_id)as a) b where rnk=1;
dont give () after on
In industry, do we also do this kind of work?
No not like this, but you will be given data to gather so you need excellent SQL skills, so tis video will help you to get an idea how to proceed solving any problem
@@AshutoshKumaryt thanks subscribed for it
@@sunnygawande5283 means a lot
@@AshutoshKumaryt this project i mention in cv sir
plzz tell me
Can any one tell me where is the dataset
In the description box of the video
I can't find the link
Hey! can you please provide the dataset link or file
Check pinned comment
I did not understand the 4th problem since top funtion is not working in my sql
Please tell me how can I execute this
Limit function works
Ashutosh bhai please share the dataset. Without that you can understand the issues.... Thx in adv.
Please check the pinned comment
Hello, I am a SQL developer.. Bt now want to be a Data Analyst.. As i am new for Data Analyst I have a doubt that business problems are already provided by clients or the analyst need to analysed data and search for the business problems accordingly
Ya I have the same question 😅
Yes business problems is problem is provided by client and you have to Analyse only ....you can use any tool.
thx for the explanation
@@lakshyajain7435 okay.. Thanks for the explanation.. its mean to me a lot.
Hi bro
Upload the answers for which you have not answered in the video, so that we can cross check our answers.
where can i get the datasets?
Check pinned comment
can you please provide the dataset
Theres no dataset , for the same dataset pls find the code attached
Where is the dataset link
Copy code from description box
I can’t seem to find the link to the data where’s it
Please see once the pinned comment
waiting for part 2
released
starts from 4:30
CAN SOMEONE EXPLAIN HOW TO SEE THE SQL FILE IN EXCEL
No need to see the SQL file in excel i just demonstrated the dataset in excel just for the sake of explaining you can just copy paste the code and start
How can I get this dataset?
Check pinned comment
Hi Sir,
How to write this project in resume?
Have you written this project in resume? If yes, then please give me an overview, how to write?🙏
Will be making a video soon
@@AshutoshKumaryt thank you, it will help to all🙏
why you have wrote drop table if exists in every steps?
To delete the temp table if it is present from before and then recreate
@@AshutoshKumaryt can u please share that excel(dataset) link
I am not able to find these data sets
@@riddhidahanukar1613 check description box
hey where is the excel data sheet
Read the pinned comment
what is this project name please!
Data Exploration & Insights - Zomato
sir this project i mention in cv sir
Will make a video soon
Sir, where are the datasets?
Please find the attached code in the description box
Excel file export kaise karna hai
Excel file ki jarurat nahi h code h description box mein copy kijie
@@AshutoshKumarytbhai ek question pooch pooch ka sabne pareshan kar diya....
Hi ashutosh please provide Zomato dataset as soon as possible
Check out pinned comment once
Plz provide the link of 2part
ruclips.net/video/fRBeYNJ3S8w/видео.html
Where is the data set?
no dataset Sandhya , pleas find code in description box , copy paste and start solving
Bro, I am a beginner. Could you please tell me how to import data to SQL
No need to import bro just see the pinned comment of this video you will get idea,let me know if you still have any questions
3rd question's answer is wrong.
The question asked for product name not id
Bro can uh share the data set ?
Check the pinned comment