title
Tableau Project For Beginners | Sales Insights : 3 - Data Analysis Using SQL

description
In previous video, we looked at data discovery for our sales insights data analysis project. In this video, we will look at mysql database that is owned by falcons team. This database has all sales transactions, customers, products and markets information. We will analyse this database and than hook it up with power BI. In power BI we will perform ETL and data cleaning operations to make it ready so that we can build our dashboard. Download resource files: https://codebasics.io/resources/end-to-end-sales-insights-project-using-tableau Page with SQL queries and setup instructions: https://github.com/codebasics/DataAnalysisProjects/blob/master/2_SalesInsightsTableau/README.md Install mysql: https://www.youtube.com/watch?v=WuBcTJnIuzo Khan academy SQL course: https://www.khanacademy.org/computing/computer-programming/sql#sql-basics Tableau Project playlist in Hindi https://www.youtube.com/playlist?list=PLPbgcxheSpE2B7YFfOualUTlRhgnQR7Kn Tableau Project playlist in English https://www.youtube.com/playlist?list=PLeo1K3hjS3usDI9XeUgjNZs6VnE0meBrL This video in Hindi: https://www.youtube.com/watch?v=2iZ4v6KK0OU&list=PLPbgcxheSpE2B7YFfOualUTlRhgnQR7Kn&index=3 🌎 Website: https://www.skillbasics.com/ #️⃣ Social Media #️⃣ 🔗 Discord: https://discord.gg/r42Kbuk 📸 Instagram: https://www.instagram.com/codebasicshub/ 🔊 Facebook: https://www.facebook.com/codebasicshub 📱 Twitter: https://twitter.com/codebasicshub 📝 Linkedin: https://www.linkedin.com/company/codebasics/ ❗❗ DISCLAIMER: All opinions expressed in this video are of my own and not that of my employers'.

detail
{'title': 'Tableau Project For Beginners | Sales Insights : 3 - Data Analysis Using SQL', 'heatmap': [{'end': 237.69, 'start': 209.008, 'weight': 0.855}, {'end': 284.01, 'start': 245.872, 'weight': 0.87}, {'end': 316.512, 'start': 298.86, 'weight': 0.817}], 'summary': 'Tutorial series on tableau project for beginners covers sql data analysis basics, setting up sql server, data management, and sales transaction analysis, with specific instructions and examples, including 150,000 sales transactions and a revenue decline from 336 million in 2019 to 152 million in 2020, emphasizing the importance of sql for generating insights and answering business queries.', 'chapters': [{'end': 185.698, 'segs': [{'end': 81.398, 'src': 'embed', 'start': 28.257, 'weight': 0, 'content': [{'end': 32.879, 'text': 'If you want to build a career as a data analyst, having SQL knowledge is a must.', 'start': 28.257, 'duration': 4.622}, {'end': 37.941, 'text': "So in this video, you don't need any prerequisite for SQL knowledge.", 'start': 33.419, 'duration': 4.522}, {'end': 40.642, 'text': 'You can just start from very basic.', 'start': 38.141, 'duration': 2.501}, {'end': 48.694, 'text': "And we'll look at different tables, their relationship, We'll run simple queries, select where, inner join, et cetera.", 'start': 41.303, 'duration': 7.391}, {'end': 51.276, 'text': "And we'll do simple data analysis.", 'start': 49.314, 'duration': 1.962}, {'end': 65.147, 'text': "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.", 'start': 51.476, 'duration': 13.671}, {'end': 72.272, 'text': "okay, so SQL itself is a data analysis tool and we'll look into that analysis and later in the in the next video,", 'start': 65.147, 'duration': 7.125}, {'end': 79.416, 'text': "we'll see how using tableau can provide some extra benefits or simple SQL queries.", 'start': 72.272, 'duration': 7.144}, {'end': 81.398, 'text': "so let's get started.", 'start': 79.416, 'duration': 1.982}], 'summary': 'Learning sql is essential for data analysis, even without tableau or power bi.', 'duration': 53.141, 'max_score': 28.257, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk28257.jpg'}, {'end': 185.698, 'src': 'embed', 'start': 125.558, 'weight': 3, 'content': [{'end': 131.841, 'text': 'so when you install MySQL, you are installing MySQL server as well as MySQL workbench.', 'start': 125.558, 'duration': 6.283}, {'end': 138.004, 'text': 'so MySQL workbench will allow you to see the tables and run the SQL queries.', 'start': 131.841, 'duration': 6.163}, {'end': 145.005, 'text': 'After you have installed it, click on MySQL Workbench and launch it.', 'start': 139.002, 'duration': 6.003}, {'end': 147.746, 'text': 'So when you launch it, it looks something like this.', 'start': 145.325, 'duration': 2.421}, {'end': 156.35, 'text': 'Now you can create a new connection and I will call it dev sales insights.', 'start': 148.727, 'duration': 7.623}, {'end': 160.152, 'text': 'Keep other values default as it is.', 'start': 157.891, 'duration': 2.261}, {'end': 164.554, 'text': 'My username, password is root root and test connection.', 'start': 160.312, 'duration': 4.242}, {'end': 170.426, 'text': 'it looks good, I just press ok.', 'start': 166.662, 'duration': 3.764}, {'end': 172.909, 'text': 'so it creates this new connection and now you click here.', 'start': 170.426, 'duration': 2.483}, {'end': 177.055, 'text': "so now you're going into that connection.", 'start': 174.434, 'duration': 2.621}, {'end': 182.977, 'text': 'if you want to check the server status, you can click on administration and check server status.', 'start': 177.055, 'duration': 5.922}, {'end': 185.698, 'text': 'here it says server status running.', 'start': 182.977, 'duration': 2.721}], 'summary': "Installing mysql includes mysql server and workbench. creating a new connection named 'dev sales insights' with username 'root' and password 'root'. checking server status shows it is running.", 'duration': 60.14, 'max_score': 125.558, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk125558.jpg'}], 'start': 0.049, 'title': 'Sql data analysis using mysql', 'summary': 'Covers sql data analysis basics, including accessing mysql database, running sql queries, and emphasizes the importance of sql knowledge for data analysts. it also includes the process of installing mysql on a local computer, setting up a new connection, and checking the server status, which is reported as running.', 'chapters': [{'end': 103.581, 'start': 0.049, 'title': 'Sql data analysis basics', 'summary': 'Covers the basics of data analysis using sql, including accessing mysql database, running sql queries, and the importance of sql knowledge for data analysts.', 'duration': 103.532, 'highlights': ['The importance of SQL knowledge for data analysts is emphasized, with the statement that it is a must for building a career as a data analyst.', 'The video covers basic SQL concepts such as different tables, their relationships, and running simple queries like select where, inner join, etc.', 'It is highlighted that even without tools like Tableau or Power BI, data analysts often perform simple analysis using SQL as it is considered a data analysis tool itself.']}, {'end': 185.698, 'start': 103.581, 'title': 'Mysql installation and database access', 'summary': 'Covers the process of installing mysql on a local computer, including setting up a new connection and checking the server status, with the server status being reported as running.', 'duration': 82.117, 'highlights': ["The chapter focuses on installing MySQL on a local computer and creating a new connection named 'dev sales insights' with default username and password, and successfully testing the connection.", 'MySQL Workbench is introduced as a tool for viewing tables and running SQL queries after the installation of MySQL on the local computer.', 'The process includes checking the server status, which is reported as running after the installation.']}], 'duration': 185.649, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk49.jpg', 'highlights': ['SQL knowledge is a must for building a career as a data analyst.', 'Covers basic SQL concepts such as different tables, their relationships, and running simple queries.', 'Data analysts often perform simple analysis using SQL as it is considered a data analysis tool itself.', "Focuses on installing MySQL on a local computer and creating a new connection named 'dev sales insights'.", 'Introduces MySQL Workbench as a tool for viewing tables and running SQL queries.', 'Includes checking the server status, which is reported as running after the installation.']}, {'end': 469.39, 'segs': [{'end': 237.69, 'src': 'heatmap', 'start': 209.008, 'weight': 0.855, 'content': [{'end': 216.355, 'text': 'now i have given the entire dump of our sales insights database on my github page.', 'start': 209.008, 'duration': 7.347}, {'end': 220.419, 'text': 'so follow uh, i have the link in the video description below.', 'start': 216.355, 'duration': 4.064}, {'end': 229.387, 'text': 'so use that link and download this file called DB underscore dump dot SQL.', 'start': 220.419, 'duration': 8.968}, {'end': 234.289, 'text': 'so I downloaded that file here DB underscore dump dot SQL.', 'start': 229.387, 'duration': 4.902}, {'end': 237.69, 'text': "okay, many times people don't know how to clone things from github.", 'start': 234.289, 'duration': 3.401}], 'summary': 'Sales insights database dump available on github for download.', 'duration': 28.682, 'max_score': 209.008, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk209008.jpg'}, {'end': 316.512, 'src': 'heatmap', 'start': 245.872, 'weight': 0, 'content': [{'end': 258.31, 'text': "so, assuming you have downloaded this file now, what I'm going to do from workbench is click on tools, rather server, and say data import.", 'start': 245.872, 'duration': 12.438}, {'end': 265.419, 'text': 'in the data import, click this option and here go to the location.', 'start': 258.31, 'duration': 7.109}, {'end': 271.505, 'text': 'so my location of the dump file is this.', 'start': 265.419, 'duration': 6.086}, {'end': 272.366, 'text': 'so select that.', 'start': 271.505, 'duration': 0.861}, {'end': 284.01, 'text': 'And what this will do is this will create the entire database, along with the records in your system.', 'start': 275.004, 'duration': 9.006}, {'end': 288.473, 'text': 'OK, so this is a self contained SQL file.', 'start': 285.431, 'duration': 3.042}, {'end': 292.535, 'text': 'I created database and exported the entire database to SQL file for you.', 'start': 288.613, 'duration': 3.922}, {'end': 297.078, 'text': 'So now all you have to do is just go here and import it.', 'start': 293.076, 'duration': 4.002}, {'end': 302.622, 'text': 'Then you can click on this button start import.', 'start': 298.86, 'duration': 3.762}, {'end': 306.817, 'text': 'and it is importing.', 'start': 305.956, 'duration': 0.861}, {'end': 308.5, 'text': 'So here it says import is running.', 'start': 306.877, 'duration': 1.623}, {'end': 311.304, 'text': 'Looks like it finished without an error.', 'start': 309.441, 'duration': 1.863}, {'end': 316.512, 'text': 'So now you can click on this refresh icon and you will see this sales database created.', 'start': 311.865, 'duration': 4.647}], 'summary': 'Database imported successfully, creating sales database with no errors.', 'duration': 62.628, 'max_score': 245.872, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk245872.jpg'}, {'end': 421.827, 'src': 'embed', 'start': 367.819, 'weight': 3, 'content': [{'end': 375.945, 'text': "now, if you don't know much about sql and relational database, i will suggest that you do this course on khan academy.", 'start': 367.819, 'duration': 8.126}, {'end': 382.57, 'text': 'it is for free, and that sql course will teach you the fundamentals of relational database.', 'start': 375.945, 'duration': 6.625}, {'end': 386.493, 'text': 'what are tables, columns, foreign key, primary key and so on.', 'start': 382.57, 'duration': 3.923}, {'end': 392.018, 'text': 'In this table I have three columns customer code, name and customer type.', 'start': 387.854, 'duration': 4.164}, {'end': 396.001, 'text': 'These are all the clients of ETLIC hardware.', 'start': 392.278, 'duration': 3.723}, {'end': 401.446, 'text': "OK Let's look at transactions table because that's the most important one.", 'start': 396.802, 'duration': 4.644}, {'end': 411.475, 'text': 'Transaction table contains all my transactions, so you can see columns such as product code, customer code, market code, order date, sales quantity,', 'start': 402.707, 'duration': 8.768}, {'end': 412.636, 'text': 'sales amount and currency.', 'start': 411.475, 'duration': 1.161}, {'end': 418.345, 'text': "Now let's look at products and markets table really quickly.", 'start': 414.122, 'duration': 4.223}, {'end': 421.827, 'text': 'So here you can right click and click on products table.', 'start': 418.685, 'duration': 3.142}], 'summary': 'Sql course on khan academy teaches fundamentals of relational database, including tables, columns, foreign keys, and primary keys.', 'duration': 54.008, 'max_score': 367.819, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk367819.jpg'}], 'start': 185.698, 'title': 'Setting up sql server and sql basics', 'summary': 'Provides a tutorial on setting up a sql server and importing a database dump file, with specific instructions and guidance on using sql workbench tools. additionally, it introduces the fundamentals of relational databases, including tables, columns, and keys, and discusses the structure of tables for clients, transactions, products, and markets.', 'chapters': [{'end': 366.916, 'start': 185.698, 'title': 'Setting up sql server and importing database', 'summary': 'Provides a tutorial on setting up a sql server and importing a database dump file, with specific instructions and guidance on using sql workbench tools, including downloading the database dump file from github and importing it into the system.', 'duration': 181.218, 'highlights': ['The tutorial guides on setting up a SQL server and importing a database dump file The chapter provides step-by-step instructions on setting up a SQL server and importing a database dump file, including specific guidance on downloading the database dump file from GitHub and using SQL Workbench tools for importing.', 'Specific instructions on using SQL Workbench tools for importing the database dump file The tutorial offers specific guidance on using SQL Workbench tools for importing the database dump file, including locating the dump file, selecting the import option, and initiating the import process.', 'Guidance on downloading the database dump file from GitHub and importing it into the system The chapter provides guidance on downloading the database dump file from GitHub by accessing the root repository and clicking on the clone button, then specifies the process of importing the downloaded file into the system using SQL Workbench tools.']}, {'end': 469.39, 'start': 367.819, 'title': 'Sql and relational database basics', 'summary': 'Introduces the fundamentals of relational databases, including tables, columns, and keys, and discusses the structure of tables for clients, transactions, products, and markets, highlighting the importance of understanding these concepts for effective database management.', 'duration': 101.571, 'highlights': ['The chapter emphasizes the importance of understanding SQL and relational databases, suggesting a free course on Khan Academy for learning the fundamentals.', 'The transactions table is highlighted as the most important, containing columns such as product code, customer code, market code, order date, sales quantity, sales amount, and currency.', 'The structure and content of the products and markets tables are briefly explained, showcasing the relevance of these tables in the database management process.']}], 'duration': 283.692, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk185698.jpg', 'highlights': ['The tutorial guides on setting up a SQL server and importing a database dump file', 'Specific instructions on using SQL Workbench tools for importing the database dump file', 'Guidance on downloading the database dump file from GitHub and importing it into the system', 'The transactions table is highlighted as the most important, containing columns such as product code, customer code, market code, order date, sales quantity, sales amount, and currency', 'The chapter emphasizes the importance of understanding SQL and relational databases, suggesting a free course on Khan Academy for learning the fundamentals', 'The structure and content of the products and markets tables are briefly explained, showcasing the relevance of these tables in the database management process']}, {'end': 736.566, 'segs': [{'end': 515.597, 'src': 'embed', 'start': 488.058, 'weight': 2, 'content': [{'end': 494.664, 'text': 'OK, you might have noticed in transactions table I had this which is sales amount is negative.', 'start': 488.058, 'duration': 6.606}, {'end': 495.926, 'text': 'Now that cannot be negative.', 'start': 494.704, 'duration': 1.222}, {'end': 499.87, 'text': 'So I had some messy data here as well.', 'start': 496.746, 'duration': 3.124}, {'end': 503.409, 'text': 'also, few transactions are in USD.', 'start': 501.147, 'duration': 2.262}, {'end': 510.914, 'text': "if you want to do, let's say, some analysis, you have to convert this USD into INR.", 'start': 503.409, 'duration': 7.505}, {'end': 515.597, 'text': 'otherwise, if you are trying to find out the total sum of revenue, it becomes really difficult.', 'start': 510.914, 'duration': 4.683}], 'summary': 'Transactions table has negative sales amounts, messy data, and some transactions in usd, making analysis and revenue calculation difficult.', 'duration': 27.539, 'max_score': 488.058, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk488058.jpg'}, {'end': 575.678, 'src': 'embed', 'start': 544.793, 'weight': 0, 'content': [{'end': 547.735, 'text': 'You can see there is 150, 000 records.', 'start': 544.793, 'duration': 2.942}, {'end': 549.777, 'text': 'So this is not some dummy database.', 'start': 547.755, 'duration': 2.022}, {'end': 552.379, 'text': "It's some serious stuff going on.", 'start': 549.857, 'duration': 2.522}, {'end': 555.582, 'text': '150, 000 sales transaction.', 'start': 552.94, 'duration': 2.642}, {'end': 563.448, 'text': 'Similarly, if you want to look at number of records in customer table, you can say select count star from sales.', 'start': 556.342, 'duration': 7.106}, {'end': 564.749, 'text': 'Sales is the name of the schema.', 'start': 563.548, 'duration': 1.201}, {'end': 568.192, 'text': "By the way, it's here and transaction is the name of the table.", 'start': 564.79, 'duration': 3.402}, {'end': 569.814, 'text': 'So similarly, if I do customers.', 'start': 568.232, 'duration': 1.582}, {'end': 575.678, 'text': 'You know, there are 38 customers.', 'start': 574.178, 'duration': 1.5}], 'summary': 'The database contains 150,000 sales transactions and 38 customers.', 'duration': 30.885, 'max_score': 544.793, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk544793.jpg'}, {'end': 736.566, 'src': 'embed', 'start': 647.092, 'weight': 1, 'content': [{'end': 649.734, 'text': 'So we are already doing now data analysis.', 'start': 647.092, 'duration': 2.642}, {'end': 653.337, 'text': 'We are generating insights from our data by using SQL.', 'start': 649.915, 'duration': 3.422}, {'end': 658.822, 'text': "That's why SQL is one of the tools that you need to know as a data analyst.", 'start': 653.898, 'duration': 4.924}, {'end': 667.503, 'text': 'If your business manager asks you how many transactions we did in Chennai in total, you can go to SQL Workbench,', 'start': 660.157, 'duration': 7.346}, {'end': 670.345, 'text': 'run this query and get the answer immediately.', 'start': 667.503, 'duration': 2.842}, {'end': 678.592, 'text': "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.", 'start': 671.346, 'duration': 7.246}, {'end': 688.158, 'text': 'Then you can run this query click on export here, export the result to CSV and give it to your business manager,', 'start': 679.412, 'duration': 8.746}, {'end': 693.239, 'text': 'so you realize how SQL can help you with your data analysis.', 'start': 688.158, 'duration': 5.081}, {'end': 696.88, 'text': 'now I saw this USD currency.', 'start': 693.239, 'duration': 3.641}, {'end': 700.521, 'text': 'I want to know how many transactions have USD currency.', 'start': 696.88, 'duration': 3.641}, {'end': 703.162, 'text': "so it is simple again it's a where clause.", 'start': 700.521, 'duration': 2.641}, {'end': 712.65, 'text': 'you can say currency equal to USD And you will find there are, luckily, only two transactions.', 'start': 703.162, 'duration': 9.488}, {'end': 714.791, 'text': 'Somehow two transactions have USD.', 'start': 712.71, 'duration': 2.081}, {'end': 721.156, 'text': 'So when we will build our Power BI dashboard, we will convert this USD value into INR.', 'start': 715.312, 'duration': 5.844}, {'end': 724.498, 'text': 'OK, the next thing is.', 'start': 722.416, 'duration': 2.082}, {'end': 731.783, 'text': 'I want to show transactions in 2020 joined by date table.', 'start': 724.518, 'duration': 7.265}, {'end': 736.566, 'text': 'So what does that mean? Well, when I do this.', 'start': 732.583, 'duration': 3.983}], 'summary': 'Using sql for data analysis, can easily answer specific business questions and export results to csv.', 'duration': 89.474, 'max_score': 647.092, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk647092.jpg'}], 'start': 469.39, 'title': 'Data management in sql', 'summary': 'Covers data cleaning in relational databases, filtering transactions in chennai, and sql for data analysis. it discusses challenges of data cleaning, filtering transactions in chennai, and emphasizes the importance of using sql for generating insights and answering business queries, with specific examples and 150,000 sales transactions.', 'chapters': [{'end': 569.814, 'start': 469.39, 'title': 'Data cleaning in relational databases', 'summary': 'Discusses the challenges of data cleaning in relational databases, including dealing with garbage data, negative sales amounts, and currency conversion, and it highlights the importance of conducting primary analysis to understand the database, with 150,000 sales transactions and the need to handle messy data.', 'duration': 100.424, 'highlights': ['The database contains 150,000 sales transactions, indicating the substantial volume of data that needs to be managed.', 'Negative sales amounts and transactions in different currencies require data cleaning for accurate analysis and reporting.', "Conducting primary analysis by using queries such as 'select count star from sales transaction' is crucial to understand the database and address the challenges of messy data.", 'Relational databases pose the challenge of dealing with a significant amount of garbage data accumulated over many years of use, emphasizing the necessity of data cleaning as a data analyst.']}, {'end': 646.352, 'start': 574.178, 'title': 'Filtering transactions in chennai', 'summary': 'Discusses filtering transactions in chennai, with a total of 38 customers and the market code marc001, demonstrating how to display specific transaction records and count the number of transactions performed in chennai.', 'duration': 72.174, 'highlights': ['The chapter explains the process of filtering transactions in Chennai, with a total of 38 customers and the market code MARC001.', "It demonstrates how to display specific transaction records using SQL query 'select star from sales, transactions, limit five'.", "The process includes using a where clause to filter records based on the market code, specifically 'where market code is Mark 001'.", 'The chapter also mentions the option of counting the number of transactions performed in Chennai.']}, {'end': 736.566, 'start': 647.092, 'title': 'Sql for data analysis', 'summary': 'Highlights the importance of using sql for data analysis and how it helps in generating insights and answering business queries, such as the number of transactions in chennai and filtering transactions based on currency, with specific examples and quantifiable data.', 'duration': 89.474, 'highlights': ['Using SQL for data analysis is crucial, as it helps in generating insights and answering business queries, such as the total number of transactions in Chennai, which can be obtained immediately by running a query.', 'Filtering transactions based on currency using a where clause in SQL, for example, finding that there are only two transactions with USD currency, providing quantifiable data for analysis.', 'Demonstrating the process of exporting query results to CSV for sharing with business managers, showcasing the practical application of SQL for data analysis.', 'Highlighting the intention to convert USD currency value into INR when building a Power BI dashboard, indicating a practical use case of data analysis for business purposes.', 'Emphasizing the importance of joining transactions with a date table in 2020 for analysis, indicating the relevance of organizing and presenting data in a meaningful way for insights and decision-making.']}], 'duration': 267.176, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk469390.jpg', 'highlights': ['The database contains 150,000 sales transactions, indicating the substantial volume of data that needs to be managed.', 'Using SQL for data analysis is crucial, as it helps in generating insights and answering business queries, such as the total number of transactions in Chennai, which can be obtained immediately by running a query.', 'Negative sales amounts and transactions in different currencies require data cleaning for accurate analysis and reporting.', 'Demonstrating the process of exporting query results to CSV for sharing with business managers, showcasing the practical application of SQL for data analysis.', 'Emphasizing the importance of joining transactions with a date table in 2020 for analysis, indicating the relevance of organizing and presenting data in a meaningful way for insights and decision-making.']}, {'end': 1088.601, 'segs': [{'end': 987.824, 'src': 'embed', 'start': 909.953, 'weight': 0, 'content': [{'end': 913.362, 'text': 'Oh so you are doing inner join with sales.datetable.', 'start': 909.953, 'duration': 3.409}, {'end': 914.104, 'text': 'So that was correct.', 'start': 913.382, 'duration': 0.722}, {'end': 920.14, 'text': 'So now you print more columns.', 'start': 915.819, 'duration': 4.321}, {'end': 923.681, 'text': 'So these two tables are joined using the date columns.', 'start': 920.16, 'duration': 3.521}, {'end': 929.962, 'text': "So now when I'm looking at this transaction, you know, the good thing is I also know the year here.", 'start': 924.561, 'duration': 5.401}, {'end': 945.888, 'text': '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.', 'start': 930.742, 'duration': 15.146}, {'end': 951.051, 'text': 'So when I do this now it will show me only the transaction from year 2020.', 'start': 945.888, 'duration': 5.163}, {'end': 952.111, 'text': 'You see there is only 2020 here.', 'start': 951.051, 'duration': 1.06}, {'end': 953.472, 'text': 'If you 2019 it will show you 2019 transactions.', 'start': 952.131, 'duration': 1.341}, {'end': 963.277, 'text': "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.", 'start': 953.532, 'duration': 9.745}, {'end': 980.78, 'text': 'So the way I can do that is I can just print sum.', 'start': 973.635, 'duration': 7.145}, {'end': 987.824, 'text': 'So I can say sum sales.transactions.', 'start': 982.061, 'duration': 5.763}], 'summary': 'Joining sales and datetable to filter transactions by year and calculate total revenue.', 'duration': 77.871, 'max_score': 909.953, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk909953.jpg'}, {'end': 1088.601, 'src': 'embed', 'start': 1017.688, 'weight': 2, 'content': [{'end': 1028.461, 'text': 'Similarly, if you do 2019, it will tell me 2019 had this much revenue.', 'start': 1017.688, 'duration': 10.773}, {'end': 1032.824, 'text': 'so you see, the revenues are, I think, declining because previous year it was 336 million.', 'start': 1028.461, 'duration': 4.363}, {'end': 1034.845, 'text': 'now it is, I think, in 2020, the revenue is 142 million.', 'start': 1032.824, 'duration': 2.021}, {'end': 1052.015, 'text': 'so this way can get the aggregation data, the aggregation insights.', 'start': 1034.845, 'duration': 17.17}, {'end': 1053.956, 'text': 'you know you can do some.', 'start': 1052.015, 'duration': 1.941}, {'end': 1057.698, 'text': 'you can do average anytime you are facing issues with SQL.', 'start': 1053.956, 'duration': 3.742}, {'end': 1059.739, 'text': 'just just google it.', 'start': 1057.698, 'duration': 2.041}, {'end': 1062.34, 'text': 'google is your friend in SQL.', 'start': 1059.739, 'duration': 2.601}, {'end': 1070.385, 'text': 'you can say SQL average column And you know that there is so much help available in Google.', 'start': 1062.34, 'duration': 8.045}, {'end': 1074.889, 'text': 'See they will show you simple table and give you all the syntax.', 'start': 1070.886, 'duration': 4.003}, {'end': 1078.352, 'text': 'So do not worry about this SQL syntax that much.', 'start': 1075.59, 'duration': 2.762}, {'end': 1081.014, 'text': 'It is actually very very simple.', 'start': 1078.992, 'duration': 2.022}, {'end': 1088.601, 'text': 'Alright, so now we have some what we want is now we want the sum.', 'start': 1082.195, 'duration': 6.406}], 'summary': 'Revenue declined from 336 million in 2019 to 142 million in 2020.', 'duration': 70.913, 'max_score': 1017.688, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk1017688.jpg'}], 'start': 740.347, 'title': 'Analyzing sales transactions and revenue with sql', 'summary': 'Covers performing joins to analyze sales transactions for 2020, calculating total revenue, and using sql to reveal a revenue decline from 336 million in 2019 to 152 million in 2020, while highlighting the simplicity of sql syntax and access to google assistance.', 'chapters': [{'end': 987.824, 'start': 740.347, 'title': 'Sales transactions analysis', 'summary': 'Discusses how to perform a join between sales transactions and date tables, allowing the user to filter and analyze transactions for the year 2020 and calculate the total revenue.', 'duration': 247.477, 'highlights': ['The process involves performing an inner join between the sales transactions and date tables based on the order date, enabling the user to filter transactions by year, such as displaying transactions only from 2020.', 'The user can calculate the total revenue for the year 2020 by utilizing the sum function on the sales.transactions data.']}, {'end': 1088.601, 'start': 989.365, 'title': 'Sql revenue analysis', 'summary': 'Discusses obtaining revenue data using sql queries, revealing a decline in revenue from 336 million in 2019 to 152 million in 2020, and emphasizes the simplicity of sql syntax and the availability of assistance through google.', 'duration': 99.236, 'highlights': ['The revenue declined from 336 million in 2019 to 152 million in 2020, indicating a significant decrease.', 'The transcript emphasizes the simplicity of SQL syntax and the availability of assistance through Google for SQL queries.', 'The chapter mentions the method to obtain revenue data using SQL queries, providing insights into revenue trends and aggregation data.']}], 'duration': 348.254, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk740347.jpg', 'highlights': ['The user can calculate the total revenue for the year 2020 by utilizing the sum function on the sales.transactions data.', 'The process involves performing an inner join between the sales transactions and date tables based on the order date, enabling the user to filter transactions by year, such as displaying transactions only from 2020.', 'The chapter mentions the method to obtain revenue data using SQL queries, providing insights into revenue trends and aggregation data.', 'The revenue declined from 336 million in 2019 to 152 million in 2020, indicating a significant decrease.', 'The transcript emphasizes the simplicity of SQL syntax and the availability of assistance through Google for SQL queries.']}, {'end': 1245.636, 'segs': [{'end': 1170.258, 'src': 'embed', 'start': 1130.269, 'weight': 0, 'content': [{'end': 1133.851, 'text': 'so if you look at market code here, see Chennai market zero, zero one.', 'start': 1130.269, 'duration': 3.582}, {'end': 1137.394, 'text': "that's why okay.", 'start': 1133.851, 'duration': 3.543}, {'end': 1139.015, 'text': 'so where is my thing?', 'start': 1137.394, 'duration': 1.621}, {'end': 1154.844, 'text': 'And this is now showing me the revenue in Chennai, which is 123123 2.4 million.', 'start': 1147.677, 'duration': 7.167}, {'end': 1160.149, 'text': 'You can also show the distinct products.', 'start': 1155.825, 'duration': 4.324}, {'end': 1167.635, 'text': 'For example, if you want to know the distinct products that you sold in Chennai, then you can print this query.', 'start': 1160.229, 'duration': 7.406}, {'end': 1170.258, 'text': 'Select distinct product from that.', 'start': 1167.816, 'duration': 2.442}], 'summary': 'Chennai market revenue is 123123 2.4 million, with option to display distinct products.', 'duration': 39.989, 'max_score': 1130.269, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk1130269.jpg'}, {'end': 1245.636, 'src': 'embed', 'start': 1199.12, 'weight': 1, 'content': [{'end': 1207.384, 'text': '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.', 'start': 1199.12, 'duration': 8.264}, {'end': 1212.148, 'text': 'Also, I have given all the queries that I ran in this video on that page.', 'start': 1207.805, 'duration': 4.343}, {'end': 1219.452, 'text': 'So try those queries out and try to perform some analysis and try to generate some insights just using SQL.', 'start': 1212.548, 'duration': 6.904}, {'end': 1222.935, 'text': 'In the next video, we will use Power BI.', 'start': 1220.173, 'duration': 2.762}, {'end': 1227.218, 'text': 'And Power BI will be connected to the same SQL database.', 'start': 1223.335, 'duration': 3.883}, {'end': 1235.466, 'text': 'and we will do some data cleaning, because you saw some usd columns, there were some negative values in sales and all of that.', 'start': 1227.998, 'duration': 7.468}, {'end': 1240.771, 'text': "so in power bi, we will initialize the model in the next video and we'll do data cleaning.", 'start': 1235.466, 'duration': 5.305}, {'end': 1243.414, 'text': "i hope you're liking this series so far.", 'start': 1240.771, 'duration': 2.643}, {'end': 1245.636, 'text': 'if you are, then please give it a thumbs up.', 'start': 1243.414, 'duration': 2.222}], 'summary': 'Instructions for downloading sql dump, running queries, and using power bi for data cleaning and analysis.', 'duration': 46.516, 'max_score': 1199.12, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk1199120.jpg'}], 'start': 1089.641, 'title': 'Chennai business analysis', 'summary': 'Covers conducting business analysis in chennai using sql, including querying revenue and distinct products sold, resulting in a revenue of 2.4 million and a list of distinct products. the transcript also mentions the availability of sql dump and instructions for database initialization.', 'chapters': [{'end': 1245.636, 'start': 1089.641, 'title': 'Chennai business analysis', 'summary': 'Covers conducting business analysis in chennai using sql, including querying revenue and distinct products sold, resulting in a revenue of 2.4 million and a list of distinct products. the transcript also mentions the availability of sql dump and instructions for database initialization.', 'duration': 155.995, 'highlights': ['Conducting business analysis in Chennai using SQL, querying revenue, and distinct products sold, resulting in a revenue of 2.4 million and a list of distinct products.', 'Availability of SQL dump and instructions for database initialization and running queries for performing in-depth data analysis.', 'Upcoming video on using Power BI to connect to the same SQL database, initializing the model, and performing data cleaning.']}], 'duration': 155.995, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fgMD2wvpvpk/pics/fgMD2wvpvpk1089641.jpg', 'highlights': ['Conducting business analysis in Chennai using SQL, querying revenue, and distinct products sold, resulting in a revenue of 2.4 million and a list of distinct products.', 'Availability of SQL dump and instructions for database initialization and running queries for performing in-depth data analysis.', 'Upcoming video on using Power BI to connect to the same SQL database, initializing the model, and performing data cleaning.']}], 'highlights': ['SQL knowledge is a must for building a career as a data analyst.', 'The tutorial guides on setting up a SQL server and importing a database dump file', 'The database contains 150,000 sales transactions, indicating the substantial volume of data that needs to be managed.', 'The user can calculate the total revenue for the year 2020 by utilizing the sum function on the sales.transactions data.', 'Conducting business analysis in Chennai using SQL, querying revenue, and distinct products sold, resulting in a revenue of 2.4 million and a list of distinct products.']}