My Answers to the Challenge Questions

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:

///

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:

 ///

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:

///

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:

///

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:

///

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:

///

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...

///

///