Question) Build the Rental Return Fact

Using the same method used for building the rental_fact table, build a rental_return_fact table based on returned movies.

Build Rental Return fact

Customer is Late if returned after 3 days of rental date, including not returned yet

Run the query as if it was 2006-02-15.

Measures :

total day rented

total day late:

total late fee

-- $4.99 for each day late

total_video_lost =1 (if not been returned for more than 60 days)

Total replacement cost (from film_dim)

-------------------------------------------------------------------------------------------------------

USE THE FOLLOWING FILES FOR YOUR REFERENCE:

Contents of film_dim:

create or replace TABLE "MYDB"."SAKILA_ANALYTICS".FILM_DIM ( FILM_KEY NUMBER(38,0) NOT NULL autoincrement, FILM_ID NUMBER(38,0) NOT NULL, FILM_NAME VARCHAR(100) NOT NULL DEFAULT 'NA', FILM_LANGUAGE VARCHAR(50) NOT NULL DEFAULT 'NA', FILM_CATEGORY VARCHAR(100) NOT NULL DEFAULT 'NA', RENTAL_DURATION_DAY NUMBER(38,0) NOT NULL DEFAULT 0, RENTAL_RATE NUMBER(4,2) NOT NULL DEFAULT 0, REPLACEMENT_COST NUMBER(4,2) NOT NULL DEFAULT 0, LENGTH_MIN NUMBER(38,0) NOT NULL DEFAULT 0 );

create or replace sequence seq1; CREATE OR REPLACE SEQUENCE FIL_DIM; INSERT INTO "MYDB"."SAKILA_ANALYTICS".FILM_DIM ( FILM_KEY, FILM_ID , FILM_NAME , FILM_LANGUAGE , FILM_CATEGORY, RENTAL_DURATION_DAY , RENTAL_RATE , REPLACEMENT_COST , LENGTH_MIN )

SELECT seq1.nextval as FILM_KEY , f.FILM_ID, f.TITLE AS FILM_TITLE, l.NAME AS LANGUAGE, c.NAME AS CATEGORY_NAME, f.RENTAL_DURATION, f.RENTAL_RATE, f.REPLACEMENT_COST, f.LENGTH FROM "MYDB"."PUBLIC"."FILM" f JOIN "MYDB"."PUBLIC"."LANGUAGE" l ON f.LANGUAGE_ID = l.LANGUAGE_ID JOIN "MYDB"."PUBLIC"."FILM_CATEGORY" fc ON f.film_id = fc.film_id JOIN "MYDB"."PUBLIC"."CATEGORY" c ON fc.category_id = c.category_id;

------------------------------------------------------------

CONTENTS OF rental_return_fact_ddl.sql file:

create or replace TABLE "MYDB"."SAKILA_ANALYTICS".RENTAL_RETURN_FACT ( RENTAL_RETURN_DATE_KEY NUMBER(38,0) NOT NULL, RENTAL_ID NUMBER(38,0) NOT NULL, CUSTOMER_KEY NUMBER(38,0) NOT NULL, FILM_KEY NUMBER(38,0) NOT NULL, STAFF_KEY NUMBER(38,0) NOT NULL, STORE_LOCATION_KEY NUMBER(38,0) NOT NULL, total_day_rented NUMBER(38,0) NOT NULL, total_day_late NUMBER(38,0) NOT NULL, total_late_fee_amount NUMBER(38,0) NOT NULL, total_video_lost NUMBER(38,0) NOT NULL,total_video_lost total_replacement_cost NUMBER(4,2) NOT NULL ); 

----------------------------------------------------------------------------------

CONTENTS OF rental_return_fact.sql file: Build Rental Return fact

-- Query for those who returned video SELECT r.return_date, DATEDIFF(r.return_date, r.rental_date ) total_day_rented , -- in days total_day_late total_late_fee_amt FROM rental r where datediff(return_date, rental_date) > 3

SELECT r.return_date, DATEDIFF(r.return_date, r.rental_date ) total_day_rented , -- in days 0 as total_day_late 0 total_late_fee_amt FROM rental r where datediff(return_date, rental_date)

-- Query for those who have not return yet SELECT r.return_date, DATEDIFF(r.return_date, r.rental_date ) rental_duration , -- in days total_day_late total_late_fee_amt FROM rental r where datediff('2006-02-15', rental_date) > 3 and return_date is null ;

SELECT r.return_date, DATEDIFF(r.return_date, r.rental_date ) rental_duration , -- in days 0 as total_day_late 0 as total_late_fee_amt FROM rental r where datediff('2006-02-15', rental_date)

-------------------------------------------------------------------------------------------------

CONTENTS OF rental_fact:

You can also refer to the following screenshot for rental_fact:

----------------------------------------------------------------------------

CONTENTS OF sakila_KPIs_1.sql:

-- Q1. What are the most frequently rented movies, in descending order

select A.film_id, A.title, B.* from film A join ( select inv.film_id, count(ren.rental_id) times_rented from rental ren join inventory inv on ren.inventory_id = inv.inventory_id group by inv.film_id ) B on A.film_id = B.film_id order by B.times_rented desc;

-- Q2. How much revenue did each store make?

select A.store_id, B.sales from store A join ( select cus.store_id, sum(pay.amount) sales from customer cus join payment pay on pay.customer_id = cus.customer_id group by cus.store_id ) B on A.store_id = B.store_id order by a.store_id;

-- Q3. List each film and the number of actors who are listed for that film in descending order

select flm.title, count(*) number_of_actors from film flm inner join film_actor fim_act on flm.film_id = fim_act.film_id group by flm.title order by number_of_actors desc; ----------------------------------------------------------------------------------------------

Da rental_factpoft - Adobe Acrobat Reader (64-bit) File Edit View Sign Windaw Help