5.4.4.Weekly challenge 4 - sj50179/Google-Data-Analytics-Professional-Certificate GitHub Wiki
Weekly challenge 4
LATEST SUBMISSION GRADE 100%
Question 1
A data analyst is working with a spreadsheet from a furniture company. To use the template for this spreadsheet, click the link below and select “Use Template.”
Link to template: Sample Transaction Table.
The analyst inputs a function to find the number of transactions that include a brass-colored product. The syntax of which of the following formulas would return that result?
- =COUNTIF(F2:F30, “brass”)
=SUMIF(F2:F30, “brass only”)=COUNTIF(E2:E30, “=brass”)=SUMIF(F2:F30, “=brass”)
Correct. The COUNTIF formula =COUNTIF(F2:F30, “brass”) has a condition that would allow the analyst to count all product color values in Column F that are brass. The condition is “brass,” so this calculation would return a count of 5 because there are 5 values in Column F that are “brass.”
Question 2
You are working in a spreadsheet and uses the SUMIF function in the formula below as part of your analysis.
=SUMIF(A1:A25, ”<10”, C1:C25)
Which part of this formula is the criteria or condition?
A1:A25- ”<10”
=SUMIFC1:C25
Correct. The criteria or condition for this SUMIF formula is “<10”. This means that if any values in the range A1 through A25 are less than 10, their corresponding values in the range C1 through C25 will be added together.
Question 3
A data analyst is working with a spreadsheet from a retailer. To use the template for this spreadsheet, click the link below and select “Use Template.”
Link to template: Retail Sales Data
The analyst wants to figure out the value of all of the items in the spreadsheet. Which formula will calculate the total price of all of the items?
=SUMIFS(C2:C21,B2:B21,"1",A2:A21,"_20")- =SUMPRODUCT(B2:B21,C2:C21)
=SUMIF(B2:B21, “=1”)=SUM(C2:C21)
Correct. To calculate the value of all items in Column C, use the SUMPRODUCT function and the range C2:C21.
Question 4
You create a pivot table in a spreadsheet containing movie data. To use the template for this spreadsheet, click the link below and select “Use Template.”
Link to template: Movie Data Project.
In order to find out how much box office revenue each genre earned, you would use the SUM function in the Values menu to summarize the data.
- True
False
Correct. You would use the SUM function to figure out how much box office revenue each genre earned. In the pivot table, the SUM function would add the total revenue separately for each genre.
Question 5
A data analyst uses the following query to perform basic calculations on their data. The variables in the query have the following values: yes_responses = 10, no_responses = 12, total surveys = 22. What is the value of the Responses_Per_Survey variable?
SELECT
Yes_Responses,
No_Responses,
Total_Surveys,
(Yes_Responses + No_Responses) / Total_Surveys AS Responses_Per_Survey
FROM
Survey_1
- 1
224411
Correct. The value of the Responses_Per_Survey variable is 1. In this query, the analyst first calculates the sum of the “yes” and “no” responses, then divides the sum by the total surveys.
Question 6
You are working with a database table that contains data about music. The table includes columns for track_id, track_name, composer, and album_id. You are only interested in data about the classical musician Johann Sebastian Bach. You want to create new album IDs. You decide to multiply the current album IDs by 10 to create new album IDs, and use the AS command to store them in a new column called new_album_id.
Add a statement to your SQL query that calculates a new album Id for each track and stores it in a new column as new_album_id.
SELECT
track_id,
track_name,
composer,
album_id,
album_id * 10 AS new_album_id
FROM
track
WHERE
composer = "Johann Sebastian Bach"
+----------+----------------------------------------------------------------+-----------------------+----------+--------------+
| track_id | track_name | composer | album_id | new_album_id |
+----------+----------------------------------------------------------------+-----------------------+----------+--------------+
| 3407 | Concerto for 2 Violins in D Minor, BWV 1043: I. Vivace | Johann Sebastian Bach | 276 | 2760 |
| 3408 | Aria Mit 30 Veränderungen, BWV 988 "Goldberg Variations": Aria | Johann Sebastian Bach | 277 | 2770 |
| 3409 | Suite for Solo Cello No. 1 in G Major, BWV 1007: I. Prélude | Johann Sebastian Bach | 278 | 2780 |
| 3430 | Toccata and Fugue in D Minor, BWV 565: I. Toccata | Johann Sebastian Bach | 297 | 2970 |
| 3433 | Concerto No.2 in F Major, BWV1047, I. Allegro | Johann Sebastian Bach | 300 | 3000 |
| 3482 | Suite No. 3 in D, BWV 1068: III. Gavotte I & II | Johann Sebastian Bach | 327 | 3270 |
| 3490 | Partita in E Major, BWV 1006A: I. Prelude | Johann Sebastian Bach | 335 | 3350 |
+----------+----------------------------------------------------------------+-----------------------+----------+--------------+
What is the new album ID for the track with Id number 3490?
29702760- 3350
3000
Correct. You add the statement **album_id * 10 AS new_album_id to calculate a new album ID for each track and store it in a new column as new_album_id. The complete query is SELECT track_id, track_name, composer, album_id, album_id * 10 AS new_album_id FROM track WHERE composer = “Johann Sebastian Bach”. The AS command gives a temporary name to the new column.
The new Album Id for the track with Id number 3490 is 3350.
Question 7
You are working with a database table that contains invoice data. The table includes columns for customer_id and total (total amount billed for each invoice). Some customers have multiple invoices. You want to find out the total amount billed to each customer, and store the result in a new column as total_amount.
You write the SQL query below. Add a GROUP BY clause that will group the data by customer ID number.
SELECT
customer_id,
SUM(total) AS total_amount
FROM
invoice
GROUP BY customer_id
+-------------+--------------+
| customer_id | total_amount |
+-------------+--------------+
| 1 | 39.62 |
| 2 | 37.62 |
| 3 | 39.62 |
| 4 | 39.62 |
| 5 | 40.62 |
| 6 | 49.62 |
| 7 | 42.62 |
| 8 | 37.62 |
| 9 | 37.62 |
| 10 | 37.62 |
| 11 | 37.62 |
| 12 | 37.62 |
| 13 | 37.62 |
| 14 | 37.62 |
| 15 | 38.62 |
| 16 | 37.62 |
| 17 | 39.62 |
| 18 | 37.62 |
| 19 | 38.62 |
| 20 | 39.62 |
| 21 | 37.62 |
| 22 | 39.62 |
| 23 | 37.62 |
| 24 | 43.62 |
| 25 | 42.62 |
+-------------+--------------+
(Output limit exceeded, 25 of 59 total rows shown)
What is the total amount billed to the customer with Id number 5?
39.62- 40.62
37.6249.62
Correct. You add the clause **GROUP BY customer_id to group the data by customer Id number. The complete query is SELECT customer_id, SUM(total) AS total_amount FROM invoice GROUP BY customer_id. The GROUP BY command groups rows that have the same values from a table into summary rows. GROUP BY is always placed as the last command in a SELECT-FROM-WHERE query.
The total amount billed to the customer with Id number 5 is 40.62.
Question 8
You are working with a database table that contains invoice data. The table includes columns for billing_state, billing_country, and total. You want to know the average total price for the invoices billed to the state of Wisconsin. You decide to use the AVG function to find the average total, and use the AS command to store the result in a new column called average_total.
Add a statement to your SQL query that calculates the average total and stores it in a new column as average_total.
SELECT
billing_state,
billing_country,
AVG(total) AS average_total
FROM
invoice
WHERE
billing_state = "WI"
+---------------+-----------------+---------------+
| billing_state | billing_country | average_total |
+---------------+-----------------+---------------+
| WI | USA | 6.08 |
+---------------+-----------------+---------------+
What is the average total for Wisconsin?
5.785.37- 6.08
5.54
Correct. You add the statement AVG(total) AS average_total to calculate the average total and store it in a new column as average_total. The complete query is SELECT billing_state, billing_country, AVG(total) AS average_total FROM invoice WHERE billing_state = “WI”*. The AVG function is an aggregate function that returns the average value of a group of values. The AS command gives a temporary name to the new column.
The average total for Wisconsin is 6.08.*