Introduction to Data Analytics for Business Coursera Quiz Answers

Introduction to Data Analytics for Business – Coursera

4.7 Stars (2,037 ratings)

Instructor: David Torgerson

Enroll Now

N.B. We endeavored our best to keep this site invigorated for our customers in vain. You can similarly contribute by reviving new requests or existing request answer(s). There are various requests on our site, it is hard for us to check them reliably. It will be exceptional if you can help us with refreshing the site. Simply let us know whether you locate any new inquiries through mail or remark . We will endeavor to invigorate the request/answer ASAP.

Use “Ctrl+F” To Find Any Questions Answer. & For “Mobile User” You Just Need To Click On Three dots In Your Browser & You Will Get A “Find” Option There. Utilize These Option to Get Any Random Questions Answer.

Week-1 Quiz Answer

Click Here To View Answers

Week-2 Quiz Answer

Click Here To View Answers

Week-3 Quiz Answer

1.In this quiz, you’ll be writing queries based on the following database. Follow this link to access a larger picture of the database.

Note: your SQL code entries will not be saved between quiz attempts! Please copy paste them somewhere so you don’t have to retype the entire code when you take the quiz again.

How many aircrafts are there in the PLANES table?

Input

SELECT

count(aircraft_type) from planes

Output

+———————-+

| count(aircraft_type) |

+———————-+

| 25 |

+———————-+

Refer to the following video if you need a refresher: video 1.

25
50
562
10961

2. Write a query that provides a list of all planes that have a seat count of 100 or more, ordered from lowest to highest number of seats.

What is the TAIL_NUMBER of the plane with the second lowest number of seats in that list?

Input

SELECT *

FROM PLANES

WHERE SEAT_COUNT >= 100

ORDER BY SEAT_COUNT

Output

+————-+———+—————-+————+————+

+————-+———+—————-+————+————+

| N132AA | SKY | A319-114 | 319-100 | 120 |

| N120EE | SKY | A319-132 | 319-100 | 124 |

| N117BB | SKY | A319-112 | 319-100 | 128 |

| N118CC | ALN | A319-115 | 319-100 | 128 |

| N119DD | GLB | A319-115 WL | 319-100 | 128 |

| N127AA | ALN | DC-9-82(MD-82) | MD-82 | 140 |

| N128BB | GLB | DC-9-83(MD-83) | MD-83 | 140 |

| N111AA | SKY | 737-823 | 737-800 | 150 |

| N112BB | ALN | 737-823 WL | 737-800 | 150 |

| N113CC | GLB | 737-832 | 737-800 | 150 |

| N121AA | ALN | A320-214 | 320-200 | 150 |

| N122BB | GLB | A320-231 | 320-200 | 150 |

| N123CC | SKY | A320-232 | 320-200 | 150 |

| N115EE | ALN | 767-223 | 767-200 | 168 |

| N116AA | GLB | 767-223 ER | 767-200 | 168 |

| N124DD | ALN | A321-211 | 321-200 | 187 |

| N125EE | GLB | A321-231 | 321-200 | 187 |

| N114DD | SKY | 757-223 | 757-200 | 188 |

+————-+———+—————-+————+————+

Refer to the following video if you need a refresher: video 2.

N114DD
N132AA
N120EE
N126AA

3. Write a query that provides the number of flights flown by each aircraft.

Which aircraft flew the most flights?

Input

SELECT TAIL_NUMBER, COUNT(*) AS NUM_FLIGHTS

FROM FLIGHTS

GROUP BY TAIL_NUMBER

ORDER BY NUM_FLIGHTS DESC

Output

+————-+————-+

| TAIL_NUMBER | NUM_FLIGHTS |

+————-+————-+

| N111AA | 5 |

| N116AA | 4 |

| N118CC | 4 |

| N122BB | 4 |

| N125EE | 4 |

| N119DD | 3 |

| N124DD | 3 |

| N132AA | 3 |

| N134CC | 3 |

| N117BB | 2 |

| N129CC | 2 |

| N130DD | 2 |

| N135DD | 2 |

| N112BB | 1 |

| N113CC | 1 |

| N115EE | 1 |

| N120EE | 1 |

| N123CC | 1 |

| N126AA | 1 |

| N127AA | 1 |

| N128BB | 1 |

| N133BB | 1 |

+————-+————-+

Refer to the following video if you need a refresher: video 2

4. Write a query that provides a list of planes that flew more than 600 passengers across all flights.

How many planes are in that list?

Refer to the following video if you need a refresher: video 2.

Input

SELECT TAIL_NUMBER, SUM(PASSENGER_COUNT) AS TOT_PASSENGERS

FROM FLIGHTS

GROUP BY TAIL_NUMBER

HAVING TOT_PASSENGERS > 600

Output

+————-+—————-+

| TAIL_NUMBER | TOT_PASSENGERS |

+————-+—————-+

| N111AA | 675 |

| N116AA | 608 |

| N125EE | 659 |

+————-+—————-+

5. Write a query that provides the total number of flights by country.

How many flights originated in the United States (Country US)?

Refer to the following video if you need a refresher: video 3.

Input

SELECT COUNTRY, COUNT(*) AS NUM_FLIGHTS

FROM FLIGHTS a

LEFT JOIN AIRPORTS b

ON a.DEPARTURE_AIRPORT = b.AIRPORT

Output

+———+————-+

| COUNTRY | NUM_FLIGHTS |

+———+————-+

| IT | 50 |

+———+————-+

6. Write a query that provides the total number of flights by regionality.

Which regionality has the second highest number of flights?

Refer to the following video if you need a refresher: video 3.

Input

SELECT REGIONALITY, COUNT(*) AS NUM_FLIGHTS

FROM FLIGHTS a

LEFT JOIN CITY_PAIRS b

ON a.DEPARTURE_AIRPORT = b.DEPARTURE_AIRPORT AND

a.ARRIVAL_AIRPORT = b.ARRIVAL_AIRPORT

GROUP BY REGIONALITY

Output

+————-+————-+

| REGIONALITY | NUM_FLIGHTS |

+————-+————-+

| NA-NA | 1 |

| NA-ROW | 1 |

| NA-US | 1 |

| ROW-ROW | 10 |

| ROW-US | 3 |

| US-NA | 1 |

| US-ROW | 4 |

| US-US | 29 |

+————-+————-+

7. How many CITY_PAIRS are there which depart from one of the following airports?

KLAX, KDEN, KORD, KDET, KLGA

Hint: use the IN operator!

Refer to the following video if you need a refresher: video 5.

Input

SELECT COUNT(*)

FROM CITY_PAIRS

WHERE DEPARTURE_AIRPORT IN (‘KLAX’,’KDEN’,’KORD’,’KDET’,’KLGA’)

Output

+———-+

| COUNT(*) |

+———-+

| 722 |

+———-+

8. How many airports are missing elevation values?

Refer to the following video if you need a refresher: video 5.

Input

SELECT COUNT(*)

FROM AIRPORTS

WHERE ELEVATION IS NULL

Output

+———-+

| COUNT(*) |

+———-+

| 6 |

+———-+

9. What flight number had the lowest passenger count (try using a subquery if you can!)?

Refer to the following video if you need a refresher: video 6.

Input

SELECT FLIGHT_NUMBER

FROM FLIGHTS

WHERE PASSENGER_COUNT IN

(SELECT MIN(PASSENGER_COUNT) FROM FLIGHTS)

Output

+—————+

| FLIGHT_NUMBER |

+—————+

| ALN626 |

+—————+

10. What is the average distance flown by SKY Airline flights (nearest mile)?

Input

SELECT AVG (DISTANCE) AS AVG_DISTANCE

FROM FLIGHTS a

LEFT JOIN PLANES b

ON a.TAIL_NUMBER = b.TAIL_NUMBER

LEFT JOIN CITY_PAIRS c

ON a.DEPARTURE_AIRPORT = c.DEPARTURE_AIRPORT AND

a.ARRIVAL_AIRPORT = c.ARRIVAL_AIRPORT

WHERE AIRLINE = ‘SKY’

Output

+—————+

| AVG_DISTANCE |

+—————+

| 1767.96389687 |

+—————+

Refer to the following video if you need a refresher: video 6.

Week-4 Quiz Answers

1. In the next 6 questions, indicate which specialist you’d seek out to help with each of the scenarios listed.

Refer to video 1 if you need a refresher!

You’ve just completed an analysis that reveals the importance of a few metrics that business leaders would like to see monthly. You need someone to help you productionalize and automate a monthly report containing those metrics.

Who should you talk to?

2. You’ve arranged for an external partner to send you data each day. You need someone to help set up a file transfer process that will allow that partner to securely connect to your company through a firewall.

Who should you talk to?

3. To ensure that the results of a data analysis can be placed into context, you need someone who can examine how certain business processes work and help you map them out.

Who should you talk to?

4. You’ve done a descriptive analysis that seems to show a correlation between customer defection and several customer characteristics, but you think that a formal statistical procedure would yield more powerful results that can predict churn. You need someone who knows how to do this.

Who should you talk to?

5. You know that a new product is coming online and you’d like to understand how measurements around that product will be represented in the database model.

Who should you talk to?

6. You’re finding that the SQL queries you are writing against your data warehouse are taking a long time to run. You need someone who can help you determine if your queries are written in the best way. Who should you talk to?

7. Consider the following structure for analytic organizations. Pick which structure would fit best in each scenario.

Refer to video 2 if you need a refresher!

Your company is functionally organized. The data sources and analytical techniques tend to be pretty similar across functions, and most resources are located in a headquarters building in downtown Chicago. The executive team gets along, but are very protective of their teams and work product.

8. Your company is a multinational organization that operates in a number of distinct industries. Each industry uses its own methods and tends to hire somewhat different types of people into analytical organizations.

Which structure would best fit this scenario?

9. Your company is organized by customer groups, which are mostly distinct but have some limited overlaps. Analyses vary in similarity – some are very similar, but others are quite different. They do, however use most of the same data sources. Currently resources are located within each customer group organization, but it’s pretty typical for there to be only one or two analysts in each area.

Which structure would best fit this scenario (mark all that apply)?

10. What term best describes the process of identifying and standardizing an organization’s most critical data?

Refer to the following video if you need a refresher: video 3.

11. Who is responsible for making sure that a data domain is correctly represented and used within an organization?

Refer to the following video if you need a refresher: video 3.

12. A large drugstore chain wants to use prescription data from its pharmacy to make complementary relevant offers to specific customers via custom coupon books, delivered via direct mail.

What is the most limiting standard that might be relevant in this case?

Refer to the following video if you need a refresher: video 4.

13. A Mobile Phone Company wants to construct and sell ‘profiles’ of customers based on a combination of internet sites visited and location data. The profiles would provide aggregate information that is not considered CPNI.

What are the most limiting standards that might be relevant in this case? (select all that apply)

Refer to the following video if you need a refresher: video 4.

14. Consider the following aspects of Data Quality. Indicate which is the dominant issue in each case below.

Refer to video 5 if you need a refresher!

A few years ago your company acquired another company and merged summary financial data into a key database. The data looks complete but there are some peculiarities we can’t explain.

15. Your company wants a mobile application that allows certain purchases to be made via the application. However, those transaction use the date / time of the user’s device as the timestamp of the transaction that is stored in the purchase database.

Provenance

16.We notice that when we join data from two different tables we need to be careful to convert the time zone in one table from Central Standard Time ( CST ) to Universal Coordinated Time ( UTC ) to match the second table, even though the company standard it UTC.

Provenance

17. On your company’s website, a customer can accidentally click a purchase button twice, which results in two purchase records being generated. Luckily, these purchases are filtered by the credit card payment processing system and removed from the company’s general ledger. However, those records are not removed from the analytical data warehouse.

Peer-graded Assignment: Final Course Assignment

Conceptual Business Model solution:

PROMPT

Part 1: Conceptual business model. Construct a conceptual business model for an industry or business that you are familiar with or have interest in. Visually it should be similar to the one we illustrated in Module 1, Video 2.

Your model should represent at least 10 ideas
It should visually represent one to one, many to one, or many to many relationships among ideas

Answer:

Conceptual Business Model solution.pdf Download

PROMPT

Part 2: Relational data model. Take a subset of the ideas from the conceptual model you constructed in Part 1 and design a simple relationship model similar to the ones we discussed in Module 2, Video 4

Your model should have at least 5 tables
You should include at least 20 attributes, or fields, in your model (20 total across all tables, not per table)
Your model should be normalized
Identify the primary key in each table, and state whether it is a natural or surrogate key
For each relationship between tables, identify any foreign keys needed to define the relationship
For each table, identify what type of system or systems you think the data might come from, like those we discussed in Module 1, Video 6.

Answer:

Relational Data Model.pdf Download

PROMPT

Part 3: SQL queries. Using the data model you constructed in Part 2, come up with two data extracts you think would be interesting, then write SQL queries to provide each one.

For each query, state what data you are trying to get and why it would be interesting
Provide the SQL query using the commands and syntax we learned in Module 3
For maximum credit, at least one of your queries should involve a join across two or more tables.

– Query to find the distribution of services used by customers in different regions. It is interesting because this info could lead to changes in service offerings in some regions if any of the services are found to be severely underused in those regions , and more business can be generated as the result.

Answer:

SELECT r.reg_name

, p.service_id

, COUNT(p.purch_id) as cnt

FROM Customers c

LEFT JOIN Purchases p

ON c.cust_id = p.cust_id LEFT JOIN Regions r

ON c.region_id = r.region_id

GROUP BY r.reg_name, p.service_id

ORDER BY cnt;

— Query to find out year-to-date revenue by customer for service

#2. It is interesting because this info can be used for a targeted marketing

campaign to generate more business from the customers currently under-using service #2.

SELECT t.cust_id

, SUM(t.amount) as revenue

FROM Transactions t

LEFT JOIN Purchases p

ON t.purch_id = p.purch_id

WHERE p.purch_date BETWEEN ‘2017-01-01’ AND DATE.NOW ()

AND p.service_id = 2

GROUP BY t.cust_id

ORDER BY revenue;

PROMPT

Part 4 : Sensitive data and data quality issues. Consider the data privacy and data quality implications of the data model you constructed in Part 2

Identify any fields you think might be PII, CFI, CPNI, or PHI as we defined in Module 4, Video 4
What data elements in your model will present the most significant data quality challenges? Explain your reasoning.

Answer:

CREATE TABLE customers (

cust_id INT PRIMARY KEY AUTO_INCREMENT

, last_name VARCHAR(30)

, first_name VARCHAR(30)

, region_id FOREIGN KEY

);

CREATE TABLE purchases (

purch_id INT PRIMARY KEY AUTO_INCREMENT

, purch_date DATE

, num_items INT

, service_id FOREIGN KEY

, cust_id FOREIGN KEY

, seller

, status_id FOREIGN KEY

, transaction_id FOREIGN KEY

, package_id FOREIGN KEY

);

CREATE TABLE items (

item_id INT PRIMARY KEY

, purch_id FOREIGN KEY

, cust_id FOREIGN KEY

);

CREATE TABLE package (

package_id

, item_id

, carrier

, PRIMARY KEY (package_id, item_id)

);

CREATE TABLE transactions (

transaction_id PRIMARY KEY

, t_date DATE

, type

, amount

, cust_id

, payment_method

, purch_id

);

The above questions are from “Introduction to Data Analytics for Business “. You can discover all the refreshed questions and answers related to this on the “Introduction to Data Analytics for Business By Coursera” page. If you find the updated questions or answers, do comment on this page and let us know. We will update the answers as soon as possible.