# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python [Root]
#     language: python
#     name: Python [Root]
# ---

# # Homework 4 SQL

# ## Question 1 
#
# Give a count of all videos currently out.

# code: 
#
#     SELECT count(*)  
#     FROM rental
#     WHERE return_date is null
#

# return:
#
# 183
#
#

# ## Question 2
#
# Make a list of all customer names who have videos out and how much they owe.

# code:
#
#     SELECT name,
#            count(*)
#     FROM rental ,
#          customer_list
#     WHERE customer_id = id
#       AND return_date IS NULL
#     GROUP BY name
#     ORDER BY COUNT
#     

# Result:
#
# ![q2](img/hw4/q2.png "q2")

# ## Question 3
#
# Give the most popular actors by store location.

# code:
#
#     SELECT A.first_name,
#            A.last_name,
#            I.store_id,
#            count(*)
#     FROM Inventory I,
#          film_actor F,
#          actor A
#     WHERE I.film_id = F.film_id
#       AND F.actor_id = A.actor_id
#     GROUP BY A.first_name,
#              A.last_name,
#              I.store_id
#     ORDER BY COUNT DESC LIMIT 2
#
#

# result:
#
# ![q3](img/hw4/q3.png "q3")

# ## Question 4
#
# Using a $1 per day late fee. Find out which users owe the most assuming all rentals are a week long.

# code:
#
#     SELECT distinct(name),
#            rental_days,
#            rental_days - 7 AS latefees
#     FROM
#       (SELECT name, (extract(DAY
#                              FROM return_date) - extract(DAY
#                                                          FROM rental_date)) + 30 * (extract(MONTH
#                                                                                             FROM return_date) - extract(MONTH
#                                                                                                                         FROM rental_date)) AS rental_days
#        FROM rental r,
#             customer_list c
#        WHERE c.id = r.customer_id
#          AND return_date IS NOT NULL) NEW
#     ORDER BY rental_days DESC
#     

# return
#
# ![q4](img/hw4/q4.jpg "q4")

# ## Question 5
#
# What hour of the day to people rent the most ?

# code :
#
#     SELECT extract(hour
#                    FROM rental_date) AS rental_hour,
#            count(*)
#     FROM rental
#     GROUP BY rental_hour
#     ORDER BY count DESC LIMIT 1 
#   

# return:
#
# 15:00
#
# count: 887

# ## Question 6
#
# Which store is more profitable, assuming all movies cost $15 per inventory item to purchase.

# code:
#
#     select T1.store_id,sum,cost,sum-cost as profit
#     from
#     (select store_id,sum(amount)
#     from payment P, Inventory I, rental R
#     where P.rental_id = R.rental_id and R.inventory_id = I.inventory_id
#     group by store_id ) as T1,
#
#     (select store_id,count(*)*15 as cost
#      from inventory
#      group by store_id
#     ) as T2
#
#     where T1.store_id = T2.store_id

# ![q4](img/hw4/q6.png "q4")



StarcoderdataJupyterScriptsDedupFiltered