Welcome back the Data Insight Specialization. I'm Evan, a data enthusiast inside of Google Cloud. I'm not going to hide the fact that this is my favorite course inside this specialization. Here we get to dive deep into advanced SQL, and BigQuery scheme of concepts. We'll use demos and hands-on labs, that allow us to unpack some of the most advanced insights that we've seen yet. First up, we'll revisit SQL functions, this time with an focus on analytical window functions, statistical functions and approximation functions. Now while we don't exhaustively cover every single SQL function that is out there, we do spend some time breaking down some real world queries that use these functions so that you can understand when to use them and why they are used. Along with functions, we'll also cover one of the most effective ways of breaking down difficult data questions by using subqueries or common table expressions, also called the WITH clause. I use these everyday, and we'll demo how you can break apart a complex query task into easier building blocks. Next we'll explore what makes BigQuery an analytic state warehouse and not a traditional relational or operational database. We'll dive deep into the technical components of how BigQuery works as a service and how you can best optimize your queries for speed. After understanding the inner workings of BigQuery, we'll unpack my favorite topic, which is how you can query and create a nested JSON like schema by using arrays and structs. All inside of one single table that has nested parent-child relationships for the fields as opposed to joining together many different tables for the same result. This is one of the most critical takeaways from this course, and we'll continue to use our real world Google analytics dataset. So everything you learn here can be directly applied outside of this course. Lastly, we'll close with how you can provision an access manager dataset through Google Cloud Platform and with BigQuery, as we walk through some common data access scenarios. Now that's a lot to cover, so let's dive right in to advanced SQL functions.
