So this is going to be a little bit of a review since we covered the common table expressions which is that WITH clause. But I'm just going to show you just another example of a good code hygiene. So here's the example and we're going to talk through this. So you saw us break apart our complex query into multiple pieces. So on the left there, we're just going to walk through those temporary tables that we're creating by invoking that WITH clause. And try to organize it as neatly as I can. Starting from the top, right? So use line three as WITH and we're saying the irs_990_2015_ein and includes all look up details of that table because we're going to join on line 11 there is the organizational details. So it's giving me all records from both the 2015 table and the organization to look up to. All right. And we don't want to do that again later on in the code. So after that, we have that table. We've identified duplicates in that second temporary table, line 15, as things that have more than one count or instance of the ein occurring in the 2015 filing table. Again, keeping in mind here if you're a charity filing for the 2015 calendar year, normally, you'll have just one tax exemption form the 990. So you want to fill throughout the rest. And then the last line is line 27 through 40, is where we pick and choose what fields we want from the 2015 table that we've joined previously. And then we join against the duplicates to basically say, "Hey, if you're in this duplicate spot, we'd pretty much want to just filter you out." So, it'll be flagged as a duplicate and then line 40 wherever the duplicate is not flagged, the duplicates.ein, we're actually going to say, include all those non-duplicated records. Okay, but the key takeaway here is you can chain multiple temporary tables together. And what I'll do constantly is I'll look at something like this and say, "All right, the query works and it's well-written, but then I'll take something like line six through 12 and I'll store that as a permanent table after I've written this massive query and gotten the results that I want." So then, you don't have to continuously invoke these temporary tables, right? This is mainly so you can just give your brain some breathing room and breaking apart a very complex query into its subcomponent parts. I highly encourage you to experiment and play with the WITH clause and you'll get a chance to in your next lab. All right. Let's recap. So we finished covering SQL functions, which included some pretty neat ones that allow you to statistically estimate with great accuracy across huge data sets. Again, it's your option here whether or not you want to trade processing time for 100 percent accuracy. Next, we covered an example. We wanted to break apart a single table into subgroups of rows that we wanted to perform a ranking calculation on each of those subgroups. We did this using analytical Window function. After that, we introduced UDFs, or user-defined functions, which can be written in either SQL or JavaScript. Keep in mind the caveat here that query performance is impacted. Lastly, and probably what I use the most, is make liberal use of that WITH clause, which allow you to break apart a very complex SQL question into multiple steps. Now let's practice that in our next lab.
