Untitled
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 1Editor is loading...