Data Wrangling using SQL | Data Engineering | Community Webinar

Key Takeaways

let's get started with what is data wrangling and I feel like data wrangling in the data and analytics space similar to data science has become kind of a buzzword and it's used so often but what does it really mean and so I'm going to Define data wrangling as the process of transforming data and preparing it for analysis now that process is going to vary depending on the data that you're using and the project the business questions that you're looking to answer so not every data wrangling not all the steps that you use for one project in terms of data wrangling are going to be applied to another project but the it's really the term data wrangling encompasses a lot of activities and that includes some of the activities that we'll be doing today such as joining multiple data sets cleaning outliers and then merging data so today um I have five goals for you but I do want to be clear that this is not going to be like a a live coding follow along session you will have the data sets available to you along with the slides um that includes the exercises that we'll be reviewing today and their Solutions that's all available in my GitHub repository um and then just in terms of housekeeping I will be doing this in D beaver and D Beaver is a oops it is a a free multi-platform database tool and I have used it at multiple organizations I'm currently using it at Carter's so I feel like you know uh if you were again I was trying to kind of think of think of tools that were available to you even if you weren't using it actively in your organization and dbiver is a great tool to have at your disposal so out of the goals that we're going to be reviewing today um I want to I want you guys to be able to query a data set join data Union data filter data and create new columns and that's essentially enriching data with new features but why SQL like out of all of the tools all of the programming languages that we have available and there are some some tools and programming languages that are really specific and conducive for data wrangling why SQL and I actually think that SQL is one of the best assets that you can have at your disposal um and it is one of the best investments of your time uh for two two main reasons the first being that it's standard so if you think about SQL it's really used to interact with relational databases well how do organizations capture their data through relational databases right um the and SQL syntax is really standardized across databases like Oracle uh MySQL server uh redshift which I'll be using today right so think of SQL as an overarching language uh that has several dialects and there was actually a question in the Q a that I answered about like what variant of SQL will you be using but the point of it being that it's it's standard enough that you can learn it once and you are going to be equipped to query multiple databases right the other part that I really like is that it's scalable so it could be used to process large data sets without you having to extract the data first and then I also saw that some of you guys have experience with um some VI tools right so if you're using Tableau you can establish uh connections to these relational databases query them and wrinkle your data with SQL which is a lot of what we do at Carter's um and then schedule your queries to run on a regular basis right to update the data and reprocess that data without you having to make updates to your code so it's really skill and that aspect if you think about if you're using Excel now I mean think about how not scalable that is right so um I this is one of the great reasons why SQL is really a great asset to you and what I'm going to try to do is I do recognize that a lot of you guys have experience maybe not in SQL but you have a relevant experience in Excel and other tools um and in in some of these bi tools as well right so I'm going to try to kind of Leverage some of that when we think about these high level concepts of data wringling with SQL for these exercises we're going to be using uh four different data sets um and if you use Tableau these might be familiar to you I actually derived these tables from tableau's Superstore data set so if you've uh Superstore data then you're likely very familiar with this right we have orders archived orders products and customers and uh below each table name I've listed out the columns in them and then their data types right um and and this is always important for you to know the other thing that I do want to know about some of these exercises is that they're going to be very aggregate exercises of these tables so we're going to really pull like high level information from them and in practice you're likely going to want to pull more granular data sets for data analysis and data modeling but the point of the exercises is that you can extend them to get more granular and you can update them right and the the other great thing that I'm going to try to kind of embed throughout the webinar is that we're going to run through five different exercises but you can at the end and as you're practicing right you can kind of combine those exercises to build a more comprehensive data set um and I think that'll make a little bit more sense as we as we go along all right so let's get started with our first school of how to query a data set um and I have to tell you guys a little story real quick so when I started at an organization it was a fortune 5 haircut company and I had you know several years experiences getting really frustrated though because I was sitting in an I.T function alongside you know software engineers and I had to start writing a sequel and I had never written SQL before I uh I had written in different statistical packages I had used R before I was really professional in stata which isn't a statistic a statistical programming package for economics and I didn't know how to write SQL what was frustrating and I think the reason that I was kind of struggling through it was I was so focused on the syntax and kind of like how to write in that language and less focused on the um the methods that I had already been practicing throughout my career right how to grab data how to join different data sets and so that's what I'm really going to try to focus throughout this webinar again is less on the exact syntax and less on the you know the the specific dialect of SQL and more on the concepts of data wrangling and how to leverage SQL to do it so uh when I was working at the sports and 500 company my colleague would take a piece of paper and he would do a trifold and at the top of each section he would write select from where and he would then have me list out how uh the fields that I wanted to include in my results at my data frame which tables I was going to have to query to get those columns and if I needed to uh kind of apply any special conditions to filter that data and I would use that where section of the paper and that really helped me kind of think about how um visually how to structure my queries to kind of build a skeleton framework right and so that's what we're going to do today our first exercise is going to be what was our historical annual profit I'm gonna uh switch over so you guys are gonna have to pair with me here all right so the first thing that I do whenever I start working and I'm going to not save this one all right whenever I start working with a table that I've never seen before or a table that I've seen a million times is I like to first take a peek at that um table so I'm going to select start and star means everything I'm just going to pull everything from that table and I'm going to pull up my orders table right uh I know from the column list I thought that's where I had my profits and I'm going to limit five so if you have used um I'm gonna run this if you've if you've used a python before this is where you would print head five of your data frame so again very similar concept so right here what I'm doing is I'm just pulling a sample of five rows for my orders table to get a sense of the data all right so I see here my column names I see the data types and I can kind of get a sense of granularity from this table right I see that it's at the order level and then I also have a couple of ideas like customer ID and product ID which I can use to enrich with uh additional customer and product information all right so our first exercise asked us to identify historical annual profit well I can start by just kind of updating my query and I'm going to select you know and I I need gear and I'm going to derive here from my order date so I'm going to just select order date from here and then I need profit all right so let me also show you really quick what distal select star looks like all right so this if I were to export this data it would be all of the data inside of this table because there's no limit here if I go again I'm only interested in date because that's what I'm going to use for a year and profit because I want to find the most Pro because I want to find profit by year firm orders let's see what this looks like too it's a lot of rows and I really wasn't expecting this because I was kind of expecting it to aggregate on the day right I would have one row per day What's Happening Here is that we're really using the same data that we had in this query but we're not aggregating it to any level higher right we're just excluding columns because we didn't include them in the select so because I'm wanting to extract gear for my order date and aggregate my profits at that level I'm gonna have to make some updates here so if you have used Excel you are likely familiar with the date part so take the year from my order date and I'm gonna take profit so let's see what that looks like looks the same and I'm not paying attention to the chat but if you are active in the chat I want you to kind of give me a suggestion of what you would expect uh for me to do here and if you've used Tabla or power API you likely know that and you've you know updated uh created any any uh calculated Fields you probably know that I need to aggregate my profit um and Summit here so I'm gonna sum profit you can do uh different types of aggregation so you don't have to do some depending on your kind of use case of the business question that you're looking to answer here I want to find a total profit so I'm going to do that I need to do one more thing I need a group by my year and so let me show you what that looks like all right so now I have one row for each year and the total profit there are a couple of things that are happening here that I want to kind of clear you and on because I think it's going to become important as we work through the rest of our exercises um let's pay attention to our our column names they're going to be kind of generic based on the functions that I've uh used so here we see some here we see uh postgres date right date part I'm gonna Alias this and to Alias uh field means to kind of think about giving it a nickname I mean I think um that's pretty common right to to reading columns so that's what I'm going to do with an alias I'm just going to add that name that I want to call that column after the after I select it so here for my date part I'm going to name it yr for year and I'm going to do that instead of spelling out here so that it doesn't confuse it with the function and then for profit I'm going to call it like annual profit all right so now here you see that I'm aliasing the column names now this is something that I'm going to do I'm going to explain it I don't want you guys to focus too much about it because it is about syntax but when I was talking about the dialect because I'm using um redshift I can extract Year from order date and that's actually the way that I prefer to do it so I'm gonna do that I don't want you to pay attention to that too much I just want I wanted to tell you that that's what I'm gonna do because I'm in uh I'm in redshift and this is my preferred way of doing it and then another thing that you're going to see me do is when I group by so if I had multiple um dimensions in my select Clause I would have to group by each of those you're going to see me alternate between explicitly naming the columns and then referring to them and uh by their uh position I prefer position because I don't want to type out all of the the names so I'm gonna I you saw that I switched it up to group by one and that means this group by the first column in the data frame all right okay and then if I go back to my slides there is another question of how much product was shipped by each ship mode so now that we've kind of walked through querying the orders table to find to aggregate to an annual level and Define profit I think uh we're pretty equipped here to do the same thing with ship mode so I'm going to do the same thing here I'm going to select ship mode and I'm gonna remember to send quantity um orders and I'm going to group by one now I need to go back and do this to aliasm all right so any questions here before we move on to our second concept of joining datum yes so we have a few questions um we'll start with Christy uh so what program do you use are there programs you recommend for SQL um in terms of if I can kind of talk about the tool stack that we have at Carter's which I is for me kind of phenomenal we use D Beaver to access our databases to query them so I I that's the tool that I use I stick with them that I recommend um and I've used it across organization so to me it's pretty standard um and then in terms of bi you know a bi tool we use Tableau I'm not as familiar with um Power bi but the reason that I bring that up is because we also leverage Tableau to write custom SQL queries against our databases but if I all I was doing was just accessing um the relational databases I do it directly through the DB verb okay one other uh I guess um non-technical question is can you zoom in on your code um it's just coming a little small there we go um if everyone in the chat if you were having this issue is seeing uh can you can you let me know if that's big enough and then if not I'm sure we can we can make it bigger but um it looks good from my end so um anyways back to technical questions um uh can you use Alias for group Phi and this is followed up with or can you only use by position so you cannot you can't use aliases if I for instance hear an aliasing the year as yr I can't root by y r because it's not going to know oh it will so I take I take that back sometimes it'll let you do it sometimes it won't um I wonder if this is because I have already pulled it um so give it a try I'm telling you if you see me code you're always going to see me use my uh use the the line position because I don't want to type it out especially again our our exercises are going to be very aggregate where we're going to just choose a few columns from our data frame but in practice you're going to be building out data frames with a lot of columns and your queries are likely going to look longer than the ones that I'm going to be writing so just imagine having to like you know type out I don't know 20 more Fields versus typing out 1 through 20. like sounds okay and then one that I think is somewhat similar and um I'm hoping we can infer the question but it just says Group by y r which I think stands for year um but it just has a question so a question mark at the end of it so I'm hoping we can infer um something out of that sure yeah I'm not sure though that's probably in terms of like grouping by the Alias so um but but if I may so another thing that I think might not be as obvious in this exercise because we're only choosing one um one dimension here let's say that we're looking at you know the year we do want to see um the customer ID so I'm going to add that in here now I need a group by customer ID as well right because that's an additional level of detail within my data frame and so I need to add that to the group by and this is a very important Concept in terms of like making sure that your data is your data frame is aggregating to the right level as uh the concept of like grouping on all of the dimensions that you're using in the data frame and and accurately aggregating your metrics okay perfect and we have a few more questions that I think should be pretty quick to get through so we're going to do those really quickly as well another group by question can you group by alias well I in this example I thought that we couldn't but we could okay so so if you remember yeah where I had it where I was like oh it's not going to recognize what year is because I had aliased it and then it did so you can but I know that in some dialects it will not allow it okay and um uh this is uh I'm just going to read it out it says oh so we don't need to use the as prefix you can't okay so this is also a good question um you you can and some dialects and I'm not you know an expert in all of the SQL dialects but sometimes what you're gonna have to do or what you will see if you're reviewing somebody's queries they will specify the column that they're selecting and then they Alias with the as keyword and you can do the same thing for order so I could Alias my orders table as o and refer to it to the table with the um the prefix o throughout the query or I could Alias it like bus with the as command and so this will return the same results okay and then last question before we move on uh what acts what extract function did did you compare to date part what extract function okay so in uh what in the postgres sequel I used extract and then years it's the same and here you just have to enclose it in and um first okay perfect that's it for now so I'll let or I guess that's not it for now but we're gonna save the the rest of those questions uh for the end uh so let's continue make sure we have enough time to uh to to get through the presentation and um and we'll stop for questions again uh in the next section all right thank you yeah so the next concept that I really want to kind of focus on and this is you're gonna run into this very quickly at any organization across Industries is joining data right and that's the idea of combining data from multiple tables and really combining that table with the datum by adding columns from different tables right you're gonna combine the data on a common key or a shared field across the tables and something that you're gonna have to really uh be aware of in-joins is duplicating records and there are a couple of questions that I ask myself if I'm seeing kind of duplicate records is am I I using the primary key all of the primary keys to join tables and are my tables aggregated up to the same level if not am I clear on the type of aggregation rates that sometimes business users or you as a technical user might have some misconceptions on the the level of detail in the tables so this is one of those slides where I was warning our SQL experts don't come at me because I know that this is not an inclusive list of the types of Jordans right there are many drawings many more joints than I have included on here that are available to you to me these are the three most common I have um but experience them throughout my experience but then also these are the three most common that I leverage right the left join game number one um in terms of left join what that's going to do is it's going to return all of your records from table one which is also considered your left table and then it's going to return matching records from table two and this is the one that we're going to use in our exercises right uh there's also this concept of an inner joint and what the inner join is will do it'll return records that have matching values in both tables right so it's only going to return like the common rows and an outer joint is going to do the opposite it's just going to return everything so it's going to return all your records from both tables does both with or without a match um there's something that I wanted to mention here but I think we're going to get to it um okay so what you're left join your left joint and your right join are very similar um just think about bump swapping uh places in terms of your right drawing is going to keep everything from your right table and that's why I was saying uh that for me these are the three most important because they will cover most of your use cases like if you can do everything with the left join why would you even bother with the right drawing all right so for our exercise uh the first one being what was the most profitable region overall so let me hop back over to the beaverm all right so I'm gonna have to do a refresher on um the tables that I have I think I saw from the the Lister tables that I provided that I had in region in my customers taper so let's take a look all right so I have region here I have customer ID and that's what I'm going to use to join on um and then I also have that's all I need all right so let me pull some data from my orders table because I'm interested in profit um actually what I'm going to do is I'm just going to start by selecting everything from my orders table and then what I want to do let me Alias this so I'm going to say take everything from orders but only take region from customers I'm going to left join customers to my orders table so remember I'm going to use my customer ID so now I have everything from that orders table and then I have regions now I can really see kind of the region for that order now we can kind of start aggregating to a level up all right because I was uh I'm only looking for the most profitable region I'm going to keep region I'm going to take the rest of orders out because I don't need that much detail but what I do need is the sum of the profit and we already practiced aggregating um to a higher level so I know that I need to wrap profit and a sum and I need to group by and I'm gonna um start splitting out my lines I think it's easier to read but be it's more common um so when we run this um let's take a look all right so here I see the region unlim a profit we can Alias some which we should have done we'll call this Regional profit here now I could have done this with the right join as well uh and actually because I'm only using region from this table it really wouldn't matter because I'm aggregating anyway um but we could have done uh so we could have done from customers left drawing region that would have yielded the same results or we could have done select from customers um and then write joint orders that would have been the same thing as this just because we slept the order of our table soon we were referring to orders us the left table or the right table let me pause here do you guys have any questions yes um uh all right so first question um uh what is the name of the platform that you're using for uh SQL query on data all right so I'm using AWS Workshop so I'm accessing redshift and that's where my data is uploaded and stored okay of course and then is that what does that answer the question I'm not sure I think so but we'll find out um so so going back to what you're just doing um does it some even if the profit for that region is null um like there's no issues with that or um do you need to explicitly handle it like set to zero if null yeah exclusively gonna aggregate the values that it does have right because it's at a higher level there are some times where you'll have to coalesce funnels or you know uh adjust for them but because this is very uh aggregate we don't need to that is all of the questions I'm seeing seeing that are relevant to this section so I'd say let's move on and again we'll answer any questions that we haven't answered we will answer at the end Okay cool so there was another uh in the joint exercise there was another option to um or another question which is what is the best selling category and I want to show you guys this because it's uh pretty relevant so again I'm going to go back to my favorite thing is just to take a peek at the data all right so if you think about this when you're analyzing data your product ID and your customer ID um they're interesting when you're doing the data wrangling but in terms of analyzing the data and really uh distilling it you're not going to learn a lot from product ID right you need all of the other dimensions and just descriptions from that product to give your data meaning and so uh this is again a very aggregate exercise where we're pulling just one level of our product and aggregating on sales but in reality in practice what you would do is you would select you know all of your interests all of the fields that you're interested in from your orders and start enriching that data with different columns from your products table and your customers table so here we were interested in the best selling category it's very similar to the exercise that we just did so I'm going to select category from this table that I'm going to call this um I'm going to call this products table key for these all right Alias up there and now I'm going to some sales and sales is going to come from my orders table okay um I don't I don't want to go on a product on a down a rabbit hole but when we get to the where section I make sure you guys another kind of quick way that I I get away with not using the left join and the ons explicitly I take some shortcuts um all right it's a product ID let's see what the sealed [Music] um so let's Group by one all right so same concept we're joining on um on a common key which were our IDs that we had across two different tables pulling the columns that we're interested in and then aggregating our measures so again we could do something like this which is likely a more applicable root by one two three and see how much time I saved I don't have to explicitly call out each of my columns um but this is more realistic query right for you to do analysis and um and data modeling where you're kind of looking at row level data but enriching it with different columns all right let me move on to our next concept which is unioning data and so unioning data is is kind of the same concept of joining where we're looking to combine data from different uh tables or different result sets right here instead of adding columns from the other table what we're going to be doing is we're going to be stacking uh results or data sets data frames on top of each other right and the results set should have the same number of columns and they should have similar data types The Columns should be in the same order as well so that's very important and they're really just two types of unions Union and Union all when you uh put a union clause in your query it's only going to keep unique records from your two result sets if you did union union all it's going to keep everything all right so we're gonna kind of look back to our first exercise which is what was historical annual profit and we're gonna um enrich that data with some of our archived orders right so we have and this again is a really relevant example in in uh in practice where relational databases have their limits and so a lot of the time dbas or database administrators are going to Archive tables based on uh you know some type of time frame so uh most of the time they're only going to retain like three years of history and then anything older than three years they're gonna archive and they're gonna kind of like put in a separate location that doesn't mean that it's not accessible to you but it just means that it's in in the archives and that um it's not readily available like all of your other tables right all right so let me go back to the beaver and let me start from scratch all right so I'm gonna query you guys are so sick of me doing this but I'm telling you it's so helpful all right so here I'm gonna just kind of take a peek at the columns that I have in my table at the order of the columns um I'm going to kind of take it get a sense of the data types and then I'm going to do the same thing for archived orders because I remember I have this constraint that they have to be in the same order have the same data types all right um now now that I've kind of done some of that sanity checking and I feel confident in that I'm gonna link to Union those two tables again stack one on top of each other uh one on top of the other so I'm going to take all the data that I have from orders and then I'm going to stack it on top of my archive orders to get a full sense of History and because I am um aggregating this to really high level against at the annual level and we've already worked on this query if you guys remember it was select year um order date we did some sales or it was profit sorry Susan profit from orders so this is the query that we're originally working with so we have two options here um or two methods that we can employ the first method is we can do the same thing here and but to the archived orders and and then kind of Stack those results sorry let me see what's going on here [Music] um that's okay I have I have my background and in my backup I reverted to date part the error message by the way was not related to extract gear from or date part year and likely I missed something if you're new to SQL by the way and you are um trying to debug your query look for a missing comma look for an extra comma there are uh a lot of those things would kind of trip me up again kind of focusing on the syntax of SQL as I was learning um so kind of focus in on that so as this is running and I shouldn't take that one but let's see what happens as this is running again let's focus on the methodology here um what I've done is I've aggregated my orders to look at the year and the total sales and then and really I should have swapped this out for profit but um and then I did the same thing for archived orders and then taking those two result sets and stacked them on top of each other something else that I could have done let me start a new one something else that I could have done is I could have just taken the at all of the row level data right so like stacked the two tables on top of each other and then aggregated from there so actually let me do that I think that this is debugging so what I'm going to do is I'm just gonna I'm just gonna let it like Let It Roll um timed out all right let's see all right so it's working fine now I don't know what happened here but I'm not saying that one all right so let's go back to thinking about aggregating the results that's among using them so let me select here and Order date from orders and I want to group by one all right let me do the same thing to my and before I do that I'm going to make sure that this one works all right let me make sure that this one works because last time this one was tripping us up all right this one's working so now let me Union them all so here I have the result set from My First Data frame stacked on top of my result set from the second data frame any questions here always so um we'll start with uh we have two questions that are fairly similar so we'll start with one and see if we need to ask the other um but can you achieve a union also by just using joints um the time that the Box achieved through like uh an outer join but for me really when we think about so you know as you guys are developing your SQL skills and your data wrinkling skills there are processing costs to that and then um there's also kind of like best practices so if you're again looking to really just kind of Stack to result sets on top of each other uh Union is the way to go but yes you can achieve it through an outer join um and then a similar question is how is a union and a full join different they're not unless you I mean they are not really so again you can use them interchangeably but I think that um there are times where you're going to get some of those null uh rows or columns with null Fields if you're doing that after a drink but yet you you can use them interchangeably uh and can you explain uh a little about avoiding duplicates in joints in Joins Okay so and this happened to me a lot so think about um let me bring up an example with products maybe I think that might be a good example so you may have a product ID for you know your company might produce like 10 product IDs um and those product IDs are similar across your country but they they are different in the way that um every country has like some Nuance to it right so like you're producing let's say um a water bottle and the the same product key let's say product one two three for us and product one two three for Canada might indicate a water bottle but a different type of water bottle and so when you're looking to join to the order you might not be aware that if you don't join to both the product ID and to your country code because that's also an identifier there you're going to be duplicating records and so um you could be and by duplicating records what I mean is that if you're not choosing those unique keys or groupings of primary Keys across tables you could be uh like duplicating rows because that same key shows up twice or three times in your um in your data table so think about water bottle one two three uh when I join that to my products table I have two rows of product one two three one for each country one for Canada and one for us right so when I join it to an order and I'm only joining it to my product key that one order is now going to have two rows and so I'm going to duplicate the sales for that the quantity for that and the profit for that because I fail to recognize that uh there was a Nuance in the unique identifier or the the primary key that there were two it was the combination of product key and country that were really identifying the the unique row of data does that make sense I think so but you're also talking to someone who has no experience I don't know if they can do it like a thumbs up or something um I'd love to see the thumbs up and um uh so there is a follow-up question uh so does that mean you have to include all of the unique identifiers when joining um yes all of the relevant unique identifiers across the tables yes um and but sometimes sometimes um your uh your product ID or like your their the sun tables some some beers uh go to the external creating like unique keys that already can handle for that right so like let's say the your the combination of product ID and country are the fields that you need to join on there could be a column called like I don't know like skewed for instance uh that's that's the combination of both and that skew is unique so you can just use skew you would use if you'd have SKU you would use it across the data sets so so yes you should use you should join on multiple keys if they are applicable to your data set sometimes you may be able to just use a unique key because that's been taken care of okay we we will answer a couple of other questions before again moving on uh before we start answering more questions we did release the certificate of attendance form so uh just be on the lookout for that in the chat um uh and some we we've had a couple people that want to confirm um so you're using uh D Beaver with AWS redshift yep perfect all right so that is confirmed um [Music] uh okay um do you want me to go back to my science or no I've I'm just trying to get caught up with my questions because I've been dismissing them and now they're all changing the order um so what does the C dot region represent yes C dot region oh okay so that was an earlier snippet that I did okay let me do that all right so I am aliasing my customers table with the Alias C okay and so when I refer to region I can prefix it with the table alias so that the query explicitly knows Pig region from the customers table can I can I not to go in a rabbit hole but I do want to show you guys this um and then uh Seadog region from customers okay so if I didn't specify customer ID here it's going to say that the customer ID is ambiguous and that's because customer ID I'm sorry let me fix this here for you guys that's because customer ID appears both in the customers table and the orders table and so I need to explicitly tell the query where to get the customer ID column from this is likely going to happen to you if you are joining tables um that have fields in common like dates and things like that where you're going to have to be explicit with kind of where where you're picking the column from so I'm going to pick customer ID from my orders column so I'm going to say pick uh customer ID from orders because I'm prefixing it with the table Alias and then give me region from C which is customers now if I I call this like cast then I'd have to update this and if I didn't update it it would tell me well I don't know what c is C is undefined C relationship does not exist because I aliased it as cast right okay um uh next uh two questions we'll ask them at the same time before uh we move on uh why is there a null in the union answer so going back to what was up there before and then um uh how can we eliminate the null rows in such scenarios okay if this person has been paying attention and if you really want to know why there was a null um my answer should be that I did this on purpose so that we can work on this in our next exercise but it really wasn't on purpose it was um it was my fault it was erroneous records as I Was preparing the tables but I kept it in there on purpose because if I go back to our next exercise filter so how do we filter data you can do this do you think the the where statement and what the where statement does is it allows you to specify conditions that must be met by your results at or your data frame and you can use many operators this is just kind of like a list of some of them um the exercise here was like how do can we identify the names of customers in just the east region but before we do that let's clean up the um the null which is erroneous right so I'm going to come in here and I'm gonna say um so I already have everything kind of filter I have everything kind of pulled um but Within These results sides I'm going to say um where so select from where every time it says like from where I think about the triple that my colleague had given me so um we're gonna do where the the date part or extract year foreign so I'm gonna pair this with a couple of operators right so is not and this doesn't have to be capitalized it's not and then null which is another operator um and then I'm gonna Group by I'm going to do the same thing in my second result set sorry I'm just trying to kind of clean this up because I made everything so large now and really if you as we're sampling it we saw that we didn't have a null in the orders table we really only had it from the archived orders we could have applied this transformation to just archived orders but I shouldn't hurt to do it in here all right so we've using where we filtered out the null records okay all right um and then well let me pause there because that was a new concept of filtering any questions there Nathan um let me check uh not at the moment I would say let's just continue and and then I guess maybe uh the rest of the questions we can see yeah yeah that sounds good okay and so then the next um concept that I want to talk about is creating new call The Columns or enriching your data set your results on um and so with these columns you can add criteria to your select cause to create a new field so an interesting exercise given the data set that the data sets that we've been working on um can we create a new feature called customer status and the criteria for that is if the customer had more than one order we're going to label them as an existing customer otherwise we're going to label them as a new customer if you are Excel Savvy you might be used to maybe like an if a function where you would say you know if the account is greater than one existing otherwise or else new um if you've used other programming languages um or calculated Fields like in Tableau you might be used to case statements I'm going to stick to a case statement here but the concept is the same all right so I'm going to come back here all right sorry I wanted to pull up my code so that I don't have to think and type um so I'm going to select customer name let me for the sake of term I'm just going to copy and paste this one here all right so I'm going to select customer name customer ID order ID from customers join it on orders on the common keys that we've talked about now we know that we don't want to see the order ID what we do want is the count of order so I'm going to wrap this and count here pause it here turn it out I'm going to put a count the more appropriate thing if you would if you were let's say like at a level below order ID but you want to count distinct orders as we would say count the distinct orders right because our table is at the order level we really the count and the count distance are going to give us the same um this the same answer most of the time I defer to our account distinct so I don't want to put that out there um and so just let me show you guys what this looks like um and it's ambiguous because I see it's in both tables we just talked about that and I didn't group I thought I copied and pasted this group by one and two because count is an aggregation so this should give me my results okay perfect all right so but remember I don't really want to see their order count I want to call them a new customer if they don't have uh any orders or if they only have one order but if they have more than one order I'm going to call them existing customers and so what I'm going to do here is I'm going to uh wrap this in in a kind of case statement and so I'm going to say um case when this count is greater than one then call them existing and because I'm just using wave two conditions otherwise call them Noob so I don't have to rewrite that um and I'm going to close it out with an end and this is going to give me labels on these order counts you it might help if you guys can wrap this in parentheses just to kind of like keep the uh the function within itself and let's see all right so everybody that had uh more than one order is existing otherwise they are new customers any questions here um I'm not seeing any explicitly for this section but um uh I I think we should continue to the end because we are at the 60 Minute Mark we are at the end thank you your resources will are have already been linked but they are in the slide deck as well and then make sure to connect with me because I'm so excited about kind of tuning in with you guys and uh going along this journey with y'all okay thank you so much Selma we do have extra questions so if you don't mind sticking around for an extra uh maybe 10 minutes we'll try to get through as many as we can um uh so let's go ahead and do that um all right let's get to the bottom uh will you share the recording of This webinar yes um uh Fatima if you could share uh maybe the link to where the recording will be um uh you can have that as well um uh can D Beaver allow you to drill into records that make up a row in the result set and deeper sorry I was reading the Q a okay I'm scrolled all the way to the bottom if uh if that helps you um and I'll repeat the question please yeah can D Beaver allow you to drill into the records that make up a row in the result set yeah you could do that with like a sort of like a true with an index you could do that okay you could also do that by the way just like when we think about drilling into a result site you would do you would do that with a where clause and you don't need to think about like the the position of the row inside of the data set you could think about like the conditions that make that grow up so if it was a specific order ID a specific customer right to like really drill into the data set you would do that with the word clause okay and um uh before I say this question if somebody in the chat has a response to this as well would love to hear your answers but this person says hello sorry I work with geospatial data and I have heard SQL is almost necessary for geographic information systems field do you consider this true or or have um have you heard about it thanks so I'm not sure if you'll be able to answer that question but if somebody in the chat can that be great it's almost necessary for geographic information systems field I'm not sure what they're asking there um I have so like in a past life I worked in the public Consulting sector and we used geospatial data but we would use like GIS and they would not be in relational databases so we couldn't query it with SQL um but then there's also the flip side of like with customers where we had region and customer addresses and different Geographic fields that we could query so yeah so see SQL positive SQL is for relational data FDI has relational data perhaps so like if it has coordinates if it has um uh like if a tables have coordinates or or uh geographical information certainly you can query it you can build out a data set with that geographical information and then if you are leveraging some VI tool or uh or any kind of package like within python or anything like that you can definitely map it yes okay um how to do this is from Paul so how to do a default for case win statement like if none matches use certain value you can just do that with the else statement right Paul aren't you like the the sequel expert are you testing me so yeah I would just use that else statement right where I would so uh I would specify my conditions um so like we had if when the customer order when the customer count was greater than one we would call them existing customers if they were you know equal to one we would call them new customers otherwise we would call them like I don't know random customers right so like the else statement and your case statement or or the Alice clause in your case statement is how you would do that okay um uh let's go through we answered this one um so many questions uh uh can you explain aliases again there were uh quite a few uh questions at the start about this and I skipped them because we we needed to go further but uh can you just go through the concept of aliases one more time yes so think about aliases as you're just renaming your columns or you're renaming the way that you're referring to those tables that's really all we're doing um let me see if I have something over here all right so if I didn't Alias well first the problem if I didn't Alias customer name which I didn't or I didn't I didn't Alias my order my tables uh I would get an error if we remember right let's see I have a c somewhere um because the customer ID is is present in in both tables so it's ambiguous so that's the first kind of reason why aliasing is important is it allows you to refer to the um the table well actually it's not in this case we don't have to Alias it but it allows you to kind of create these like shortcuts right for for referencing your tables I could say customers Dot customer name but if I Alias it to C it's much easier to refer two the second part of it is let me just quickly do this just so I can give you guys another glimpse of where aliasing is especially important again aliasing is really just renaming uh the the fields that you're pulling let me call this um [Music] here all right so let me run this for you all right look at this third column it just says case um if I were to connect to this in like Tableau and power bi or if I were to provide this to my team they wouldn't they wouldn't get a sense of what this last column really is right um so if you're in Excel what you would do is you just rename that column header and you would call it as like you would rename it something like customer status and so that's what I'm doing here by aliasing it right after I I specify the function again you can use the keyword as I'm going to give this column name uh something meaningful um uh so Selma I think um I think we can end there we've already gone quite a few minutes over time thank you um you know this wouldn't be we wouldn't be able to do this without you so big thank you and really appreciate taking a little more than an hour out of your day to do this and then