Data ImpressionsRead through the entire sidebar before you beginthis Project!In this project, you will perform exploratory data analysis (EDA) on real data. You will use a wide range of MySQL keywords...

Read through the entire sidebar before you beginthis Project!

In this project, you will perform exploratory data analysis (EDA) on real data. You will use a wide range of MySQL keywords including WHERE, GROUP BY, and JOIN to write queries that answer questions and uncover key insights.

While completing your project,stop every now and then to save your work.This will ensurethat your work won't be lost.

You can find the LetsMeet data in your own schema. When you lookat the navigator on the left side of MySQL Workbench, your schema should bebold. If it's not bold, double-click on your schema to activate it.

Open theLetsMeet slide deckif you want to take a look at it again before you begin your tasks.

Note that you will submitall of the code you write within the SQL platform to be graded.

Key insights will be a significant componentof thisproject part. Akey insight is acritical piece of information that helps you understand the bigger picture of your data.

In this part of the project you will be delivering two types of key insights.

1. The first will be insights based on specific questions asked by the sales team.
2. The second will be insights that you develop on your own by exploring the data.

To start, you will be providing insights based on specific questions. For example, let's say the marketing team wantsto know if there areareas outside of North America that they should target with their marketing.

This seems related to where the LetsMeet members are from! So you might start exploring this byrunning aSELECT * FROM grp_member(where most of the member information is stored). You know there is a column namedhometownthat shows members hometowns, including international cities.

Since you want to know how many people come from different hometowns.You canrun the following query:

SELECT 
    hometown, 
    COUNT(*) 
FROM grp_member
GROUP BY hometown
ORDER BY COUNT(*) DESC;

You remember that LetsMeet operates in New York, San Francisco, and Chicago, so the top of the list is no surprise. But if you scroll down far enough, you willstart to see hometowns from cities in other countries.

A key insight may be summarized as follows:

“LetsMeetmembers come from all over the world. Most of international members come fromLondon, Paris, and Hong Kong. The number of members who's hometowns are outside of North America is relativelysmall compared to the number of members who come from North American cities like New York, Chicago and San Francisco"

Key insights do not have to be staggering. They just have to be useful.

You will need to write your insights within comments on the SQL platform.

To write comments across multiple lines, write /* to open a comment. Any text you write from this point on will be commented and not executedwhen code is run.

Also, be sure to click the Wrap Textbutton in the top toolbar (shown below) before you writeinsights. That way, all of your comments will stay within the query window and be visible to your instructor.

In the1_sales_insights.sqlfile, you will write a set of queries that provide answers to specific questions asked by the sales team. You will be providing insights to them based on the results of your queries.

You will report your insights back to the sales team by copying the question into a commentandanswering the questionin full sentences within the comments.

The following code snippet shows an example of a query, followed by a question and insights.

SELECT
    AVG(duration) / 60,
    MIN(duration) / 60,
    MAX(duration) / 60
FROM event;

/*What is the average event duration in minutes? 
What are the min and max durations in minutes?

The average event duration is 179.5
This equates to roughly 3 hours.
The min event duration is 45 minutes.
The max event duration is 20160 minutes.
This is roughly 14 days. This is likely an error or an ongoing event*/

Notice that the comment answers the question directly, but italso gives additional useful information.

You should think aboutwhythe sales team isanswering these questions, and provide additional useful information as you come across it.

You will find the questions the Sales team is interested in answering categorized below. In thesales_insights.sqlfile, you will find the same categories indicating where you should write your queries for these questions.

ACTIVE CITIES

1. What cities have active members?
2. Are there any cities listed in the citytable with no active members?If so, what state are those cities in?

GROUPS

1. How many groups are currently open, waiting for approval, and/or closed?

CATEGORIES

1. What are the five categories that contain the most groups? What are the five categories that contain the least number of groups?

MEMBERS

1. How many members are there?
2. What percentage of members are in Chicago?

Now that you've discovered some key insights based on pre-defined questions, it's time to provide some additional insights of your own!

To obtain key insights fora given table, it's always good to start by start by runningSELECT * FROM table_nameandDESCRIBE table_name.

You should review the data,looking for interesting patterns.Then you will develop questions or hypothesesand write queries to test them.

Don't forget, key insights don't have to be staggering. They just have to be useful.

The data we are providing is not perfect. There is no need to correct any data, but pointing out concerns or general data issues you see in specific columns can be part of your insights.

Your goal is to find at least one key insight per table.

Your insights should be written as comments in your SQL file as complete sentences.

Submit your SQL code along with the insight in the following format. A single line comment referencing the table, followed by your query and a multiline comment containing your key insight.

-- grp_member

SELECT
    hometown,
    COUNT(*) ct
FROM grp_member
GROUP BY hometown
ORDER BY ct DESC;

/*LetsMeetmembers come from all over the world. 
Most of the international members come fromLondon, Paris, and Hong Kong. 
The number of members who's hometown is outside of North America 
is relativelysmall compared to the number of members who come from 
North American cities like New York, Chicago, and San Francisco*/

Insights and queries that are related to the same table should be grouped together.

You should write your additional insights in the SQL file named2_additional_insights.sql.

In the2_additional_insights.sqlfile you will find and report at least one insight for each of six tables in the LetsMeet database. These tables include:

• venue_
• grp
• grp_member
• city
• event
• category

Explore the data using the SQL queries you learned thus far. Here is the ERD diagram that Quinn has provided for your review.

To get you started, here are a few guiding questions for each table below.

VENUE_

• What is the RAND() column in this table? Does it have any relation to the venues?
• Which city has the most venues?
• Which zip code has the most venues? What neighborhood is this?

GRP

• What is the longest running group on LetsMeet? How many members do they have?

• What does public_limited mean? How many groups are public_limited?
• You notice the who column. What values come up most frequently in the who column?

GRP_MEMBER

• Who was the first LetsMeet member that is still active? What member is a part of the most groups?
• Can you use this table to determine the total number of members? Why or why not?

CITY

• What is the ranking column? Is it providing any additional useful information about cities?

EVENT

• What is the average duration of all events? In minutes? Hours?
• Which venue is the most popular and how many events were held there?

CATEGORY

• How can you combine the category data with other data to determine the most popular categories?

You can use these questions as a guide, butbe sure to include your own insights for each table.

The following snippet is a reminder of how the queries and insights in youradditional_insights.sqlfile should be formatted.

-- grp_member

SELECT
    hometown,
    COUNT(*) ct
FROM grp_member
GROUP BY hometown
ORDER BY ct DESC;

/*LetsMeetmembers come from all over the world. 
Most of the international members come fromLondon, Paris, and Hong Kong. 
The number of members who's hometown is outside of North America 
is relativelysmall compared to the number of members who come from 
North American cities like New York, Chicago, and San Francisco*/

Congratulations on making it through Part 1 of your project!

You should have two sql files to submit:

1. sales_insights.sql
2. additional_insights.sql

Before submitting your project, review your queries and insights for the following:

• proper use of indentation and capitalization
• proper formatting of multi-line comments
• queries and insights insales_insights.sqlare grouped by the following categories: active cities, groups, categories and members.
• insights inadditional_insights.sqlthat arerelated to a specific table identify which table they are for.

Remember to save your work.

Now that yousaved your work, when you're ready, click Submitto submit your SQL query script.

Note: Whatever work yousaved in MySQL Workbench will be sent to your instructor after you submit. Any changes you make after you press Submitwill not be visible to your instructor.