Tableau Data Analysis Project: Sales Insights : 3 - Data Analysis Using SQL

last video we talked about aims grid and data discovery in this video we will look at our mysql database which is owned by falcon steam and the data master team will be doing analysis on that so now both the teams are having a discussion and falcons have given access to mysql database to data masters and data masters are now accessing that database and they will run some sql queries if you want to build a career as a data analyst having sql knowledge is must so in this video you don't need any prerequisite for sql knowledge you can just start from you know very basic and we will look at different tables the relationship will run simple query select where inner join etc and we'll do simple data analysis so i will show you that even if you don't use tools such as tableau or power bi data analyst often they do a simple analysis using sql itself okay so sql itself is a data analysis tool and we'll look into that analysis and later in the in the next video we'll see how using tableau can provide some extra benefits or simple sql queries so let's get started and i'm going to now link a video that i did for the other sales inside power bi series the sql analysis part is the same so i'm just going to attach that particular video here and if i ever mention power bi in that video just assume that it's actually tableau okay so we'll we'll get started and i have a complete database down so we'll download the database dump run sql queries and it's gonna be a lot of fun so let's begin all right the first thing is let's install mysql on your local computer i'm going to provide a link of this great video which goes through the installation instructions so just follow the video it is linked in the video description below and install mysql so when you install mysql you are installing mysql server as well as mysql workbench so mysql workbench will allow you to see the tables and run the sql queries after you have installed it click on mysql wash workbench and launch it so when you launch it it looks something like this now you can create a new connection and i will call it dev let's say sales insights okay keep other values default as it is my username password is root root and test connection and it looks good i just press ok so it creates this new connection and now you click here so now you are going into that connection if you want to check the server status you can click on administration and check server status here it says server status running if you are facing issues in running sql servers just google it figure it out i don't want to make this like a full-fledged sql tutorials there are so many tutorials out there so just figure it out and i'm assuming that now you are at a stage where your sql server is running and you have in the schemas there is a default schema called sys now i have given the entire dump of our sales insights database on my github page so follow uh i have the link in the video description below so use that link and download this file called db underscore dump dot sql so i downloaded that file here db underscore dump dot sql okay many times people don't know how to clone things from github you have to just go to root repository and click on the green clone button and that way you can download the file so assuming you have downloaded this file now what i'm going to do from workbench is click on tools or rather server and say data import in the data import click this option and here go to the location so my location of the dump file is this so select that and what this will do is this will create the entire database along with the records in your system okay so this is a self-contained sql file i created database and exported the entire database to sql file for you so now all you have to do is just go here and import it then you can click on this button start import and it is importing so here it says import is running looks like it finished without an error so now you can click on this refresh icon and you will see this sales database created you'll see a couple of tables here now let's look at some records from customers table so you can right click and select this and you will see this recourse it might take some time to get used to this user interface but don't worry just play with it this is actually a very simple interface if you want to export this data to excel file you can click here on excel import you know so it you can export the whole customer table as a csv file then you can do that using this option also you can run our different queries here so here when i uh what it is doing is it is printing all the records from customer table now if you don't know much about sql and relational database i will suggest that you do this course on khan academy it is for free and that sql course will teach you the fundamentals of relational database what are tables columns foreign primary key and so on in this table i have three columns customer code name and customer type these are all the clients of athletic hardware okay let's look at transactions table because that's the most important one transaction table contains all my transactions so you can see columns such as product code customer code market code order date sales quantity sales amount and currency now let's look at products and markets table really quickly so here you can right click and click on products table so these are you know like there are no fancy products name so we have just rename name this product one two three etc and if you look at the markets table the markets table has the market code the name of the city so at least hardware is doing business in india so market's name would be one of the cities in india the zone would be either south central or north now you can see some records here new york and paris and there is no zone so looks like this company did some business with new york and paris maybe one time business and that's why the software engineers might have added these records here but these records are not useful right now because right now the company is doing business only in india when you're dealing with relational databases you will find so many garbage you know because these databases have been in use for so many years so there will be so much garbage and that's where the main challenge comes in you have to do data cleaning as a data analyst and deal with cases like this okay you might have noticed in transactions table i had this which is sales amount is negative now that cannot be negative so i had some uh messy data here as well also few transactions are in usd if you want to do let's say some analysis you have to uh convert this usd into inr otherwise if you are trying to find out the total sum of revenue it becomes really difficult you cannot add this 500 with this one so you will be facing a lot of these challenges so let's do some primary analysis uh of our database in transactions we see some records here i want to see how many total records are there for that you can use this query select count star from sales transaction okay and when you click here it will give you the total count you can see there is 150 000 records so this is not some dummy database it's some serious stuff going on 150 000 sales transaction similarly if you want to look at number of records in customer table you can say select count star from sales sales is the name of the schema by the way it's here and transaction is the name of the table so similarly if i do customers you know there are 38 customers okay so this looks good so far now i want to show the transactions only from chennai so if you look at chennai the market code is mark 001 okay so let's first again print some transaction record so you can say select star from sales transactions to limit five when you do limit five it will only print the first five records okay and there is a market code here so if you want to print only market code zero zero one there is a where clause so you can say where market code basically give me all the records where market code is mark 0 0 1 and when you execute this you know you get all these records again if you want to do count you can you can see how many transactions were performed in chennai so we are already doing now data analysis we are generating insights from our data by using sql that's why sql is one of the tools that you need to know as a data analyst if your business manager asks you how many transactions we did in chennai in total you can go to sql workbench run this query and get the answer immediately if he wants to know all the transactions you know he'll be like okay give me the dump of all the transactions in chennai then you can run this query click on export here and export the result to csv and give it to your business manager so you realize how sql can help you with your data analysis now i saw this usd currency i want to know how many transactions have usd currency so it is simple again it's a very close you can say currency equal to usd and you will find there are luckily only two transactions somehow two transactions have usd so when we'll build our power bi dashboard we will convert this usd value into inr okay the next thing is i want to show transactions in 2020 joined by date table so what does that mean well when i do this okay see i'm printing sales transaction here okay fine i get these records but i want to know how many transactions were performed in 2020 let's say i'm interested in particular year one thing you can do is uh get the year out of this column or if you notice we have this date table so this date table is very important actually so here so date table has a date and it will tell you what is the given year for that particular date so we can do now and inner join with this table and we can figure it out there is a outer join as well there is inner join and then there is a left and you know right join so you can read about all those joins online it's not a rocket science you can refer to some materials and you can get an understanding easily so let's perform our join now so what you will do is you will say select sales dot transactions dot star dot star means uh print all the columns then you want to print all the columns from date table as well right so just printing columns from both the tables now you want to join those two tables together so how do you join it so you will say from sales dot transactions you know from sales to transactions inner join sales dot date on say okay inner join says dot date dot date because sales is the name of the schema date is the name of the table and date is the name of the column here date on sales dot transactions dot order date is equal to sales dot date dot date oh so in you are doing inner join with sales.datetable so that was correct so now you print uh more columns so these two tables are joined using the date columns so now when i'm looking at this transaction you know the good thing is i also know the year here see and what i can do now is i can print all the transaction in 2020 by saying sales dot date here equal to 2020 so when i do this now it will show me only the transaction from year 2020. you see there is only 20 here if you 2019 it will show you 2019 transactions see 2019 and in 2020 now i want to let's say i want to know the total revenue in year 2020 or total sales so the way i can do that is uh i can just print so i can say sum sales dot transactions dot what is the amount well sales amount right sales amount okay now i know we have usd thing going on but that is not in year 2020 so you don't have to worry about that currency but if there was a usd currency then you have to chain the query a little bit but when you execute this now i got this number that there was 152 million rupees revenue in 2020. similarly if you do 2019 it will tell me 2019 had this much revenue so you see the revenues are i think declining because previous year it was 336 million now it is i think in 2020 the revenue is 142 million so this way you can get uh the aggregation data the aggregation insights you know you can do some you can do average anytime you are facing issues with sql just just google it google is your friend in sql you can say sql average column and you know there is so much help available in google see they will show you simple table and give you all the syntax so do not worry about this sql syntax that much it is actually very very simple all right so now we have some what we want is now we want the sum in chennai so i want to know how much business i did in chennai okay so what we can do is now we can join uh we can join use we can do market code basically okay so we can say where sales.date is equal to this okay and so in the where clause you can put end and sales dot transactions dot market code is equal to you know for chennai the market code is zero zero one so if you look at market code here so it's in a market zero zero one that's why okay so where is my thing and this is now showing me the revenue in chennai which is one two three one two three two point four million uh you can also show the distinct products for example if you want to know the distinct products that you sold in chennai then you can print this queries select distinct product from that and you know this will show you the uh distinct products list of products that you sold in chennai so far so that's all i have for this video you can run more queries and perform more in-depth data analysis using sql i have given the dump file on my github page so if you look at the video description below there is a readme file link if you go to that link i will have instructions on how you can download this sql dump so that you can initialize your database also i have given all the queries that i ran in this video on that page so try those queries out and try to perform some analysis and try to generate some insights just using sql in the next video we will use power bi and power bi will be connected to the same sql database and we will do some data cleaning because you saw some usd columns there were some negative values in sales and all of that so in power bi we will initialize the model in the next video and we'll do data cleaning i hope you're liking this series so far if you are then please give it a thumbs up

Channel: codebasics

Views: 34,486

Rating: 4.9465084 out of 5

Keywords: yt:cc=on, data analysis project using tableau, data analyst project, tableau project, data analysis projects for beginners, data analyst project example, data analytics project examples, tableau project tutorial, tableau tutorial, tableau project for practice, tableau project for beginners, tableau live project, tableau project ideas, tableau projects, tableau data science project, tableau visualization projects, tableau real life project, tableau data visualization

Id: fgMD2wvpvpk

Length: 20min 46sec (1246 seconds)

Published: Thu Dec 17 2020