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