Our SQL tutorials have covered how to look up data, navigate it, and perform simple calculations. But in practice, we want to extract specific data from specific tables, not all the data in every table. The queries we have learned so far can filter most of that information, but we still need to cover a few more. Here are more queries and functions that are essential in your analytic data journey.

There are specific standards that cannot be omitted from marketing and data analysis. The most important criteria when looking at existing customers, new customers, sales, and other data is the date.

#Table of Contents

Query 1. Find the number of rentals that occur on a daily basis
SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’) AS date, count(rental_id) AS count_rental FROM rental GROUP BY date;

Query 2. Find which day of the week has the most rentals
SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week, count(rental_id) AS count_rental FROM rental GROUP BY day_of_the_week ORDER BY count_rental DESC;

Query 3. Find the rental duration by calculating the difference between the rental date and return date
SELECT rental_id, customer_id, rental_date, return_date, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental;

Before we dive into the information on the rental table, we need to become familiar with the date function and format of the SQL query. Let’s first run a query that prints the current date using DATE_FORMAT.

#DATE_FORMAT

Think of DATE_FORMAT as a column name and plug it in after your SELECT query, then specify a factor that has to do with the date. In our case, we want today’s date (now()). Next, we’ll specify the format. Using commas, spaces, and dashes, we can rearrange the date in any format we want! For this example, we will demonstrate two: First, year-month-day followed by the name of the day(‘%Y-%m-%d, %a’). Second, day-month-year preceded by the name of the day(‘%W, %d-%m-%y’).

Try these:

SELECT DATE_FORMAT(now(), ‘%Y-%m-%d, %a’);
SELECT DATE_FORMAT(now(), ‘%W, %d-%m-%y’);

⭐️QueryPie provides a multi-query function that enables you to run two or more queries simultaneously and view query results at the same time!

Here is a table of helpful shortcuts you can use:

Q1. Find the number of rentals that occur on a daily basis and organize by date (using COUNT and DATE_FORMAT)

First, let’s look at the rental history data in the rental table.

SELECT * FROM rental;

This table contains the following information:

- Rental ID: (rental_id)
- Date of the rental: (rental_date)
- Inventory ID: (inventory_id)
- Date of return: (return_date)
- Customer ID:(customer_id)
- Staff ID: (staff_id)

Let’s say you want to look at the number of movie rentals that occur at the rental store daily. It’s simple. We can easily do this using the DATE_FORMAT query along with previously learned COUNT and GROUP BY.

This time, instead of using now() we will designate rental_date as the column we want date information to be pulled from, and specify the format as year-month-day. Let’s also rename this new column ‘date’.

SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’ ) AS date,

🔑 Remember to capitalize the Y in %Y so the full 4 digit year shows!

To find the number of all rentals, use COUNT and (rental_id), then rename that new column as count_rental. We’re using ID here because every movie has a unique ID assigned to them. Specify the table we want all this information from(rental):

COUNT(rental_id) AS count_rental FROM rental

And finally, organize the information date with GROUP BY. Your final query should look like this:

SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’) AS date, count(rental_id) AS count_rental FROM rental GROUP BY date;

This query returns the number of rentals every day available on the record. Although it doesn’t offer the exact time (00:00:00), it does give the year-day-month of the rental starting from 8 rentals on May 24, 2005.

Q2. Find which day of the week has the most rentals (using GROUP BY and ORDER BY)

Now that we’ve checked the number of movie rentals daily let’s check the number of movie rentals on a weekly day basis. We will use the same data in the rental table, and the query statements are very similar. The only thing we’ll change is the date output format.
Before we used ‘%Y-%m-%d’ to find the full date of the rental day, but in this next query, we only want the name of the day. If you check the handy table we provided in this tutorial, you will see to get this information we need to use a capital W (%W).

Just like Query 1, use the rental_date column as a basis for the date information(DATE_FORMAT(rental_date,). For the format, clarify that you only want the name of the day(%W). And rename the new column with AS.

SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week

Next use COUNT to find the sum of the rentals and rename that column as well:

count(rental_id) AS count_rental

Clarify the name of the table:

FROM rental

Organize the information with GROUP BY and ORDER BY using the new column names we just made:

GROUP BY day_of_the_week ORDER BY count_rental DESC

Put it all together!!

SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week, count(rental_id) AS count_rental FROM rental GROUP BY day_of_the_week ORDER BY count_rental DESC;

As you can see in the results, the largest number of loans occurred on Tuesday.

Q3. Find the rental duration by calculating the difference between the rental date and return date

Using the information in the rental table, let’s calculate the rental period, rental date, and the return date for each movie. We can do this using the query DATEDIFF.

#DATEDIFF

DATEDIFF calculates the number of days between two date values, date1, and date2. In our example, we want to know how long the movie was rented out. So we will calculate the difference between the day it was rented out (rental_date) and the day it was returned (return_date). Go ahead and rename the new column to Rental Duration:

DATEDIFF(return_date, rental_date) AS rental_duration FROM rental

Earlier we mentioned we also want to see the rental ID, customer ID, rental date and return date, so specify those in the SELECT line.

SELECT rental_id, customer_id, rental_date, return_date

Of course, don’t forget to clarify which table you want all this info from:

FROM rental;

Put it all together and it should look like this:

SELECT rental_id, customer_id, rental_date, return_date, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental;

This method can also be useful for inquiring about the average lease period/max loan period/minimum loan period.