BlockBooster

To showcase some of my SQL abilities, I took data from a fictional movie rental location (Block booster) and conducted some ad-hoc questions that could be answered from the fictional list.

To get started, I first created a visio drawing showing the tables and their respective primary keys and where they coordinate with eachother:

Or alternatively, you can download the PDF of the above for easier viewing

General Film Questions

Of all the films we have, what are the 10 most frequent actors that appear on those films?

How many films of each type of rating do we have?

Which category of films do we have the most of?

General Store Questions

What would be the replacement cost of our films in each of our stores?

How many staff do we have at each store location?

How many customers have visited each location?

Create a query that compares the average rental revenue based on the length of the movie in 30 minute increments

Ad-Hoc Questions

Which films based on the year is us the most revenue? (note, current year is 2007 in example)

Marketing is looking for a list of all the domain names used by customers that are used more than once, ordered by frequency

Oh no!

We’re missing a domain name. After discussing with management, there should be 12 customers with a gmail domain name, conveniently, all 12 customers have a last name that start with “co”.

And a simple select statement to ensure we corrected the mistake

Who were our most valuable customers for the prior month that spent at least $80? (Note, the last date on record for these tables is 05-14-2007)

What day is our largest revenue producing day? For days in which we make less than the average of $2.95 per rental, mark those days as concerning.