Date and Time operations

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema='dvdrentals'

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema='dvdrentals'

SELECT * FROM dvdrentals.film

SELECT * FROM dvdrentals.rental

SELECT * FROM dvdrentals.inventory

/* Calculate the expected return date of a film by using rental_date from rental table and rental duration from film table. Actual return date is in rental table as well. 
But film table and rental table can not be joined together because in film table it is the film_id the primary key while in rental table it is the inventory_id. First I need to LEFT JOIN the rental table to inventory table on inventory_id. From that join, I will get film_id and then LEFT JOIN the resulting join with film table on film_id */
SELECT f.title, r.rental_date, r.return_date, f.rental_duration,
	r.return_date - r.rental_date AS days_rented,
    --r.rental_date + f.rental_duration AS expected_return_date, NOT possible since rental date is timestamp and rental_duration is smallint
    ROW_NUMBER() OVER(ORDER BY f.title ASC) AS total_rentals,
    DENSE_RANK() OVER(ORDER BY f.title ASC) AS distinct_titles 
FROM dvdrentals.rental AS r
INNER JOIN dvdrentals.inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN dvdrentals.film AS f ON i.film_id = f.film_id -- Now I have all the necessary columns
ORDER BY f.title ASC;

/* The results show that there are 168 distinct films rented with 2857 records. This means a fim is in manyinventories. */

/* Convert the rental_duration to INTERVAL type and exclude rentals that are still outstanding */
SELECT INTERVAL '1' day *  f.rental_duration AS rental_duration, -- convert rental duration to 1 day INTERVAL data type
		r.rental_date, r.return_date,
        r.return_date - r.rental_date AS days_rented
FROM dvdrentals.rental AS r
INNER JOIN dvdrentals.inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN dvdrentals.film AS f ON i.film_id = f.film_id
WHERE r.return_date IS NOT NULL
ORDER BY f.title;

/* Now calculate the expected return date as well */
SELECT 
	INTERVAL '1' day * f.rental_duration AS rental_duration,
    r.rental_date,
    r.rental_date + (INTERVAL '1' day * f.rental_duration) AS expected_return_date,
    r.return_date
FROM dvdrentals.rental AS r
INNER JOIN dvdrentals.inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN dvdrentals.film AS f ON i.film_id = f.film_id

SELECT
	CURRENT_TIMESTAMP(0)::TIMESTAMP AS right_now,
    INTERVAL '5 days' + CURRENT_TIMESTAMP(0) AS five_days_from_now;
    

SELECT NOW()::DATE AS today;

SELECT CURRENT_DATE::DATE ;

SELECT CURRENT_TIMESTAMP;

SELECT * FROM dvdrentals.payment;

/* Want to get the quarterly amount of rental revenue */
-- Use EXTRACT() function to extract the quarter and the year of each payment date and sum the amounts within year-quarter groups.

SELECT 
	EXTRACT(quarter FROM dvdrentals.payment.payment_date) AS quarter,
    EXTRACT(year FROM dvdrentals.payment.payment_date) AS year,
    SUM(amount) AS total_amount
FROM dvdrentals.payment
GROUP BY 1, 2
ORDER BY total_amount DESC;

/* Create a field showing the day of the week and then count the total number of rentals by day of the week from the rental table */

SELECT 
	EXTRACT(dow FROM rental_date) AS dayofweek,
    COUNT(*) AS rentals
FROM dvdrentals.rental
GROUP BY 1 -- group by the position of the field in SELECT statement, that is dayofweek
ORDER BY rentals DESC;