SQL Portfolio Project - Part 2 | Analytics | Ashutosh Kumar
HTML-код
- Опубликовано: 21 май 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 , i n this video i have analysis on indian census data for 2011 . I have gathered data from the websites mentioned below or you can find the two data sets used into this video below-
Websites-
www.census2011.co.in/district...
www.census2011.co.in/literacy...
Datasets -
docs.google.com/spreadsheets/...
github.com/ashutoshkr103/SQL-...
docs.google.com/spreadsheets/...
github.com/ashutoshkr103/SQL-...
Code -
github.com/ashutoshkr103/SQL-...
----------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here
👉 SQL Portfolio Project Indian Census- Part 1
• 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
👉 SQL Portfolio Project Indian Census- Part 1
ruclips.net/video/I3YvjFfn478/видео.html
I am not getting total population. select state,sum(population) from censusii;
It is showing :state sum(population)
Andhra Pradesh 13175
@@ajitkhomane7110 you need to use group by also
bro ...bring more videos like this ...highly appreciated. 👍👍👍
love to have more SQL projects like this.
Thanks a lot
Hii,
Ashutosh Kumar, I really appreciate your efforts to prepare such videos for us. Thanks a lot.
Thanks a lot
excellent!
Thanks, Ashutosh, as an aspiring Data Analyst it really helped me to get a lot of insight. Will surely work on your other project videos.
Thanks again🙏🙏🙏🙏🙏
Glad it was helpful!
Great JOB. Thank you for made this project video.
Thank for supporting
very much helpful for me as a SQL beginner . Thank u
Thanks a lot
Thank you so much Ashutosh. I didn't expect this much after watching first part but I learnt a lot .. Appreciated and highly recommended❤. Happy teaching
Thanks for your words
Hi @AshutoshKumaryt Anna, I feel very enthusiastic during I am watching this video. This is the first time I am watching a portfolio project video with statistical formulas, it was really a good experience watching your video. I am really appreciating your efforts Anna and very thank you so much!
Thx anna
Great Project Ashutosh .
Love to see more Project like This.
Thanks check the latest project video for more awesome stuff
Thank you sir✨✨✨
Thankyou Sir
Thank you sir...
You provided lot of queries...
Glad that you liked, thanks a lot
Thank you Ashutosh, this will be really helpful
Thanks a lot
great effort. Thank you!
Glad it was helpful!
THANK YOU SO MUCH GOT TO LEARN ALOT
Thanks
Thank you so much sir. It's very helpful to me.😊
thx
Great work!!
Thanks a lot
Bro keep it up
thank you ashutosh
thanx
Thank You.
Thanks a lot
Awesome
Thanks a lot
Amazing video, clear and articulate form of delivery, Calm and steady paced throughout the video. Keep up the good work!
Just one question, so to calculate females, if we were to Females = (Population - Males), do we have to create a temp table for that and how do we proceed in such situations wherein a calculation that we are about to derive needs to be used in the same calculation. Thank you.
You can use cte
Thanks a lot for this project
Thanks
but how would i show this project in my portfolio?
like how to do that
Thankyou!!!
Thanks
How does one add this to resume or linkdin ? How to provide link of this on linkdin?
bro ......Area column is not given in your data 2 excel sheet.
I think area / population is impractical because why would anyone want to know how much area on average would be taken by 1 person. I think it should be the other way around i.e. population by area, otherwise called population density. Here is the query:
select *, curr_pop_density-prev_pop_density pop_den_growth, round(((curr_pop_density-prev_pop_density)/prev_pop_density)*100,2) pop_den_growth_pct from (
select state, sum(Area_km2) state_area, sum(population) curr_pop, round(sum(population)/sum(area_km2),0) curr_pop_density, sum(prev_pop) prev_pop, round(sum(prev_pop)/sum(area_km2),0) prev_pop_density,(sum(population)-sum(prev_pop)) growth from(
select district, state, population, growth, Area_km2, round(population/(1+Growth),0) prev_pop from (
select a.district, a.state, a.Growth, b.Area_km2, b.population from Data1 a inner join Sheet1 b on a.district = b.district) c) d
group by state) e
order by pop_den_growth_pct desc
In this query I have added statewise population density for current as well as previous population. I have also compared the increase(or decrease) in population density and have also added growth(or decrease) percentage. If you want aggregate data for the country then you can add another sub query sum all the relevant columns.
Hope this was useful :)
correct , thanks!
How to add this project to resumes, like we have to write all the queries or link of that?
Can u explain the concept of adding the +1 to the sex ratio in the final calculation for finding out females and males? Didn't quite get it.
total no. of males & females query is not working in mysql...
Hi Ashutosh I was trying to work with your project but like sum(area_km2) result value is not matching with yours similar for some of the previous calculation I tried importing the data2 fresh but still no change. Why so?
might be wrong/incomplete data has been imported just count the number of rows imported and on the excel sheet to cross check
Hie Ashutosh ! Greate Work
I have a question
Just like we found literate count ,Can we find male literate ,female literate count?? if yes what will be the formula
please reply
I am Trying to make it on Tableau
group by gender
why do we need to add +1 in the following formula
males =population (sex _ratio+1 )in formula for the first mathematical function can you explain sir
Sir this has come from the derivation
ok sir thank you for reply
I am trying to import a excel file
but only around 19k rows are being imported into the database table out of 85k. Can you help me out on this??
Divide the entire dataset in 5 different Excel files with 18k rows in each,then import each data file one by one,use union formula(Google this) to join entire datasets in one in ssms
Sir I am trying to run same queries in the mysql server with same data which you provided in description but why i am not getting the same output as yours.......not getting any error but my output is different from yours one.....please tell me why it is?
I think the full data is not getting imported. Do a count(*) and see if total data is getting imported from the excel sheet
Does the partition by query doesn’t work in mysql ? Its giving me syntax error
Let me see
nice video and explanation.. just one thing at 12.38 you edited the video and i got confused about the code. could you please explain when you do things like this in next videos.. thanks
Yes sure extremely sorry for this
You should not b sorry. You did really great job. Just wanted to let you know as user.
Thanks alot again.
Sir, can we do join with sub query at same time.
Yeah
Can anbody share me second dataset
Ashutosh plz gave me access for 2 nd data set
done , recieved ?
how to get which state hving max population...
You can use Max function
I have an error in this query..Anyone plz help me finding an error
select d.district,d,state,d.literacy_ratio*d.population as literate_people, (1-d.literacy_ratio)*d.population as Illiterate_people from
(select a.district,a.state,a.literacy/100 literacy_ratio,b.population from dataset1 a inner join dataset2 b on a.district=b.district)d;
Can you please explain the 'KEY' part here? I lost you there completely.
sir why used #topstates?
12:52 code is not running i followed your all steps but its not working please help
Is showing when i am using sex ratio in upper line it is not geting it
I thin coad is only taking data1 not data2
Try reinserting data2 then once
@@AshutoshKumaryt where and how?
Only Inner join coad is working last line code if i am running both coad simultaneously it is giving error
@@prabhanshuneekhara3041 send a hi on instagram @ashutosh.analytics will see from there
How to share query results to clients.. In sql
upload on github
I didn't understand why ratio= females/males but not males/females?
That's the standard definition, no brainer
please tell the error in this code:
select a.District,a.State,a.Sex_Ratio,b.Population from sensex_analysis.dataset a left join sensex_analysis.dataset1 b on a.District=b.District
what's the error
from where to collect the datasets???
attached link in description box
Initially you talked about two table, how does the Area table came??
If it came why don't you provide it?
Timestamp?
while calculating total no. of male and females output aa rha ahi bt only in 1 or 2 digit
Koi ni concept practice is important
Yuss thanks sir
not able to calculate male and female population need help @Ashutosh Kumar
I am using PostgreSQL
I want the result of those states whose total population is more than 3% of the total population of india?
Select states,sum(population )from table group by state having sum(population)>(Select 0.03*sum(population) from table)
Why can't we join the state column?
u mean state name ?
@@AshutoshKumaryt yes, can u explain in detail
@@aryagfrj468 joining should be done on integer columns if not present in the table then we can use varchar column
@@AshutoshKumaryt but u join based on district why can't u use state instead of district
@@aryagfrj468 I strongly feel your joins concepts is not clear yet , please watch this you will get an idea why I did this ruclips.net/video/Bmj5ej62XjM/видео.html
with cte1 as(
Select a.State,round(avg(a.Sex_Ratio)/1000,3) as Sex_Ratio
from census..data1 a inner join census..data2 b
on a.District=b.District
group by a.State
),cte2 as(
Select b.State,round(avg(b.Population),0) as total_population
from census..data1 a inner join census..data2 b
on a.District=b.District
group by b.State
),cte3 as(
select a.State,a.Sex_Ratio ,b.total_population,
round(b.total_population/(a.Sex_Ratio+1),0) as male,
b.total_population-round(b.total_population/(a.Sex_Ratio+1),0) as female
FROM
cte1 a inner join cte2 b
on a.State=b.State
) select * from cte3
bro i am getting different output from yours, what is the problem in my solution
Hi Sir, Can you please confirm for Total Females Final Equation(5) as per my calculation - it should be as mentioned below:
--Sex Ratio = Total Females / Total Males ->(Equation 1)
--Total Population = Total Females + Total Males ->(Equation 2)
--Total Females = Total Population - Total Males ->(Equation 3)
--Putting Equation 3 in Equation 1
--Sex Ratio = (Total Population - Total Males)/Total Males
--(Sex Ratio)Total Males= Total Population - Total Males
--[(Sex Ratio)Total Males]+Total Males = Total Population
--Total Males(Sex Ratio + 1)=Total Population
--Total Males= Total Population / Sex Ratio+1 ->(Equation 4)
--Referring Equation 3 and 4 for calculating Total Females
--Total Females = Total Population - (Total Population / Sex Ratio+1)
--Total Females = Total Population(1-1/Sex Ratio+1)
--Total Females = Total Population * ( Sex Ratio /Sex Ratio + 1) ->(Final Equation 5)
but it will give different answer - if I use : Total Females =( Total Population * Sex Ratio) /Sex Ratio + 1
It's correct
Sex ratio is defined as female for 1000 male
@@AshutoshKumaryt Thankyou
select c.District,c.State,c.Literacy,c.Population,(c.Population*(c.Literacy/100)) as 'Literate population'
from
(select a.District,a.State,a.Literacy,b.Population from
dataset1 a inner join dataset2 b on a.District=b.District )c;
I am getting answer in two digit like 12 13 for Literate population. Can you please explain
Just check if the entire dataset you have by count(*)
with result as
(
select a.district, a.state, a.growth, b.population
from data1 a
left join data2 b
on a.District = b.District
), result2 as
(
select * , ((growth*100)+100) as calcul from result
), result3 as
(
select *, result2.calcul as x , round(((population*100)/result2.calcul), 0) as previous_census
from result2
)
select sum(previous_census) as totppp, SUM(population) as current_P
from result3