Overview

Google Standard SQL for BigQuery supports sub-queries. A subquery is a query that appears inside another query statement. Sub-queries are also referred to as sub-SELECTs or nested SELECTs. The full SELECT syntax is valid in sub-queries. This lab allows you to practice writing sub-queries.

Objectives

In this lab you learn how to perform the following tasks:

• Use partitions and other analytic functions to perform calculations over a subset of your data.
• Consider sub-queries to break up complex logic into multiple steps.

Setup and requirements

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Task 1. Practicing with sub-queries

1. Click on Navigation menu > BigQuery.

2. Click DONE.

3. Write the below query in the query EDITOR.

4. Using the provided query WITH clause below, write a SELECT statement that follows which calculates the avg_income (revenue - expenses) and sort the results by avg_income highest to lowest.

Also include year_filed and the other metrics from the WITH clause in your result:

#standardSQL WITH summary AS ( # count of filings, revenue, expenses since 2013 SELECT CONCAT("20",_TABLE_SUFFIX) AS year_filed, COUNT(ein) AS nonprofit_count, AVG(totrevenue) AS avg_revenue, AVG(totfuncexpns) AS avg_expenses FROM `bigquery-public-data.irs_990.irs_990_20*` WHERE _TABLE_SUFFIX >= '13' GROUP BY year_filed ORDER BY year_filed DESC ) # write your code here

5. Compare your result against the solution below:

#standardSQL WITH summary AS ( # count of filings, revenue, expenses since 2013 SELECT CONCAT("20",_TABLE_SUFFIX) AS year_filed, COUNT(ein) AS nonprofit_count, AVG(totrevenue) AS avg_revenue, AVG(totfuncexpns) AS avg_expenses FROM `bigquery-public-data.irs_990.irs_990_20*` WHERE _TABLE_SUFFIX >= '13' GROUP BY year_filed ORDER BY year_filed DESC ) SELECT year_filed, nonprofit_count, avg_revenue, avg_expenses, avg_revenue - avg_expenses AS avg_income FROM summary ORDER BY avg_income DESC

6. Click RUN.

Congratulations!

You have completed the Advanced SQL lab.

Learning review

• Use partitions and other analytic functions to perform calculations over a subset of your data
• Consider sub-queries to break up complex logic into multiple steps

References

• Standard SQL Analytic Functions Guide

End your lab

When you have completed your lab, click End Lab. Google Cloud Skills Boost removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.