sql-project-submission by Hellen Namulinda.txt

/*Project One: Investigate a Relational Database*/

Question 1(question set 1 #1): We want to understand more about the movies that families are watching. The following categories are considered family movies: Animation, Children, Classics, Comedy, Family and Music.

Create a query that lists each movie, the film category it is classified in, and the number of times it has been rented out

/* Query 1 - the query used for the first insight */
SELECT
    f.title AS film_title,
    c.name AS category_name,
    COUNT(*) AS rental_count
FROM
    film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
    JOIN inventory i ON i.film_id = f.film_id
    JOIN rental r ON r.inventory_id = i.inventory_id
WHERE
    c.name IN (
        'Animation',
        'Children',
        'Classics',
        'Comedy',
        'Family',
        'Music'
    )
GROUP BY
    1,
    2
ORDER BY
    2,
    1;


Question 2 (question set 1 #2): Now we need to know how the length of rental duration of these family-friendly movies compares to the duration that all movies are rented for. Can you provide a table with the movie titles 
and divide them into 4 levels (first_quarter, second_quarter, third_quarter, and final_quarter) based on the quartiles (25%, 50%, 75%) of the rental duration for movies across all 
categories? Make sure to also indicate the category that these family-friendly movies fall into. 

/* Query 2 - the query used for the second insight */
SELECT
    f.title,
    c.name,
    f.rental_duration,
    NTILE(4) OVER (
        ORDER BY
            f.rental_duration
    ) AS standard_quartile
FROM
    film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
WHERE
    c.name IN (
        'Animation',
        'Children',
        'Classics',
        'Comedy',
        'Family',
        'Music'
    )
ORDER BY
    4;


Question 3(question set 1 #3): Finally, provide a table with the family-friendly film category, each of the quartiles, and the corresponding count of movies within each combination of film category for each 
corresponding rental duration category. The resulting table should have three columns:

a. Category
b. Rental length category
c. Count
The Count column should be sorted first by Category and then by Rental Duration category.

/* Query 3 - the query used for the third insight */ 
SELECT
    t.name AS category_name,
    t.standard_quartile AS rental_length_category,
    COUNT(t.standard_quartile) AS cental_count
FROM
    (
        SELECT
            f.title,
            c.name,
            f.rental_duration,
            NTILE(4) OVER (
                ORDER BY
                    f.rental_duration
            ) AS standard_quartile
        FROM
            film f
            JOIN film_category fc ON fc.film_id = f.film_id
            JOIN category c ON c.category_id = fc.category_id
        WHERE
            c.name IN (
                'Animation',
                'Children',
                'Classics',
                'Comedy',
                'Family',
                'Music'
            )
    ) t
GROUP BY
    1,
    2
ORDER BY
    1,
    2;


Question 4(question set 2 #1)
We want to find out how the two stores compare in their count of rental orders during every month for all the years we have data for. Write a query that returns the store ID for the store, the year and month and the number of rental orders each store has fulfilled for that month. Your table should include a column for each of the following: year, month, store ID and count of rental orders fulfilled during that month.

The count of rental orders is sorted in descending order.

/* Query 4 - the query used for the fourth insight */
SELECT
    DATE_PART('month', r1.rental_date) rental_month,
    DATE_PART('year', r1.rental_date) rental_year,
    s1.store_id,
    COUNT(*) AS count_rentals
FROM
    rental r1
    JOIN staff s1 ON s1.staff_id = r1.staff_id
    JOIN store s2 ON s2.store_id = s1.store_id
GROUP BY
    1,
    2,
    3
ORDER BY
    4 DESC;