Repository navigation
Expand file tree
/
Copy path04_functions.sql
More file actions
112 lines (102 loc) · 3.4 KB
/
Copy path04_functions.sql
File metadata and controls
112 lines (102 loc) · 3.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
-- left(string, character_count) function
-- Find out how many rentals each month has
select left(r.rental_date, 7), count(r.rental_id)
from rental r
group by 1
order by 2 desc;
-- First and last rental date of each movie
select f.title as title, min(r.rental_date) as first_rental,
max(r.rental_date) as last_rental,
count(r.rental_id) as rental_count
from film f, rental r, inventory i
where r.inventory_id = i.inventory_id and f.film_id = i.film_id
group by i.film_id
order by 4 desc;
-- Customers who haven't rent a movie in the last month
select c.customer_id as id,
concat(c.first_name, " ", c.last_name) as name,
c.email as email,
left(max(r.rental_date), 7) as last_active_mon
from customer c, rental r
where c.customer_id = r.customer_id
and 5 < '2006-02'
group by 1;
-- Revenue by month
select left(p.payment_date, 7) as month,
sum(p.amount) as revenue,
count(p.rental_id) as rental_count
from payment p
group by 1
order by 1;
-- Distinc keyword
-- How many distinct renters per month
select left(r.rental_date, 7) as month,
count(r.rental_id) as rental_count,
count(distinct r.customer_id) as unique_customers,
count(r.rental_id) / count(distinct r.customer_id) as avg_rentals_per_renter
from rental r
group by 1;
-- Num of unique films rented each month
select left(r.rental_date, 7) as month,
count(r.rental_id) as rental_count,
count(distinct i.film_id) as unique_films,
count(r.rental_id) / count(distinct i.film_id) as avg_rentals_per_film
from rental r, inventory i
where r.inventory_id = i.inventory_id
group by 1;
-- IN() function
-- Num of rentals in Comedy, Sports, and Family category
select left(r.rental_date, 7) as month,
c.name as category,
count(r.rental_id) as rental_count
from rental r, inventory i, film_category fc, category c
where r.inventory_id = i.inventory_id
and i.film_id = fc.film_id
and fc.category_id = c.category_id
and c.name in ("Comedy", "Sports", "Family")
group by 1, fc.category_id
order by 1 desc;
-- Comparison operators (>, =)
-- >, <, >=, <=, =, not equal to: <>, !=
select c.name as category,
count(r.rental_id) as rental_count
from rental r, inventory i, film_category fc, category c
where r.inventory_id = i.inventory_id
and i.film_id = fc.film_id
and fc.category_id = c.category_id
and c.name != "Comedy"
group by 1;
-- Customers who have rented at least 3 times
-- Using function with having clause
select c.customer_id as id,
concat(c.first_name, " ", c.last_name) as name,
count(r.rental_id) as rental_count
from customer c, rental r
where c.customer_id = r.customer_id
group by 1
having count(r.rental_id) > 3;
-- Revenue for store 1 from films rated as R and PG-13
select i.store_id as store_id,
f.rating as rating,
sum(p.amount) as total_revenue
from payment p, rental r, inventory i, film f
where p.rental_id = r.rental_id
and r.inventory_id = i.inventory_id
and i.film_id = f.film_id
and i.store_id = 1
and f.rating in ("R", "PG-13")
group by 2
order by 3 desc;
-- Limit date range
select i.store_id as store_id,
f.rating as rating,
sum(p.amount) as total_revenue
from payment p, rental r, inventory i, film f
where p.rental_id = r.rental_id
and r.inventory_id = i.inventory_id
and i.film_id = f.film_id
and i.store_id = 1
and f.rating in ("R", "PG-13")
and p.payment_date BETWEEN "2005-06-08" and "2005-07-09"
group by 2
order by 3 desc;