Maven Movies
Maven Movies is a brick-and-mortar DVD Rental business. A potential investor has got access to the entire Maven Movies SQL database and needs some help with answering his questions about the business.
The Situation
An investor is interested in purchasing Maven Movies, a brick-and-mortar DVD Rental business. He has some questions about the business before proceeding with its purchase.
The Objective
Use MySQL to extract and analyze data from various tables in the Maven Movies database to answer the investor’s questions.
Data Sources
16 database tables, related to each other.
The Database
1. My partner and I want to come by each of the stores in person and meet the managers. Please send over the managers’ names at each store, with the full address of each property (street address, district, city, and country please).
The Query
The Result
2. I would like to get a better understanding of all of the inventory that would come along with the business. Please pull together a list of each inventory item in stock, including the store_id number, the inventory_id, the name of the film, the film’s rating, its rental rate and replacement cost.
The Query
The Result
3. From the same list of the films just pulled, please roll that data up and provide a summary-level overview of the inventory. We would like to know how many inventory items there are with each rating at each store.
The Query
The Result
4. Similarly, we want to understand how diversified the inventory is in terms of replacement costs. We want to see how big of a hit it would be if a certain category of films became unpopular at a certain store. We would like to see the number of films, as well as the average replacement cost, and total replacement cost, sliced by store and film category.
The Query
The Result
5. We want to make sure we get a good handle on who the current customers are. Please provide a list of all customer names, which store they go to, whether or not they are currently active, and their full addresses (street address, city, and country).
The Query
The Result
6. We would like to understand how much customers are spending, and also to know who the most valuable customers are. Please pull together a list of customer names, their total lifetime rentals, and the sum of all payments collected from them. It would be great to see this ordered on total lifetime value, with the most valuable customers at the top of the list.
The Query
The Result
7. My partner and I would like to get to know the board of advisors and any current investors. Could you please provide a list of advisor and investor names in one table? Could you please note whether they are an investor or an advisor, and for the investors, it would be good to include which company they work with.
The Query
The Result
8. We're interested in how well covered the most awarded actors are. Of all the actors with three types of awards, for what % of them do they carry a film? And how about for actors with two types of awards? Same questions. Finally, how about actors with just one award?
The Query
The Result
