Write a single sql statement for each query below (15 pts ea…
Write a single sql statement for each query below (15 pts each). All queries are against the Sakila sample database. If you want to do any setup, such as creating a view, you can do this in your script but the final results for each problem should be returned by a single query. To open the sample results files without disturbing your test, right-click and open in a new tab or window. Query 1 (14 pts) List the customer id, first name, and last name of all customers in the database. Include an addition column, is_active, that indicates ‘Active’ if the customer’s most recent rental date is within 30 days of the most recent rental date in the database, and ‘Inactive’ otherwise. Order your results by customer last name, then first name. (599 rows, q1.csv) Query 2 (14 pts) List our top 10 titles by total rental business recorded for those titles in the payment table. Include a column indicating the rank of each title, such that the title generating the most business is ranked #1. Show the #1 ranked title first. (10 rows, q2.csv) Query 3 (14 pts) List the rental id, rental date, film id, title, and amount paid for each rental according to the payment table. Order your results by title, then rental date. Include a column indicating the number of each rental (the first time a film is rented is #1) and a column showing the cumulative sales of the title at each rental. (16044 rows, one for each rental, q3.csv) Query 4 (14 pts) List the rental id, rental date, customer id, first name, last name, and number the rentals for each customer in each day. Include a final column, day_type, that idicates whether that rental was the only rental for that customer that day (single-rental day), or if the rental was part of a ‘multi-rental day’ for the customer. Order your results by customer first then rental date. (16044 rows, one for each rental, q4.csv) Query 5 (14 pts) List the customer id, first name, last name, home store (from the customer table), late percentage, and late percentage rank for customers whose late percentage is in the top 10 for their store. The late percentage is the number of rentals returned late divided by the total number of rentals for the customer. Exclude rentals that have not been returned from your results. Order your results by store, then by late percentage rank such that the customer with the highest percent late appears first. (20 rows; top 10 for each of 2 stores, q5.csv) For example, Marilyn Ross (customer id 80) has 30 total rentals, of which 29 have been returned. 20 of the 29 (69%) were returned late, which is the 5th-highest percentage of customers whose home store is 1. Recursive CTEs Solve each of these queries using a recursive CTE for full credit. Query 6a (10 pts) List the year, month, and number of rentals in that month. Include months where we have no record of any rentals. Begin with the earliest month for which we have data, and end at the latest month for which we have data. (10 rows, q6a.csv) Query 6b (10 pts, extra credit) List the customer id, first name, last name, and the longest gap (in days) between consecutive rentals for that customer. Your query should use a recursive CTE to calculate the results. (599 rows, one for each customer, q6b.csv) For example, Lucy Wheeler (customer id 208) rented on August 17, 2005. She didn’t rent again until February 14, 2006, going 180 days without renting. Similarly, Kimberly Lee (customer id 24) rented on May 31, 2005. She didn’t rent again until June 16, going 15 days without renting.
Read Details