Done-zo | Skillshare Projects

2

--

# The “challenge questions”

Queries that answer very simple and important questions, but are challenging to write.

1. Who were our best 10 customers in the last three months of 2012?
George Holm - 7 movies in Dec 2012

SELECT customer_name, COUNT(datetime) FROM (SELECT customer_id, movie_id, datetime FROM order_data AS od WHERE datetime >'2012-12-01') AS dc INNER JOIN customer_data AS cd ON dc.customer_id = cd.customer_id GROUP BY customer_name ORDER BY COUNT(datetime) DESC LIMIT 10;

1. What is the worst-selling movie on our website?
THE STING

1. Summarize total number of purchases by price; do you think price make a difference in order behavior?
Yes  for .99cent movies, # of orders is 1907 vs. 541 for 5.99\$

1. Summarize sales on our website by hour of the day, i.e., \$X of movies were sold between 12 and 1 AM, \$Y between 1 and 2 AM, and so on.

SELECT HOUR(datetime), SUM(movie_price)

FROM order_data AS od

INNER JOIN movie_data AS md

ON od.movie_id = md.movie_id

GROUP BY HOUR(datetime);

1. What is the average price of a movie order across each month of the year?

Jan - 2.4

1. How many times was each individual movie ordered in each month of each year? I.e., Amadeus was ordered twice in in 1/2011, three times in 3/2011, etc.
A beautiful Mind - ordered 7 times in Jan

SELECT MONTH(datetime), AVG(movie_price) FROM order_data AS od INNER JOIN movie_data AS md ON od.movie_id = md.movie_id GROUP BY MONTH(datetime);

1. Show off! What is the most interesting insight you can draw from the database?

It’s strange that sales change very little based on time of day.  A lot of people are ordering movie at 3 in the morning...