Question) Build KPIs using sakila_KPIs_1.sql, rental_fact and dimensions.sql

Convert the SQL query from OLTP Sakila to OLAP Queries.

1. Avg Rental sale amount ($) Per film category

2. Avg Rental sale amount ($) Per customer

3. Avg Rental sale amount ($) Per Customer Country

4. Avg Sales ($) per Staff

5. Avg Rental Quantity per customer

6. Total Rental sale $ amount by Zip Code

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

The contents of the files (highlighted in bold above) are as follows:

CONTENTS OF sakila_KPIs_1.sql file:

-- 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;

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

CONTENTS of dimensions.sql FILE:

CREATE OR REPLACE TABLE "MYDB"."SAKILA_ANALYTICS".CUSTOMER_DIM ( CUSTOMER_KEY NUMBER(38,0) NOT NULL autoincrement, CUSTOMER_ID NUMBER(38,0) NOT NULL, FIRSTNAME VARCHAR(50) NOT NULL DEFAULT 'NA', LASTNAME VARCHAR(50) NOT NULL DEFAULT 'NA', FIRSTNAME_LASTNAME VARCHAR(100) NOT NULL DEFAULT 'NA', CUSTOMER_EMAIL VARCHAR(150) NOT NULL DEFAULT 'NA', CUSTOMER_ADDRESS VARCHAR(100) NOT NULL DEFAULT 'NA', CUSTOMER_ADDRESS2 VARCHAR(100) DEFAULT 'NA', CUSTOMER_CITY VARCHAR(100) NOT NULL DEFAULT 'NA', CUSTOMER_DISTRICT VARCHAR(100) NOT NULL DEFAULT 'NA', CUSTOMER_ZIP VARCHAR(10) NOT NULL DEFAULT 'NA', CUSTOMER_COUNTRY VARCHAR(50) NOT NULL DEFAULT 'NA', CUSTOMER_PHONE VARCHAR(50) NOT NULL DEFAULT 'NA', IS_ACTIVE VARCHAR(5), CUSTOMER_REGISTRATION_DATE DATE NOT NULL , CUSTOMER_LAST_UPDATE datetime NOT NULL ); CREATE OR REPLACE SEQUENCE CUST_DIM;

INSERT INTO "MYDB"."SAKILA_ANALYTICS".CUSTOMER_DIM ( CUSTOMER_KEY, CUSTOMER_ID , FIRSTNAME , LASTNAME , FIRSTNAME_LASTNAME , CUSTOMER_EMAIL , CUSTOMER_ADDRESS , CUSTOMER_ADDRESS2 , CUSTOMER_CITY , CUSTOMER_DISTRICT , CUSTOMER_ZIP , CUSTOMER_COUNTRY , CUSTOMER_PHONE , IS_ACTIVE , CUSTOMER_REGISTRATION_DATE , CUSTOMER_LAST_UPDATE ) SELECT cust_dim.nextval as CUSTOMER_KEY , c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME, CONCAT (FIRST_NAME, ',',LAST_NAME ) AS firstlastname, c.EMAIL, a.address AS customer_address, a.address2 AS customer_address2, ci.city AS customer_city, a.district AS customer_district, a.postal_code AS customer_postal_code, co.country AS customer_country, a.phone AS customer_phone, CASE c.active WHEN c.active=1 THEN 'Yes' ELSE 'No' END AS is_active, c.create_date AS registration_date, to_timestamp(c.last_update) AS customer_last_udpate FROM "MYDB"."SAKILA"."CUSTOMER" c JOIN "MYDB"."SAKILA"."ADDRESS" a ON c.address_id = a.address_id JOIN "MYDB"."SAKILA".city ci ON a.city_id = ci.city_id JOIN "MYDB"."SAKILA".country co ON ci.country_id = co.country_id ;

-- * * * * * * * * * * * * * * * * * * -- "SAKILA_DW"."ANALYTICS".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; 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"."SAKILA".film f JOIN "MYDB"."SAKILA".LANGUAGE l ON f.language_id = l.language_id JOIN "MYDB"."SAKILA".film_category fc ON f.film_id = fc.film_id JOIN "MYDB"."SAKILA".category c ON fc.category_id = c.category_id;

-- * * * * * * * * * * * * * * * * * * -- "SAKILA_DW"."ANALYTICS".STAFF_DIM -- * * * * * * * * * * * * * * * * * * create or replace TABLE "MYDB"."SAKILA_ANALYTICS".STAFF_DIM ( STAFF_KEY NUMBER(38,0) NOT NULL autoincrement, STAFF_ID NUMBER(38,0) NOT NULL, STAFF_FIRSTNAME VARCHAR(50) NOT NULL DEFAULT 'NA', STAFF_LASTNAME VARCHAR(50) NOT NULL DEFAULT 'NA', FIRSTNAME_LASTNAME VARCHAR(100) NOT NULL DEFAULT 'NA', STAFF_EMAIL VARCHAR(100) NOT NULL DEFAULT 'NA', STAFF_IS_ACTIVE VARCHAR(3) ); create or replace sequence seq1; INSERT INTO "MYDB"."SAKILA_ANALYTICS".STAFF_DIM ( STAFF_KEY, STAFF_ID, STAFF_FIRSTNAME, STAFF_LASTNAME, FIRSTNAME_LASTNAME, STAFF_EMAIL, STAFF_IS_ACTIVE ) SELECT seq1.nextval as STAFF_KEY, s.staff_id, s.first_name, s.last_name, CONCAT (FIRST_NAME, ',',LAST_NAME) AS firstlastname, s.email, CASE s.ACTIVE WHEN 'TRUE' THEN 'Yes' ELSE 'No' END AS active FROM "MYDB"."SAKILA".staff s; 

-- * * * * * * * * * * * * * * * * * * -- "SAKILA_DW"."ANALYTICS".STORE_LOCATION_DIM -- * * * * * * * * * * * * * * * * * * create or replace TABLE "MYDB"."SAKILA_ANALYTICS".STORE_LOCATION_DIM ( STORE_LOCATION_KEY NUMBER(38,0) NOT NULL autoincrement, STORE_ID NUMBER(38,0) NOT NULL, STORE_POSTAL_CODE VARCHAR(10) NOT NULL DEFAULT 'NA', STORE_CITY VARCHAR(100) NOT NULL DEFAULT 'NA', STORE_DISTRICT VARCHAR(50) NOT NULL DEFAULT 'NA', STORE_COUNTRY VARCHAR(50) NOT NULL DEFAULT 'NA', STORE_PHONE VARCHAR(20) NOT NULL DEFAULT 'NA' ); create or replace sequence seq1; INSERT INTO "MYDB"."SAKILA_ANALYTICS".STORE_LOCATION_DIM ( STORE_LOCATION_KEY, STORE_ID, STORE_POSTAL_CODE, STORE_CITY, STORE_DISTRICT, STORE_COUNTRY, STORE_PHONE ) SELECT seq1.nextval as STORE_LOCATION_KEY , s.store_id, a.postal_code, c.city, a.district, co.country, a.phone FROM "MYDB"."SAKILA"."STORE" s JOIN "MYDB"."SAKILA"."ADDRESS" a ON s.address_id = a.address_id JOIN "MYDB"."SAKILA"."CITY" c ON a.city_id = c.city_id JOIN "MYDB"."SAKILA"."COUNTRY" co ON c.country_id = co.country_id; 

-- * * * * * * * * * * * * * * * * * * -- "SAKILA_DW"."ANALYTICS".RENTAL_FACT -- * * * * * * * * * * * * * * * * * * create or replace TABLE "MYDB"."SAKILA_ANALYTICS".RENTAL_FACT ( RENTAL_DATE_KEY NUMBER(38,0) NOT NULL, RENTAL_TIMEOFDAY_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, RENTAL_QUANTITY NUMBER(38,0) NOT NULL, RENTAL_TOTAL_AMOUNT NUMBER(4,2) NOT NULL );

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

CONTENTS OF rental_fact FILE:

CREATE OR REPLACE TABLE "MYDB"."SAKILA_ANALYTICS".RENTAL_FACT ( RENTAL_DATE_KEY NUMBER(38,0) NOT NULL, RENTAL_TIMEOFDAY_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, RENTAL_QUANTITY NUMBER(38,0) NOT NULL, RENTAL_TOTAL_AMOUNT NUMBER(4,2) NOT NULL ); CREATE OR REPLACE SEQUENCE rental_fact ;

INSERT INTO "MYDB"."SAKILA_ANALYTICS".RENTAL_FACT ( RENTAL_DATE_KEY, RENTAL_TIMEOFDAY_KEY, RENTAL_ID, CUSTOMER_KEY, FILM_KEY, STAFF_KEY, STORE_LOCATION_KEY, RENTAL_QUANTITY, RENTAL_TOTAL_AMOUNT ) SELECT d_dim.DATE_KEY AS RENTAL_DATE_KEY, t_dim.TIMEOFDAY_KEY, r.rental_id, c_dim.CUSTOMER_KEY, f_dim.FILM_KEY, s_dim.staff_key, st_dim.store_location_key, 1 AS RENTAL_QUANTITY, p.amount AS RENTAL_TOTAL_AMOUNT FROM "MYDB"."PUBLIC"."RENTAL" r JOIN "MYDB"."PUBLIC"."INVENTORY" i ON r.inventory_id = i.inventory_id JOIN "MYDB"."SAKILA_ANALYTICS"."STORE_LOCATION_DIM" st_dim ON i.store_id = st_dim.store_id JOIN "MYDB"."PUBLIC"."PAYMENT" p ON p.rental_id = r.rental_id JOIN "MYDB"."SAKILA_ANALYTICS"."CUSTOMER_DIM" c_dim ON r.CUSTOMER_ID = c_dim.customer_id JOIN "MYDB"."SAKILA_ANALYTICS"."STAFF_DIM" s_dim ON r.staff_id = s_dim.staff_id JOIN "MYDB"."SAKILA_ANALYTICS"."FILM_DIM" f_dim ON i.film_id = f_dim.film_id JOIN "MYDB"."SAKILA_ANALYTICS"."DATE_DIM" d_dim ON DATE(r.rental_date) = d_dim.date JOIN "MYDB"."SAKILA_ANALYTICS"."TIMEOFDAY_DIM" t_dim ON HOUR(r.rental_date) = t_dim.HOUR_24;

You can also refer to the following screenshot for rental_fact:

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