NOTE: I'm having trouble (or Skillshare is) with formatting my text and tables - the Skillshare page layout was breaking and it was truncating my last table. And it doesn't seem to like double-spaces. So those triple slashes you see have been added to fix the problem. But the last table of data doesn't contain all the results.
///
Here are my answers to the challenge questions. I had a harder time using JOINs than I did using WHERE statements; WHERE/AND/OR make more logical sense to me, I guess, and I had a hard time stringing JOIN statements together like WHERE/AND (and I don't know if it's possible or not).
If anyone sees any errors or has suggestions on how to improve these, I'd love to get your feedback!
///
CHALLENGE QUESTION #1
Who were our best 10 customers in the last three months of the year?
I chose to use revenue and not number of orderes as my measure for "best customer." I made the assumption that "last the months of the year" meant last three months of last year, or October 1 - December 31, 2012.
Query:
SELECT customer_name, SUM(movie_price) AS revenue
FROM order_data AS od, customer_data AS cd, movie_data AS md
WHERE od.customer_id = cd.customer_id
AND od.movie_id = md.movie_id
AND datetime > '2012-10-01'
GROUP BY customer_name
ORDER BY revenue DESC
LIMIT 0, 10;
Data:
customer_name |
revenue |
George Holm |
26.89 |
Larry Kozikowski |
13.9 |
Amish Haffner |
10.94 |
Sarah Curran |
10.93 |
Natasha Simpson |
9.97 |
Jan Smith |
9.95 |
Jeff Southern |
9.94 |
Rick Haffner |
9.93 |
Lindsey Prentice |
9.93 |
Rebecca Davis |
9.91 |
///
CHALLENGE QUESTION #2
What is the worst-selling movie on our website?
Logically, this one was pretty straight-forward.
Query:
SELECT movie_title, COUNT(*) as sales
FROM order_data AS od, movie_data AS md
WHERE od.movie_id = md.movie_id
GROUP BY movie_title
ORDER BY sales ASC
LIMIT 0,1;
Data:
movie_title |
sales |
Patton |
32 |
///
CHALLENGE QUESTION #3
Summarize the total number of purchases by price; do you think price makes a difference in order behavior?
Clearly, price does have an influence on purchase behavior - in particular at the upper and lower bounds.
Query:
SELECT movie_price, COUNT(*) as orders
FROM movie_data AS md, order_data AS od
WHERE od.movie_id = md.movie_id
GROUP BY movie_price
ORDER BY orders DESC;
Data:
movie_price |
orders |
0.99 |
2238 |
1.99 |
1399 |
3.99 |
1044 |
5.99 |
319 |
///
CHALLENGE QUESTION #4
Summarize sales on our website by hour of the day, i.e., $X of movies were sold between 12 and 1AM, $Y between 1 and 2AM, and so on.
I thought it was interesting that there was very little variance based on time of day. Depending on whether your customers were more local or international, I would guess you would see more variance with real data. Also, I didn't recall HOUR(datetime) being covered in the class, but it worked similarly (as one would expect) as MONTH(datetime).
Query:
SELECT HOUR(datetime), SUM(movie_price)
FROM movie_data AS md, order_data AS od
WHERE md.movie_id = od.movie_id
GROUP BY HOUR(datetime);
Data:
HOUR(datetime) |
SUM(movie_price) |
0 |
518.800000000001 |
1 |
508.610000000001 |
2 |
466.930000000001 |
3 |
473.960000000001 |
4 |
335.140000000001 |
5 |
450.020000000001 |
6 |
505.640000000001 |
7 |
438.970000000001 |
8 |
481.830000000001 |
9 |
424.020000000001 |
10 |
448.080000000001 |
11 |
400.080000000001 |
12 |
511.800000000001 |
13 |
472.870000000001 |
14 |
473.870000000001 |
15 |
382.130000000001 |
16 |
435.900000000001 |
17 |
452.930000000001 |
18 |
462.020000000001 |
19 |
510.740000000001 |
20 |
454.930000000001 |
21 |
472.020000000001 |
22 |
488.850000000001 |
23 |
505.860000000001 |
///
CHALLENGE QUESTION #5
What is the average price of a movie order across each month of the year?
I first ran a query grouped by YEAR(datetime) to see how many years were in the database. I'm not really sure why people are purchasing more expensive movies in Q1 of each year.
Query:
SELECT YEAR(datetime), MONTH(datetime), AVG(movie_price)
FROM movie_data AS md, order_data AS od
WHERE md.movie_id = od.movie_id
GROUP BY YEAR(datetime), MONTH(datetime);
Data:
YEAR(datetime) |
MONTH(datetime) |
AVG(movie_price) |
2011 |
1 |
5.36950138504156 |
2011 |
2 |
3.36293729372938 |
2011 |
3 |
3.02804347826088 |
2011 |
4 |
2.79836236933799 |
2011 |
5 |
1.40042345276874 |
2011 |
6 |
1.94789473684211 |
2011 |
7 |
1.83527220630373 |
2011 |
8 |
1.10309523809524 |
2011 |
9 |
0.990000000000004 |
2011 |
10 |
1.07881578947369 |
2011 |
11 |
1.14037593984963 |
2011 |
12 |
1.92910256410257 |
2012 |
1 |
5.41857142857143 |
2012 |
2 |
3.28166666666667 |
2012 |
3 |
2.92548387096775 |
2012 |
4 |
2.82146067415731 |
2012 |
5 |
1.43943820224719 |
2012 |
6 |
1.87571428571429 |
2012 |
7 |
1.9 |
2012 |
8 |
1.07653846153846 |
2012 |
9 |
0.989999999999999 |
2012 |
10 |
1.06692307692308 |
2012 |
11 |
1.24471698113207 |
2012 |
12 |
2.01941176470589 |
///
CHALLENGE QUESTION #6
How many times was each individual movie ordered in each month of each year? I.e., Amadeus was ordered twice in 1/2011, three times in 2/2011, etc.
I *think* this solution is correct. If anyone has a better solution, please let me know!
Query:
SELECT movie_title, YEAR(datetime), MONTH(datetime), COUNT(*)
FROM order_data AS od, movie_data AS md
WHERE od.movie_id = md.movie_id
GROUP BY movie_title, YEAR(datetime), MONTH(datetime)
ORDER BY movie_title;
Data:
movie_title |
YEAR(datetime) |
MONTH(datetime) |
COUNT(*) |
A Beautiful Mind |
2011 |
5 |
120 |
A Beautiful Mind |
2012 |
5 |
36 |
Amadeus |
2011 |
10 |
73 |
Amadeus |
2012 |
10 |
24 |
American Beauty |
2011 |
5 |
69 |
American Beauty |
2011 |
6 |
30 |
American Beauty |
2011 |
7 |
1 |
American Beauty |
2012 |
5 |
15 |
American Beauty |
2012 |
6 |
16 |
Annie Hall |
2011 |
11 |
3 |
Annie Hall |
2011 |
12 |
36 |
Annie Hall |
2012 |
11 |
8 |
Annie Hall |
2012 |
12 |
17 |
Braveheart |
2011 |
7 |
92 |
Braveheart |
2012 |
7 |
29 |
Chariots of Fire |
2011 |
11 |
61 |
Chariots of Fire |
2011 |
12 |
2 |
Chariots of Fire |
2012 |
11 |
14 |
Chariots of Fire |
2012 |
12 |
1 |
Chicago |
2011 |
4 |
108 |
Chicago |
2011 |
5 |
3 |
Chicago |
2012 |
4 |
32 |
Chicago |
2012 |
5 |
1 |
Crash |
2011 |
3 |
104 |
Crash |
2012 |
3 |
41 |
Dances with Wolves |
2011 |
8 |
108 |
Dances with Wolves |
2012 |
8 |
31 |
Driving Miss Daisy |
2011 |
8 |
7 |
Driving Miss Daisy |
2011 |
9 |
86 |
Driving Miss Daisy |
2011 |
10 |
3 |
Driving Miss Daisy |
2012 |
8 |
2 |
Driving Miss Daisy |
2012 |
9 |
28 |
Forrest Gump |
2011 |
7 |
117 |
Forrest Gump |
2012 |
7 |
35 |
Gandhi |
2011 |
10 |
27 |
Gandhi |
2011 |
11 |
37 |
Gandhi |
2011 |
12 |
2 |
Gandhi |
2012 |
10 |
8 |
Gandhi |
2012 |
11 |
19 |
Gladiator |
2011 |
5 |
109 |
Gladiator |
2012 |
5 |
36 |
Kramer vs. Kramer |
2011 |
11 |
52 |
Kramer vs. Kramer |
2011 |
12 |
2 |
Kramer vs. Kramer |
2012 |
11 |
25 |
Kramer vs. Kramer |
2012 |
12 |
2 |
LOTR: Return of the King |
2011 |
4 |
116 |
LOTR: Return of the King |
2011 |
5 |
4 |
LOTR: Return of the King |
2012 |
4 |
37 |
LOTR: Return of the King |
2012 |
5 |
1 |
Million Dollar Baby |
2011 |
3 |
49 |
Million Dollar Baby |
2011 |
4 |
63 |
Million Dollar Baby |
2011 |
5 |
2 |
Million Dollar Baby |
2012 |
3 |
25 |
Million Dollar Baby |
2012 |
4 |
20 |
No Country For Old Men |
2011 |
2 |
95 |
No Country For Old Men |
2011 |
3 |
24 |
No Country For Old Men |
2012 |
2 |
34 |
One Flew Over the Cuckoo's Nest |
2011 |
12 |
48 |
One Flew Over the Cuckoo's Nest |
2012 |
12 |
24 |
Ordinary People |
2011 |
11 |
60 |
Ordinary People |
2012 |
11 |
23 |
Out of Africa |
2011 |
10 |
77 |
Out of Africa |
2012 |
10 |
27 |
Patton |
2011 |
12 |
23 |
Patton |
2012 |
12 |
9 |
Platoon |
2011 |
9 |
28 |
Platoon |
2011 |
10 |
44 |
Platoon |
2012 |
9 |
10 |
Platoon |
2012 |
10 |
21 |
Rain Man |
2011 |
9 |
83 |
Rain Man |
2011 |
10 |
6 |
Rain Man |
2012 |
9 |
26 |
Rain Man |
2012 |
10 |
1 |
Rocky |
2011 |
12 |
45 |
Rocky |
2012 |
12 |
24 |
Schindlers List |
2011 |
7 |
83 |
Schindlers List |
2011 |
8 |
38 |
Schindlers List |
2012 |
7 |
24 |
Schindlers List |
2012 |
8 |
9 |
Shakespeare in Love |
2011 |
6 |
119 |
Shakespeare in Love |
2011 |
7 |
4 |
Shakespeare in Love |
2012 |
6 |
40 |
Slumdog Millionaire |
2011 |
2 |
125 |
Slumdog Millionaire |
2011 |
3 |
6 |
Slumdog Millionaire |
2012 |
2 |
34 |
Slumdog Millionaire |
2012 |
3 |
1 |
Terms of Endearment |
2011 |
10 |
72 |
Terms of Endearment |
2012 |
10 |
22 |
The Artist |
2011 |
1 |
126 |
The Artist |
2012 |
1 |
41 |
The Deer Hunter |
2011 |
11 |
53 |
The Deer Hunter |
2011 |
12 |
2 |
The Deer Hunter |
2012 |
11 |
17 |
The Deer Hunter |
2012 |
12 |
1 |
The Departed |
2011 |
3 |
172 |
The Departed |
2012 |
3 |
55 |
The English Patient |
2011 |
6 |
45 |
The English Patient |
2011 |
7 |
51 |
The English Patient |
2012 |
6 |
18 |
The English Patient |
2012 |
7 |
11 |
///
CHALLENGE QUESTION #7
Show off! What is the most interesting insight you can draw from the database?
I thought it would be interesting to see which customers purchased the higher priced movies and which purchased only the lower priced movies. In the real world, you could use this data to filter recommendations by price, or suggest higher-priced movies to customers who were less price sensitive. Unfortunately, the data shows that customers pretty much behave the same when it comes to pricing. My guess is that there would be more variance in a real data set of customer behavior.
Here was my challenge: Summarize the # of orders by price for each customer
You could throw this data in a pivot table and get a better summary.
Query:
SELECT customer_name, movie_price, COUNT(movie_title)
FROM order_data AS od, movie_data AS md, customer_data AS cd
WHERE md.movie_id = od.movie_id
AND cd.customer_id = od.customer_id
GROUP BY customer_name, movie_price
ORDER BY customer_name, movie_price;
Data:
Note: This is just a sample of the data, because Skillshare is choking on the entire table...
customer_name |
movie_price |
COUNT(movie_title) |
Alicia Fisher |
0.99 |
28 |
Alicia Fisher |
1.99 |
16 |
Alicia Fisher |
3.99 |
11 |
Alicia Fisher |
5.99 |
9 |
Alicia Smith |
0.99 |
14 |
Alicia Smith |
1.99 |
17 |
Alicia Smith |
3.99 |
6 |
Alicia Smith |
5.99 |
7 |
Alicia Weller |
0.99 |
25 |
Alicia Weller |
1.99 |
12 |
Alicia Weller |
3.99 |
13 |
Alicia Weller |
5.99 |
1 |
Amish Fisher |
0.99 |
35 |
Amish Fisher |
1.99 |
19 |
Amish Fisher |
3.99 |
17 |
Amish Fisher |
5.99 |
5 |
Amish Haffner |
0.99 |
19 |
Amish Haffner |
1.99 |
15 |
Amish Haffner |
3.99 |
14 |
Amish Haffner |
5.99 |
1 |
Amish Heitzmann |
0.99 |
21 |
Amish Heitzmann |
1.99 |
9 |
Amish Heitzmann |
3.99 |
13 |
Amish Heitzmann |
5.99 |
4 |
Amish Prentice |
0.99 |
21 |
Amish Prentice |
1.99 |
12 |
Amish Prentice |
3.99 |
9 |
Amish Prentice |
5.99 |
3 |
Carol Haffner |
0.99 |
26 |
Carol Haffner |
1.99 |
16 |
Carol Haffner |
3.99 |
8 |
Carol Haffner |
5.99 |
5 |
Carol Holm |
0.99 |
20 |
Carol Holm |
1.99 |
16 |
Carol Holm |
3.99 |
16 |
Carol Holm |
5.99 |
4 |
Chris Chen |
0.99 |
22 |
Chris Chen |
1.99 |
14 |
Chris Chen |
3.99 |
6 |
Chris Chen |
5.99 |
2 |
///
///
Comments