You and your business partner were recently approached by another local business owner who is interested in purchasing your Movies Rental Business. He primarily owns restaurant and bars, so he has lots of questions for you about your business and the rental business in general. His offer seems very generous, so you are going to entertain his questions
Leverage your SQL skills to extract and analyze data from various tables in the Movies Database to answer you potential Acquirer’s questions. Each question will require you to write a multi-table SQL query, joining at least two tables
- 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).
- 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 you have stocked, including the store_id number, the inventory_id, the name of the film, the film's rating, its rental rate, and replacement cost.
- From the same list of films you just pulled, please roll that data up and provide a summary-level overview of your inventory. We would like to know how many inventory items you have with each rating at each store.
- 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 film 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.
- We want to make sure you folks have a good handle on who your 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).
- We would like to understand how much your customers are spending with you and also to know who your most valuable customers are. Please pull together a list of customer names, their total lifetime rentals, and the sum of all payments you have 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.
- My partner and I would like to get to know your 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, for the investors, it would be good to include which company they work with.
- We're interested in how well you have covered the most-awarded actors. Of all the actors with three types of awards, for what % of them do we carry a film? And how about actors with two types of awards? Same questions. Finally, how about actors with just one award?
