Welcome to the SQL Analytics Project! This project is broken up into several parts throughout the course.

computer science

Description

Project Overview

Project, Part 1 – Data Impressions

Welcome to the SQL Analytics Project! This project is broken up into several parts throughout the course. Each new part of the project will give you a chance to practice what you learned so far in the course. This is an opportunity to synthesize the skills you learned by answering more open-ended questions than you've seen before.

You will also gain experience solving real-world business problems and start building a portfolio of work to demonstrate your skills to potential employers. By the end of the project, you will have written SQL scripts, answered direct stakeholder questions, authored reports, and improved the structure of a database. Don't worry if some of this seems overwhelming at this point; each section of the project will only require the skills you learned up to that point. 

Project Part 1 Learning Objectives

Continue reading to get started on Part 1 of the project. 

The Set-Up

In this scenario, you were hired as a data analyst on a temporary contract with the company LetsMeet. At the end of the contract, the company will evaluate your work to determine if they want to offer you a salaried position. You are determined to work hard and make a great impression.

LetsMeet brings the local community together by connecting real people in real life. These experiences offer powerful learning opportunities, partnership possibilities, and lifelong connections. The LetsMeet website provides a platform for users to create and search for real-world events that match their interests and location. LetsMeet currently operates in New York, Chicago, and San Francisco, with plans to expand across the United States.

Your job is to support the growth LetsMeet has been experiencing in the past six months by providing insights into who LetsMeet members are and how they use the platform. Right now, the company has 15 full-time employees and no data team. Your direct supervisor will be Isabella, the VP of engineering. Isabella has looked at the data but hasn't performed any significant analysis. This means that you will be the first person on the team to analyze the data.

The company sent you a slide deck to prepare you for your first day. Check out the deck and then continue to read on about your first day.

LetsMeet Slide Deck

Day 1: Morning Summary

On your first day, your manager Isabella is too busy to meet. Fortunately, the operations manager contacts one of the engineers, Quinn, and he shows you how to access the data through MySQL Workbench. Quinn provides you with the following diagram of the LetsMeet SQL database. He encourages you to review the data to learn about the various tables and columns. He says that the team would love to hear any insights you discover while exploring the data.

LetsMeet ERD

An Entity Relationship Diagram (ERD) showing the relationships between six tables: ‘grp_member’, ‘category’, ‘venue_’, ‘event’, ‘grp’, and ‘city’. The ‘grp_member’ table has the columns ‘member_id’, ‘city’, ‘country’, ‘hometown’, ‘joined’, ‘member_name’, ‘member_status’, ‘visited’, and ‘group_id’ (foreign key). The ‘category’ table has the columns ‘category_id’ (primary key), ‘category_name’, ‘shortname’, and ‘sort_name’. The ‘venue_’ table has the columns ‘venue_id’ (primary key), ‘address_1’, ‘address_2’, ‘city’, ‘country’, ‘distance’, ‘lat’, ‘localized_country_name’, ‘lon’, ‘venue_name’, ‘phone’, ‘state’, ‘zip’, and ‘RAND() DOUBLE’. The ‘event’ table has the columns ‘event_id’ (primary key), ‘created’, ‘description’, ‘duration’, ‘event_url’, ‘group_id’ (foreign key), ‘headcount’, ‘how_to_find_us’, ‘maybe_rsvp_count’, ‘event_name’, ‘photo_url’, ‘rsvp_limit’, ‘event_status’, ‘event_time’, ‘updated’, ‘utc_offset’, ‘venue_id’ (foreign_key), ‘visibility’, ‘waitlist_count’, ‘why’, and ‘yes_rsvp_count’. The ‘grp’ table has the columns ‘group_id’ (primary key), ‘category_id’ (foreign key), ‘city_id’ (foreign key), ‘created’, ‘description’, ‘join_mode’, ‘group_name’, ‘rating’, ‘visibility’, ‘members’, and ‘who’. The ‘city’ table has the columns ‘city_id’ (primary key), ‘city’, ‘state’, ‘zip’, ‘country’, ‘localized_country_name’, ‘distance’, ‘longitude’, ‘latitude’, ‘member_count’, and ‘ranking’. The ‘grp_member’ table is linked to the ‘grp’ table via the ‘group_id’ field. The ‘category’ table is linked to the ‘grp’ table via the ‘category_id’ field. The ‘venue_’ table is linked to the ‘event’ table via the ‘venue_id’ field. The ‘city’ table is linked to the ‘grp’ table via the ‘city_id’ field.

As a reminder, these are the symbols used in an ERD:

The Primary Key is denoted with a yellow key symbol. FOREIGN KEY (NOT NULL) is denoted with a red diamond. FOREIGN KEY (NULL values allowed) is denoted by a white diamond with red border. Not a PRIMARY or FOREIGN KEY is denoted by a white diamond with a blue border. ​

You also have an email from Isabella:

"Swamped in meetings right now. Will check in later. Sales need answers to the following questions:

Questions from Sales:

·     What cities have active members? Are there any cities listed in the city table with no active members? What state are those cities in?

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

·     What are the five most popular group categories? What are the five least popular group categories?

·     How many members do we have? What percentage of members are based in Chicago?

Send me the info as soon as you have the chance."

Now that you reviewed the set-up, read on to review the grading rubric for this part of the project. 

Grading and Instructions 

In this part of the project, you have two tasks. First, analyze the tables for insights. Second, provide answers to the questions from Isabella's email. 

Deliverables For This Project:

Two .sql files developed in the Pathstream MySQL Workbench environment.

·     The first should contain your SQL queries that show one insight for each table, along with comments explaining that insight.

·     The second should contain your SQL queries that answer the questions from Isabella, along with comments explaining the answers to the questions. 

Submit all queries and comments within your SQL script in this MySQL Workbench Lab. 

Begin the Project and get started. 

 

 

Ask This Assignment To Be Done By Our Experts

Get instant assignment help service

Related Questions in computer science category