Analyzing Maven Movies Database.

Case Study:

The Maven Movies' company insurance policy is up for renewal and the insurance providing company’s underwriters need some updated information from it before they will issue a new insurance policy.

Objective:

Leveraging SQL skills to extract and analyze data from various tables in the Maven Movies database for the purpose of answering the insurance underwriters’ questions. Each question can be answered by querying just one table. Part of the analysis task is figuring out which table to be used.

The project workflow follows the below key questions the insurance underwriters need to be answered:

• We will need a list of all staff members, including their first and last names, email addresses, and the store identification number where they work.

• We will need separate counts of inventory items held at each of your two stores.

• We will need a count of active customers for each of your stores. Separately, please.

• In order to assess the liability of a data breach, we will need you to provide a count of all customer email addresses stored in the database.

• We are interested in how diverse your film offering is as a means of understanding how likely you are to keep customers engaged in the future. Please provide a count of unique film titles you have in inventory at each store and then provide a count of the unique categories of films you provide.

• We would like to understand the replacement cost of your films. Please provide the replacement cost for the film that is least expensive to replace, the most expensive to replace, and the average of all films you carry.

• We are interested in having you put payment monitoring systems and maximum payment processing restrictions in place in order to minimize the future risk of fraud by your staff. Please provide the average payment you process, as well as the maximum payment you have processed.

• We would like to better understand what your customer base looks like. Please provide a list of all customer identification values, with a count of rentals they have made all-time, with your highest volume customers at the top of the list.

Running the project:

1 -- MySQL Workbench or similar is needed. 2 -- Use the "create_mavenmovies.sql" database file to read data to the project script.