SQL Portfolio Project - Part 2 - Zomato | Analytics | Ashutosh Kumar

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

[@AshutoshKumaryt]

SQL Portfolio Project - Part 1 - Zomato - ruclips.net/video/BlN4U7GF704/видео.html

[@arnabmaity751]

please provide the dataset along your video

[@AshutoshKumaryt]

@@arnabmaity751 check pinned comment or description box for the dataset

[@Rachel-ds5bm]

Thankyou so much for this. Learnt a lot from you. Keep doing more such videos

[@reelsbeelz]

Thank u sir.. I have learned alot from this project.. Thanks alott.. 🤗

[@udayan5]

its a wonderful session that i had.

[@muskanverma9038]

Thanks ashutosh, you are doing great work, wishing you success at this .

[@AshutoshKumaryt]

thnx Muskan

[@lilyfullery4779]

thanks need more video like this with big data and more complex questions

[@AshutoshKumaryt]

soon

[@sambitpati6129]

GReaT job DUDE !!! heart full of thanks ❤❤❤❤❤

[@AshutoshKumaryt]

Thanks a lot

[@tempestking4624]

Thanks a lot this really helped as I made a last minute project it revised my concepts plus I was able add it in. my portfolio, really awesome please get us more videos like this

[@AshutoshKumaryt]

Thanks

[@Livefree103]

Amazing project. got to learn a lot. Thankyou sir

[@AshutoshKumaryt]

Thx

[@ceilingfan2031]

So many people can't find the dataset because they fail to listen... I'm surprised you have the patience to deal with all these comments lol. These videos were a great way to showcase understanding of important SQL concepts. Really well explained video! Keep up the good work

[@AshutoshKumaryt]

thxx

[@kiranpkulkarni6877]

I learnt few things from this project. Thanks

[@AshutoshKumaryt]

Great to hear!

[@tharunprabu1230]

Nice, keep up the good work.

[@AshutoshKumaryt]

thanks

[@user-ce3ry1jp3v]

Thanks a ton, Ashutosh

[@AshutoshKumaryt]

thanks for recognizing efforts

[@suharshac4021]

very helpful for beginners for understanding concepts and projects.waiting for new projects

[@AshutoshKumaryt]

Thanks a lot

[@user-rn8ji1pn3n]

Hi Ashutosh, thank you for your great efforts. One suggestion I would like to share is please share the queries through ctes instead of sub-queries as ctes are more easy to understand and more readible

[@AshutoshKumaryt]

hey that's correct , its fast also , there are many ways to solve a problem

[@harshshah9006]

Thank u so much !!

[@harshshah9006]

Bhaiya plz make a video on how to add files to github (sql) plz

[@Motivational_Greek]

Thank you...

[@AshutoshKumaryt]

thanks

[@vaidiksirsate6776]

thank you so much

[@AshutoshKumaryt]

You're welcome!

[@VinayYadav-gu4xb]

Thank u sir

[@AshutoshKumaryt]

Welcome

[@sumitrathod6962]

Hi Ashutosh, I think in the question 10th, you've not considered the total no. of orders purchased by those customers in one year of span after becoming gold members.

[@nishantnandkeolyar13289]

Thank you so much 👌

[@AshutoshKumaryt]

thanks

[@jameammarijr.2248]

Thanks!

[@AshutoshKumaryt]

Welcome!

[@user-ec6ez5kx1b]

Thanks a lot for this video, I Just want to know if I can add this to my resume? as a fresher looking for jobs

[@saurabhpathak7463]

in question 12, if we order created_date in ascending order, i am getting first zeroes then 6, 7 at last for userid: 1 how to make them 1 and 2?

[@thatgoanguy4427]

How to create a dashboard of so many questions? Is there a better way?

[@SBS-2030]

Thanks to share concept of rank ()

[@AshutoshKumaryt]

Glad you liked

[@ayushsahu632]

how to add this project in our portfolio?

[@pragyatigga5340]

I used workbench to create database and query it.
For 10th scenario created_date

[@latestlaunchesandreviews5489]

In Postgre SQL , I had to use , created_date BETWEEN gold_signup_date AND gold_signup_date + INTERVAL '1 year'

[@SaiKumar-ss8id]

in question 7, question is about the latest product before gold member so all the three customer will be present so is it correct to use inner join? may be using left join can be correct noe? plz reply and correct me if im wrong.

[@adityatomar9820]

Sir please make a video on how to add this project on github !!!

[@shwetamishra1497]

Content is really good. Please improve video quality it's quite difficult to see the query. Otherwise sql queries are really awesome. Thanks for the video

[@DaughterOfGodJG]

in ques 6, a is for sales, b : goldusers-signup, c & d ??

[@dhruveshbandivadekar9986]

IN THE 10TH QUESTION,YOU ALSO HAVE TO USE GROUP BY CLAUSE AS THEIR CAN BE MORE THAN ONE ORDERS,SO U NEED TO FIND THE TOTAL PRICE OF ALL THE ORDERS TO CALCULATE THE POINTS

[@starbonegaming9980]

sir i have one query in 10 question we have apply datediff library to replace a dateadd library

[@parvezshaikh9712]

Thankyu bhai plzz aur projects lao aise

[@AshutoshKumaryt]

Thanks a lot

[@rishabhmehta2294]

dateadd function not giving the output as it shows column 'year' dose not exist.

[@sachinok113]

how to do this project in mysql?

[@jayasriammu4112]

hi sir i have one doubt 9 qus y u div by amount and points pls explain it

[@nitishpandey7741]

Hi Sir, Q8 is showing error as ambigous. column reference Price is ambigous.used exactly ur code

[@user-bx3uk4wo2o]

Couldn't able to understand q-9 how Zomato point distribution on p2

[@narendrakumarchauhan8928]

Website aapne hta diya jis pr ye sabhi code lokhe the

[@yash2k02]

hello sir,
I am unable to export these queries as tables into excel files, I wanted to create visualization on these data using power bi but I am not able to export the data as im facing "could not find object 'Query' error. Any response will be appreciated.
Thanks

[@sunnygawande5283]

Do we do same thing on job for data analytics??

[@AshutoshKumaryt]

Not exactly but kind of, but good for learning purpose

[@shrutibajaj7249]

Hello Sir, In Question No 7: Why we are taking created_date

[@AshutoshKumaryt]

Yes you are right

[@vijinrajraj5746]

Bro, Where can I find these data set and how to import, I am a beginner

[@AshutoshKumaryt]

Bro check the description box of the video for the code and just copy paste the code in ssms

[@samosagaming9476]

i have done it in sqllite and I got it without cast()

[@aryakaagnihotri3210]

same in mysql

[@unpredictable_307]

Sir use both Hindi+ English language

[@kavinchellamuthu2601]

Sir can I use this in resume..

[@AshutoshKumaryt]

Yes

[@khushboojain3883]

Hi Ashutosh Sir,
While solving the first problem statement @08:41 , I am getting wrong output even after I am using your code. My code and your code are giving same answers.
The output:
1|10-23-2019|2|09-22-2017|1
3|11-10-2016|1|04-21-2017|1
I used online SQL compiler to run it. Please help me.

[@muskanjain6530]

Yes i am also getting same

[@muskanjain6530]

What is the problem we are doing
Have u come to know about the same ?

[@abhinavreddy4327]

i am also getting the same output, may be it is getting sorted by date instead of year

[@vyshnavveeravalli2305]

@@muskanjain6530 try for "yyyy-mm-dd"

[@mukeemshaikh6380]

Give the data and er diagram for project so pls send

[@AshutoshKumaryt]

Data is provided in the description box , just cop the code and proceed

[@phelelanimkhize4522]

WHAT IS THE POINT OF FOLLOWING ALONG THIS ENTIRE EXERSIZE IF THERE IS NO DATASET?

[@AshutoshKumaryt]

Please find the code in the description box

[@sabbatical786]

@@AshutoshKumaryt code not working

[@aish000]

​@@sabbatical786it is working.

[@farazahmad4002]

bhai this time again its showing wrong date format in SQL work bench

[@davidrajajames6898]

exactly MySQL workbench following different date formats and mssql sever following other time format

[@miniverma2768]

Please improve your video quality.

[@AshutoshKumaryt]

Thanks a lot , please let me know what exactly, it will help me to improve

[@panther_.gaming]

select c.userid,sum(zomato_points) from
(select b.userid,a.*,
( CASE product_name when 'p1' then price/5*2 when 'p2' then price/10*5 else price/5*1 end) as Zomato_Points
from product a
inner join sales b
on a.product_id=b.product_id) c
group by userid
order by userid
Can anyone say can i find answer of 9 with this code??....coz what i get and what shown in this video is different answers and if you manually calculate you will get this answer