Untitled

 avatar
unknown
plain_text
3 years ago
28 kB
15
Indexable
--customer 3055
DROP TABLE IF EXISTS bengisu.customer;
CREATE TABLE bengisu.customer(customer_id int,
                                 store_id int,
                                first_name varchar(200), 
                                last_name varchar(200),
                                email varchar(200),
                                address_id int,
                                activebool varchar(2), 
                                create_date timestamp,
                                last_update_date timestamp(6),
                                active int)
                                
ORDER BY customer_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.customer_rejected;                                
COPY bengisu.customer FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3055.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.customer_rejected;


--customer correct dates
DROP TABLE IF EXISTS bengisu.customer_correct_dates;
CREATE TABLE bengisu.customer_correct_dates(customer_id_new int,
                                 store_id int,
                                first_name varchar(200), 
                                last_name varchar(200),
                                email varchar(200),
                                address_id int,
                                activebool varchar(2), 
                                create_date_new timestamp,
                                last_update_date_new timestamp(6),
                                is_active_new varchar(1))
                                
ORDER BY customer_id_new unsegmented all nodes;
                            
COPY bengisu.customer_correct_dates FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3055.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1;


--actor 3057
DROP TABLE IF EXISTS bengisu.actor;
CREATE TABLE bengisu.actor(actor_id int, first_name varchar(200), last_name varchar(200), last_update timestamp(6))
                                
ORDER BY actor_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.actor_rejected;                         
COPY bengisu.actor FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3057.dat' PARSER fcsvparser(delimiter=',', type='traditional') skip 1 rejected data as table bengisu.actor_rejected;

--category 3059
DROP TABLE IF EXISTS bengisu.category;
CREATE TABLE bengisu.category(category_id int, name varchar(200), last_update timestamp(6))
                                
ORDER BY category_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.category_rejected;                         
COPY bengisu.category FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3059.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.category_rejected;


--film 3061
DROP TABLE IF EXISTS bengisu.movie;
CREATE TABLE bengisu.movie(movie_id int, 
                        title varchar(200), 
                        description varchar(200), 
                        release_year int, 
                        language_id int, 
                        rental_duration float, 
                        rental_rate float, 
                        length float, 
                        replacement_cost float, 
                        rating varchar(200), 
                        last_update timestamp(6), 
                        special_features varchar(200), 
                        fulltext varchar(500))
                                
ORDER BY movie_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.movie_rejected;                         
COPY bengisu.movie FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3061.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.film_rejected;

--film_actor 3062
DROP TABLE IF EXISTS bengisu.film_actor;
CREATE TABLE bengisu.film_actor(actor_id int, film_id int, last_update timestamp(6))
                                
ORDER BY film_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.filmactor_rejected;                         
COPY bengisu.film_actor FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3062.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.filmactor_rejected;

--movie_category 3063
DROP TABLE IF EXISTS bengisu.film_category;
CREATE TABLE bengisu.film_category(film_id int, category_id int, last_update timestamp(6))
                                
ORDER BY film_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.filmcat_rejected;                         
COPY bengisu.film_category FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3063.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.filmcat_rejected;

--address 3065
DROP TABLE IF EXISTS bengisu.address;
CREATE TABLE bengisu.address(address_id int, 
                                address varchar(200), 
                                address2 varchar(200), 
                                district varchar(200), 
                                city_id int, 
                                postal_code int, 
                                phone varchar(200), 
                                last_update timestamp(6))
                                
ORDER BY address_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.address_rejected;                         
COPY bengisu.address FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3065.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.address_rejected;

--country 3069
DROP TABLE IF EXISTS bengisu.country;
CREATE TABLE bengisu.country(country_id int, country varchar(200), last_update timestamp(6))
                                
ORDER BY country_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.country_rejected;                         
COPY bengisu.country FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3069.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.country_rejected;

--inventory 3071
DROP TABLE IF EXISTS bengisu.inventory;
CREATE TABLE bengisu.inventory(inventory_id int, film_id int, store_id int, last_update timestamp(6))
                                
ORDER BY inventory_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.inventory_rejected;                         
COPY bengisu.inventory FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3071.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.inventory_rejected;

--language 3073
DROP TABLE IF EXISTS bengisu.language;
CREATE TABLE bengisu.language(language_id int, name varchar(200), last_update timestamp(6))
                                
ORDER BY language_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.language_rejected;                         
COPY bengisu.language FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3073.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.language_rejected;

--payment 3075
DROP TABLE IF EXISTS bengisu.payment;
CREATE TABLE bengisu.payment(payment_id int, customer_id int, staff_id int, rental_id int, amount float, payment_date timestamp(6))
                                
ORDER BY payment_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.payment_rejected;                         
COPY bengisu.payment FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3075.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.payment_rejected;

--rental 3077
DROP TABLE IF EXISTS bengisu.rental;
CREATE TABLE bengisu.rental(rental_id int, 
                        rental_date timestamp, 
                        inventory_id int, 
                        customer_id int, 
                        return_date timestamp, 
                        staff_id int, 
                        last_update timestamp)
                                
ORDER BY rental_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.rental_rejected;                         
COPY bengisu.rental FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3077.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.rental_rejected;

--staff 3079
DROP TABLE IF EXISTS bengisu.staff;
CREATE TABLE bengisu.staff(staff_id int, 
                        first_name varchar(200), 
                        last_name varchar(200), 
                        address_id int, 
                        email varchar(200), 
                        store_id int, 
                        active varchar(2), 
                        username varchar(200), 
                        password varchar(200), 
                        last_update timestamp(6), 
                        picture varchar(200))
                                                        
ORDER BY staff_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.staff_rejected;                         
COPY bengisu.staff FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3079.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.staff_rejected;

--store 3081
DROP TABLE IF EXISTS bengisu.store;
CREATE TABLE bengisu.store(store_id int, manager_staff_id int, address_id int, last_update timestamp(6))
                                
ORDER BY store_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.store_rejected;                         
COPY bengisu.store FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3081.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.store_rejected;

--payment types
DROP TABLE IF EXISTS bengisu.payment_type;
CREATE TABLE bengisu.payment_type(payment_type_id int, payment_type_name varchar(20))
                                
ORDER BY payment_type_id unsegmented all nodes;
DROP TABLE IF EXISTS bengisu.payment_type_rejected;                         
COPY bengisu.payment_type FROM LOCAL '/Users/globalmaksimum/Downloads/dvdrental/3083.dat' PARSER fcsvparser(delimiter=';', type='traditional') skip 1 rejected data as table bengisu.store_rejected;

--select
SELECT * FROM bengisu.customer;
DROP TABLE bengisu.customer_rejected
SELECT * FROM bengisu.actor;
DROP TABLE bengisu.actor_rejected;
SELECT * FROM bengisu.category;
DROP TABLE bengisu.category_rejected;
SELECT * FROM bengisu.movie;
DROP TABLE bengisu.movie_rejected;
SELECT * FROM bengisu.movie_actor;
DROP TABLE bengisu.movie_actor_rejected;
SELECT * FROM bengisu.address;
DROP TABLE bengisu.address_rejected;
SELECT * FROM bengisu.city;
DROP TABLE bengisu.city_rejected;
SELECT * FROM bengisu.country;
DROP TABLE bengisu.country_rejected;
SELECT * FROM bengisu.inventory;
DROP TABLE bengisu.inventory_rejected;
SELECT * FROM bengisu.language;
DROP TABLE bengisu.language_rejected;
SELECT * FROM bengisu.payment;
DROP TABLE bengisu.payment_rejected;
SELECT * FROM bengisu.rental;
DROP TABLE bengisu.rental_rejected;
SELECT * FROM bengisu.staff;
DROP TABLE bengisu.staff_rejected;
SELECT * FROM bengisu.store;
DROP TABLE bengisu.store_rejected;
SELECT * FROM bengisu.payment_type;
DROP TABLE bengisu.payment_type_rejected;

--alter

SELECT * FROM bengisu.movie;

ALTER TABLE bengisu.movie
ADD subtitle_language_id_1 int;

ALTER TABLE bengisu.movie
ADD subtitle_language_id_2 int;

ALTER TABLE bengisu.movie
ADD subtitle_language_id_3 int;

UPDATE bengisu.movie
SET    subtitle_language_id_1 = abs(RANDOMINT(7))
WHERE  subtitle_language_id_1 IS NULL;

UPDATE bengisu.movie
SET    subtitle_language_id_1 = 7
WHERE  subtitle_language_id_1 = 0;

UPDATE bengisu.movie
SET    subtitle_language_id_2 = abs(RANDOMINT(7))
WHERE  subtitle_language_id_2 IS NULL;

UPDATE bengisu.movie
SET    subtitle_language_id_2 = 7
WHERE  subtitle_language_id_2 = subtitle_language_id_1 OR subtitle_language_id_2 = 0;

UPDATE bengisu.movie
SET    subtitle_language_id_3 = abs(RANDOMINT(7))
WHERE  subtitle_language_id_3 IS NULL;

UPDATE bengisu.movie
SET    subtitle_language_id_3 = 7
WHERE  subtitle_language_id_3 = subtitle_language_id_2 OR subtitle_language_id_3 = subtitle_language_id_1 OR subtitle_language_id_3 = 0;

ALTER TABLE bengisu.movie
DROP COLUMN subtitle1

ALTER TABLE bengisu.movie
DROP COLUMN subtitle2

ALTER TABLE bengisu.movie
DROP COLUMN subtitle3

UPDATE bengisu.rental
SET    staff_id = abs(RANDOMINT(5))

UPDATE bengisu.rental
SET    staff_id = 4
WHERE staff_id = 0

SELECT * FROM bengisu.rental

ALTER TABLE bengisu.staff
RENAME COLUMN active TO is_active
UPDATE bengisu.staff
SET is_active = 1
WHERE is_active = 't'
SELECT * FROM bengisu.payment

ALTER TABLE bengisu.customer
RENAME COLUMN active TO is_active
SELECT * FROM bengisu.customer

ALTER TABLE bengisu.customer
DROP COLUMN activebool

ALTER TABLE bengisu.payment
ADD payment_type_id int;

UPDATE bengisu.payment
SET    payment_type_id = abs(RANDOMINT(5))
WHERE payment_type_id IS NULL

ALTER TABLE bengisu.inventory
RENAME COLUMN film_id TO movie_id

ALTER TABLE bengisu.movie_actor
RENAME COLUMN film_id TO movie_id

ALTER TABLE bengisu.movie
RENAME COLUMN category_id TO movie_category_id;

ALTER TABLE bengisu.movie
RENAME COLUMN film_id TO movie_id

UPDATE bengisu.movie
SET    movie_category_id = category_id
FROM bengisu.movie_category
WHERE film_id = movie_id ;
SELECT * FROM bengisu.movie

DROP TABLE bengisu.movie_category
DROP TABLE bengisu.movie_category_rejected

ALTER TABLE bengisu.staff
DROP COLUMN is_active

ALTER TABLE bengisu.staff
DROP COLUMN last_update

ALTER TABLE bengisu.staff
DROP COLUMN picture

ALTER TABLE bengisu.staff
ADD is_active int;

UPDATE bengisu.staff
SET is_active = 1
WHERE is_active IS NULL

ALTER TABLE bengisu.movie
ADD CC_status int;


UPDATE bengisu.movie
SET    CC_status = abs(RANDOMINT(2))
WHERE CC_status IS NULL

UPDATE bengisu.payment p
SET payment_date = rental_date
FROM bengisu.rental r
WHERE r.rental_id = p.rental_id
SELECT * FROM bengisu.payment

UPDATE bengisu.movie
SET release_year = FLOOR(RANDOM() * (2005 - 1999 + 1) + 1999)
WHERE release_year = 2006
SELECT * FROM bengisu.movie
SELECT * FROM bengisu.year_category_film

UPDATE bengisu.payment
SET staff_id = FLOOR(RANDOM() * (4 - 1 + 1) + 1)
SELECT * FROM bengisu.payment

SELECT COUNT(*),customer_id FROM bengisu.customer GROUP BY 2 HAVING COUNT(*) >1
SELECT customer_id FROM bengisu.customer ORDER BY 1 DESC

UPDATE bengisu.customer
SET create_date = last_update_date
FROM (SELECT COUNT(*) as num_of_id,customer_id as cid FROM bengisu.customer GROUP BY 2 HAVING COUNT(*) >1)a
WHERE cid=customer_id

UPDATE bengisu.customer
SET create_date = create_date_new
FROM bengisu.customer_correct_dates
WHERE is_active = 0  

UPDATE bengisu.customer
SET last_update_date = TIMESTAMPADD (DAY, 7, (last_update_date))
FROM (SELECT COUNT(*) as num_of_id,customer_id as cid FROM bengisu.customer GROUP BY 2 HAVING COUNT(*) >1)a
WHERE is_active = 1 AND cid = customer_id

SELECT * FROM bengisu.customer limit 10


--joins

--CHECK WHEATER DUPLICATE ACTORS
SELECT first_name, last_name, count(*) FROM bengisu.actor group by 1, 2 having count(*) >1;
SELECT * FROM bengisu.movie_actor where actor_id = 110;

--RENTAL NEW (BIG) TABLE FOR PUBLISHER & CHECK NULL
SELECT * FROM (
DROP TABLE IF EXISTS bengisu.rental_new;
CREATE TABLE bengisu.rental_new as (
SELECT r.rental_id, 
        r.inventory_id,
        i.movie_id as movie_id,
        f.title,
        f.release_year,
        f.rating,
        f.length as "duration(min)",
        f.rental_rate,
        l.name as language_name,
        cat.name as category_name,
        listagg(act.first_name || ' ' ||act.last_name using parameters seperator=',' , max_length=65000)::varchar as actor_name,
        i.store_id as inventory_store_id,
        p.payment_id,
        r.customer_id,
        cos.store_id as customer_store_id,
        cos.first_name as customer_first_name,
        cos.last_name as customer_last_name,
        cos.first_name || ' ' ||cos.last_name as customer_name,
        cos.is_active,
        a.city_id,
        a.district as district_id,
        a.phone as phone_num,
        r.staff_id,
        s.first_name as staff_first_name,
        s.last_name as staff_last_name,
        s.first_name ||' ' || s.last_name as staff_name,
        s.store_id as staff_store_id,
        s.is_active as staff_stat_id,
        p.payment_date,
        p.amount,
        f.replacement_cost,
        f.special_features,
        p.payment_type_id,
        pt.payment_type_name,
        case when p.payment_id is null then 'Rent for Free' else 'Normal Rental' end as rental_type
FROM bengisu.rental r
LEFT JOIN bengisu.payment p on p.rental_id = r.rental_id
LEFT JOIN bengisu.payment_type pt on p.payment_type_id = pt.payment_type_id
LEFT JOIN bengisu.staff s on r.staff_id = s.staff_id
LEFT JOIN bengisu.store st on st.store_id = s.store_id
LEFT JOIN bengisu.address a on a.address_id = s.address_id
LEFT JOIN bengisu.city c on c.city_id = a.city_id
LEFT JOIN bengisu.country ct on ct.country_id = c.country_id
LEFT JOIN bengisu.inventory i on i.inventory_id = r.inventory_id
LEFT JOIN bengisu.movie f on f.movie_id = i.movie_id
LEFT JOIN bengisu.category cat on cat.category_id = f.movie_category_id
LEFT JOIN bengisu.language l on l.language_id = f.language_id
LEFT JOIN bengisu.movie_actor fact on fact.movie_id = f.movie_id
LEFT JOIN bengisu.actor act on act.actor_id = fact.actor_id
LEFT JOIN bengisu.customer cos on r.customer_id = cos.customer_id
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22,23,24,25,26,27,28,29,30,31,32,33,34)
order by rental_id unsegmented all nodes;
) a
where payment_id is null

SELECT COUNT(DISTINCT rental_id) FROM bengisu.rental;

SELECT COUNT(1), language_id FROM movie GROUP BY 2; --need to add new languages
SELECT COUNT(*),customer_id FROM bengisu.customer GROUP BY 2 HAVING COUNT(*) >1; --there are updated records in customer

ALTER TABLE BENGISU.rental_new RENAME TO rental_big;

SELECT COUNT(*),rental_type from bengisu.rental_big group by 2; --analyze rental types

--CHECK IF DATES ARE IN CORRECT FORM: THEY ARE NOT, NEED TO EDIT
SELECT * FROM(
SELECT r.rental_id,
        r.payment_date,
        p.rental_date,
        p.return_date
 FROM bengisu.rental_big r
LEFT JOIN bengisu.rental p
ON p.rental_id = r.rental_id)a
WHERE payment_date IS NOT NULL

--RELEASE YEARS AND CORRESPONDING CATEGORIES OF THE MOVIES
DROP TABLE IF EXISTS year_category_film;
CREATE TABLE year_category_film as(
SELECT release_year, category_name, COUNT(*) FROM bengisu.rental_big GROUP BY 1,2 ORDER BY 1,2)
SELECT * FROM year_category_film

--RENTAL COUNT BASED ON PAYMENT TYPE
DROP TABLE IF EXISTS rental_count_basedon_payment;
CREATE TABLE rental_count_basedon_payment as(
SELECT payment_type_name, COUNT(*) FROM bengisu.rental_new GROUP BY 1
)
SELECT * FROM rental_count_basedon_payment

UPDATE bengisu.rental_count_basedon_payment
SET payment_type_name = 'credit card'
WHERE payment_type_name IS NULL

SELECT * FROM rental_count_basedon_payment 

CREATE TABLE total_revenue_movies as (
SELECT title, SUM(amount) FROM bengisu.rental_new GROUP BY 1 ORDER BY 1)
SELECT * FROM total_revenue_movies

--MONTHLY REVENUE WRT PAYMENT TYPES
DROP TABLE IF EXISTS monthly_revenue_wrt_payment_type
CREATE TABLE monthly_revenue_wrt_payment_type as (
SELECT MONTH(payment_date), payment_type_name, SUM(amount) FROM bengisu.rental_new GROUP BY 1,2 ORDER BY 1,2
)
SELECT * FROM monthly_revenue_wrt_payment_type

UPDATE monthly_revenue_wrt_payment_type
SET MONTH = 1
WHERE MONTH IS NULL

UPDATE monthly_revenue_wrt_payment_type
SET SUM = 0
WHERE SUM IS NULL

--TOTAL REVENUE FROM CATEGORIES
DROP TABLE IF EXISTS total_revenue_categories
CREATE TABLE total_revenue_categories as (
SELECT category_name, round(SUM(amount),2) FROM bengisu.rental_new GROUP BY 1 ORDER BY 1)
SELECT * FROM total_revenue_categories

ALTER TABLE total_revenue_categories
RENAME COLUMN round TO total_revenue

--TOTAL REVENUE FROM ACTORS
CREATE TABLE total_revenue_actors as (

SELECT SPLIT_PART(actor_name, ',', row_number), round(SUM(amount),2) FROM bengisu.rental_new GROUP BY 1 ORDER BY 1)

--REVENUE PER STORE

DROP TABLE IF EXISTS revenue_per_store
CREATE TABLE revenue_per_store as (
SELECT customer_store_id, SUM(amount) FROM bengisu.rental_new GROUP BY 1 ORDER BY 2
)
SELECT * FROM revenue_per_store

ALTER TABLE bengisu.revenue_per_store
RENAME COLUMN SUM TO revenue_of_store

--INVENTORY COUNT PER STORE

DROP TABLE IF EXISTS inventory_movie_category
CREATE TABLE inventory_movie_category as (
SELECT category_name, inventory_store_id, COUNT(*) FROM bengisu.rental_new GROUP BY 1,2 ORDER BY 2
)
SELECT * FROM inventory_movie_category

ALTER TABLE bengisu.inventory_movie_category
RENAME COLUMN COUNT TO number_of_movies_for_this_category

--NUMBER OF RENTED MOVIES WRT CATEGORIES PER STORE
DROP TABLE IF EXISTS category_movie_revenue_per_store;
CREATE TABLE category_movie_revenue_per_store as (
SELECT category_name, customer_store_id, COUNT(*) FROM bengisu.rental_new GROUP BY 1,2 ORDER BY 1,2)
SELECT * FROM category_movie_revenue_per_store

ALTER TABLE bengisu.category_movie_revenue_per_store
RENAME COLUMN customer_store_id TO store_id

ALTER TABLE bengisu.category_movie_revenue_per_store
RENAME COLUMN COUNT TO total_number_of_rented

--NUMBER OF MOVIES RENTED PER STORE
DROP TABLE IF EXISTS movie_rented_per_store;
CREATE TABLE movie_rented_per_store as (
SELECT customer_store_id, title, COUNT(*) FROM bengisu.rental_new GROUP BY 1,2 ORDER BY 1,2)
SELECT * FROM movie_rented_per_store

ALTER TABLE bengisu.movie_rented_per_store
RENAME COLUMN total_rented_of_rented TO total_number_of_rented

--NUMBER OF RENTED MOVIES WRT THEIR CATEGORIES 
DROP TABLE IF EXISTS rental_per_store;
CREATE TABLE rental_per_store as (
SELECT title, category_name, COUNT(rental_id) FROM bengisu.rental_new GROUP BY 1,2 ORDER BY 1,2)
SELECT * FROM rental_per_store

ALTER TABLE bengisu.rental_per_store
RENAME COLUMN COUNT TO total_number_of_rented

--LOYALTY MANAGEMENT
DROP TABLE IF EXISTS loyalty_management;
CREATE TABLE loyalty_management as (
SELECT r.category_name, 
        COUNT(r.rental_id) as total_number_of_rented,
        r.staff_id,
        s.first_name || ' ' || s.last_name as staff_name
FROM bengisu.rental_new r
LEFT JOIN bengisu.staff s ON s.staff_id = r.staff_id
GROUP BY 1,3,4 
ORDER BY 4)
SELECT * FROM loyalty_management

--NUMBER OF RENTED MOVIES PER CATEGORIES
DROP TABLE IF EXISTS category_count_rented;
CREATE TABLE category_count_rented as (
SELECT category_name, 
        COUNT(rental_id) 
FROM bengisu.rental_new 
GROUP BY 1 
ORDER BY 1)
SELECT * FROM category_count_rented

ALTER TABLE bengisu.category_count_rented
RENAME COLUMN total_numberof_rented TO total_number_of_rented

SELECT COUNT(*) FROM bengisu.rental_new
SELECT COUNT(*),movie_id from bengisu.movie group by movie_id having count(*) >1


SELECT r.rental_id, r.rental_date, r.return_date, p.payment_date FROM bengisu.rental r LEFT JOIN bengisu.payment p ON p.rental_id = r.rental_id 

UPDATE bengisu.rental_new rn
SET payment_date = rental_date
FROM bengisu.rental r
WHERE payment_date IS NULL AND r.rental_id = rn.rental_id

--REVENUE FROM EACH ACTOR PER CATEGORY
DROP TABLE IF EXISTS revenue_from_each_actor
CREATE TABLE revenue_from_each_actor as(
SELECT a.first_name || ' ' || a.last_name as actor_name,
        ROUND(SUM(p.amount)) as rental_revenue_in_$,
        c.name as category_name    
FROM bengisu.payment p
LEFT JOIN bengisu.rental r ON r.rental_id = p.rental_id
LEFT JOIN bengisu.inventory i ON r.inventory_id = i.inventory_id
LEFT JOIN bengisu.movie m ON m.movie_id = i.movie_id
LEFT JOIN bengisu.category c ON c.category_id = m.movie_category_id
LEFT JOIN bengisu.movie_actor ma ON ma.movie_id = m.movie_id
LEFT JOIN bengisu.actor a ON ma.actor_id =  a.actor_id
GROUP BY 1,3
ORDER BY 1)
SELECT * FROM bengisu.revenue_from_each_actor

DELETE FROM bengisu.revenue_from_each_actor WHERE actor_name is null

--RENTAL DURATION PER STORES AND STAFF
DROP TABLE IF EXISTS rental_duration
CREATE TABLE rental_duration as(
SELECT c.name as category_name,
        r.customer_id,
        r.rental_date as start_rent,
        r.return_date as end_rent,
        TIMESTAMPDIFF(HOUR, r.rental_date, r.return_date) as rental_duration_as_hours,
        TIMESTAMPDIFF(DAY, r.rental_date, r.return_date) as rental_duration_as_days,
        to_char(r.rental_date,'DAY') as start_rent_day_of_week,
        to_char(r.return_date,'DAY') as end_rent_day_of_week,
        i.store_id,
        r.rental_id,
        r.staff_id
FROM bengisu.rental r
LEFT JOIN bengisu.inventory i ON i.inventory_id = r.inventory_id
LEFT JOIN bengisu.movie m ON m.movie_id = i.movie_id
lEFT JOIN bengisu.category c ON c.category_id = m.movie_category_id
GROUP BY 1,2,3,4,9,10,11
ORDER BY 1,2)

--TOP 10 RENTED ACTORS PER STORE
DROP TABLE IF EXISTS top10_actors_per_store
CREATE TABLE top10_actors_per_store as(
SELECT a.first_name || ' ' || a.last_name as actor_name,
        i.store_id,
        COUNT(rental_id) as number_of_rented_films      
FROM bengisu.rental r
LEFT JOIN bengisu.inventory i ON i.inventory_id = r.inventory_id
LEFT JOIN bengisu.movie m ON m.movie_id = i.movie_id
LEFT JOIN bengisu.movie_actor ma ON ma.movie_id = m.movie_id
LEFT JOIN bengisu.actor a ON a.actor_id = ma.actor_id
GROUP BY 1,2
ORDER BY 3 DESC)
SELECT * FROM bengisu.top10_actors_per_store ORDER BY 3 DESC limit 10 

--TOP 10 RENTED ACTOR PER STORE
DROP TABLE IF EXISTS top_movies_per_store
CREATE TABLE top_movies_per_store as(
SELECT r.title,
        COUNT(r.title) as sum_of_rent
FROM bengisu.rental_big r
GROUP BY 1
ORDER BY 2)

--WHICH ACTOR PLAYED ON WHICH KIND OF MOVIES AND THEIR NUMBER OF PORTRAYEL
DROP TABLE IF EXISTS actor_category_movie
CREATE TABLE actor_category_movie as(
SELECT a.first_name || ' ' || a.last_name as actor_name,
        c.name as category_name,
        COUNT(a.actor_id) count_of_portrayel,
        m.rating
FROM bengisu.actor a
LEFT JOIN bengisu.movie_actor ma ON a.actor_id = ma.actor_id
LEFT JOIN bengisu.movie m ON m.movie_id = ma.movie_id
LEFT JOIN bengisu.category c ON c.category_id = m.movie_category_id
GROUP BY 1,2,4
ORDER BY 1)
SELECT * FROM bengisu.actor_category_movie ORDER BY 1

--FRAUD DETECTION FOCUSING ON STAFFS AND PAYMENT TYPES
DROP TABLE IF EXISTS fraud_detection
CREATE TABLE fraud_detection as(
SELECT pt.payment_type_name,
        p.payment_date,
        s.first_name || ' ' || s.last_name as staff_name,
        s.staff_id,
        p.payment_id
FROM bengisu.payment p
LEFT JOIN bengisu.payment_type pt ON p.payment_type_id = pt.payment_type_id
LEFT JOIN bengisu.staff s ON p.staff_id = s.staff_id
GROUP BY 1,2,3,4,5
ORDER BY 1)

--"PAYMENT TYPE = FREE PROMOTION" PER STAFF
DROP TABLE IF EXISTS free_rental
CREATE TABLE free_rental as(
SELECT payment_type_name, COUNT(payment_id) as number_of_payments, MONTH(payment_date) as month_of_year, staff_name, staff_id
FROM bengisu.fraud_detection WHERE payment_type_name = 'free promotion' GROUP BY 1,3,4,5 ORDER BY 3 ASC)

--FREE GIVEAWAY COUNT PER STAFF
DROP TABLE IF EXISTS bengisu.staff_per_free_giveaway
CREATE TABLE staff_per_free_giveaway as(
SELECT staff_id,
        staff_name,
        SUM(number_of_payments) AS free_giveaway_count
FROM bengisu.free_rental
GROUP BY 1,2
ORDER BY 1)

ALTER TABLE  bengisu.staff_per_free_giveaway
ADD giveaway_rate_as_percent float; 

SELECT COUNT(payment_id) FROM bengisu.fraud_detection

UPDATE bengisu.staff_per_free_giveaway 
SET giveaway_rate_as_percent = ROUND((free_giveaway_count/14596)*100, 3)
WHERE giveaway_rate_as_percent IS NULL

SELECT * FROM bengisu.staff_per_free_giveaway 

--RENTAL COUNT & RATE PER STORE
SELECT store_id, COUNT(rental_id) as rental_count, ROUND((COUNT(rental_id)/16044)*100, 2) as rental_rate_per_store
FROM bengisu.rental_duration
GROUP BY 1
ORDER BY 1

--RENTAL COUNT & RATE PER STAFF
SELECT staff_id, COUNT(rental_id) as rental_count, (COUNT(rental_id)/16044)*100 as rental_rate_per_staff
FROM bengisu.rental_duration
GROUP BY 1
ORDER BY 1

--RENTAL COUNT & RATE PER CUSTOMER
SELECT customer_id, COUNT(rental_id) as rental_count, (COUNT(rental_id)/16044)*100 as rental_rate_per_staff
FROM bengisu.rental_duration
GROUP BY 1
ORDER BY 1
Editor is loading...