Calculating Inventory on Hand in SQL

Recently I've been working with a sample data set based on a fictional dvd rental store. In this example I'm using Postgres pgAdmin 4. 

As I began to query the data it occurred to me that there was no easy way to view inventory levels. This is vital information for any retail employee. I challenged myself to see if I could query inventory levels as of a particular date without creating a new table. 

I identified three tables that contained all the information I needed.

The main table I worked with is the 'rental' table. This table contains a list of rental transactions. Each transaction is assigned a serialized number along with the unique identifier for a particular copy of a dvd, rental date, and return date. 

Next I used the 'inventory' table which assigns a unique identifier to each copy of a particular film. I used this table to associate a dvd's inventory id with a film id and thus be able to count unique copies of a particular film title.

The third table I used was the 'film' table. This table contains various information about each unique film title. I used this table to pull the title of the film. It's not mandatory, but helps with readability.

In the query example I am calling the inventory level of film_id '7' for the date of 08/01/2005. 

First I call the film title via two inner joins. Because the 'rental' table does not contain the film id and the 'film' table does not contain the inventory id we have to use the 'inventory' table as a reference as it contains both the film id and inventory id of each dvd in inventory. 

Next I am calling the film id as this is the item we want to look for. I find it helpful to see this in the results, but it's not strictly necessary. 

I also created a column called 'date' which is purely for readability. It is just a repetition of the date I use in the next column. 

Next I have the meat of the query. First, I call a count of film_id from the 'inventory' table. This is going to give me the number of copies of a particular title we're starting out with. You'll notice I called that directly from the inventory table and not through my join. If I called this through my join the query would instead count all instances of the film id on my rental table. 

From the starting inventory I subtract the inventory change to date. I find this by performing a conditional sum. Every time there is a rental date the query give a value of -1 and every time there is a return date the query gives a value of +1. 

We use the conditional sum rather than a WHERE statement to get around the rental and return being part of the same row. If we use a WHERE statement the query will count the rental and return together. 

By summing the starting inventory, the rentals, and the returns we are able to identify our inventory level as of a date.