Okay now the really fun part. Let's think about how we're going to convert those amazing questions that we just came up with into some best practice SQL. Which is a little bit more rigid than just asking these questions kind of willy-nilly. All right so the first thing, as we mentioned previously, you want to enable that standard SQL as part of your code, again it's faster, it's more standards compliant. And if you need a reference in case you've inherited old code that was written in legacy SQL, there's that reference available to you there. Which also includes a migration for which functions now have these new names in standard SQL. And an additional key point that we want to highlight is, when you're running multiple queries, or say you have a long script because you're just exploring and you want to keep everything all within one document, you can highlight a portion of your code and then execute selected, or run selected in just that piece by clicking on that down arrow and then running that selected. Another key difference, and this is what you need to keep in mind when you're pulling data from one of these tables is, you need to escape the table name. And what that means is you need to actually enclose not just the table name but all the prefixes where it comes from, right. So your table is stored within a dataset, like the IRS 990 dataset, and then stored within a project, in this particular case, the public BigQuery project. So you need to actually have project.dataset.table, so a common error here is say, just having just the table. So if you had just the table, BigQuery wouldn't know which table is part of which dataset. Maybe 20 people that are taking this class already just created a table with that name. Whose product am I querying from, because you can actually query data across multiple people's projects. So of course naturally you want to actually get to some table that can actually be resolved to a specific BigQuery table in the backend. All right, so let's take a look at this query. So this query says, give me, select means give me. Give me the columns that follow the select statement, in this particular case top revenue or total revenue. Give me the total revenue from this particular dataset. Okay, so keep in mind what we're actually going to be returning here. So total revenue, that's not going to be the total revenue for all the filings in 2015. Total revenue is a field, so in actuality what you're going to get back is your going to get back just the total revenue for every single one of those records, a million of the filings for 2015. Okay, so maybe we can limit the results to ten so we're not just returning a bunch of data that we have to paginate our way through. But is this useful, especially if you have just the column for total revenue? Not really, I mean you can see, you can get a general feel that there's a lot of potentially non-zero revenue there. But we don't know who it's from, and I mean in this particular case, it's not even ordered either, so maybe we could potentially add an order by clause. All right, now we're getting somewhere, right? So now we have revenue, total revenue for 2015 filings, for those exemption filings. Now we're going to order it, and we're going to order it from highest to lowest, and we do that by adding that order by clause on, near the end, second to last in the query. And we're sorting it from highest to lowest, and how you actually denote that in SQL is descending or DESC is one of the parameters for that value there. It defaults the order by clause defaults to ascending or ASC, which would be the opposite, and it works the same way with alphabetically, A to Z or Z to A. Okay, so a key point here is whenever your ordering something, especially if you have more then 50 rows, make sure you limit it. because there's no reason to return back all that data in those paginated results, your just wasting your bytes processed, your amount of data that you're consuming.
