Question 1: Cocoa Confections
Cocoa Confections is a small bakery that sells brownies, cookies, pies, and other delicious treats to customers online. It keeps records of all of its online sales in an SQL database that is automatically populated as customers place orders on its site.
In its database, Cocoa Confections has a
customers table to keep track of customer contact information, and an
orders table to keep track of various orders that those customers have placed. The schema of these tables is as follows:
CREATE TABLE customers ( customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, address VARCHAR(255) DEFAULT NULL, city VARCHAR(255) DEFAULT NULL, state VARCHAR(2) DEFAULT NULL, zip_code VARCHAR(5) DEFAULT NULL, ); CREATE TABLE orders ( order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, order_placed_date DATE NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) );
It's the end of 2016, and the owner of Cocoa Confections wants to write an SQL query that finds the
COUNT of orders placed in 2016 by customer e-mail address. She wants to
ORDER the results by the
COUNT of orders placed in 2016, descending, so that she can personally send thank-you e-mails to Cocoa Confection's top customers by order volume.
Can you write a query that will help the owner of Cocoa Confections find the
COUNT of all
orders placed in 2016, by customer e-mail address, sorted descending?
The owner of Cocoa Confections wants to find the
COUNT of orders placed by e-mail address. But the
customers table, not the
orders table. What type of statement do we need to use to combine the two tables together?
To combine our two tables together, we'll use a
JOIN. Since the
orders table has a
customer_id field, we can match that against the
customer_id in the
customers table to link up the two separate data sources. We'll use an
INNER JOIN so that data is only included if the
customer_id listed matches both of our input tables. Try the following to get you started — it'll compile a list of all orders in our database with all requisite customer information appended:
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
Now that we have a complete list of orders with e-mail addresses included, we can use the
GROUP BY functions to get a list of orders by e-mail address. This will return a
COUNT of all orders in our database by e-mail address. We'll also restrict our
SELECT clause so that it only pulls the e-mail address and the
COUNT of orders from our database, excluding all other fields.
SELECT email, COUNT(*) FROM orders INNER JOIN customers on orders.customer_id = customers.customer_id GROUP BY email; /* Results: +------------------------------+----------+ | email | COUNT(*) | +------------------------------+----------+ | email@example.com | 6 | | firstname.lastname@example.org | 16 | | email@example.com | 13 | ...100 rows in set (0.01 sec) */
We're not done yet! We still need to restrict the dates of our pull to 2016 only. How do we filter for particular results in SQL? Using a
WHERE clause! We'll also need to use an
ORDER BY clause to ensure that our results are sorted descending by number of orders. These two clauses, combined, should look something like this:
WHERE order_placed_date BETWEEN CAST('2016-01-01' AS DATE) AND CAST('2016-12-31' AS DATE) ORDER BY 2016_num_orders DESC;
Note that we need to use the
CAST function to ensure that our numerical inputs are properly interpreted as dates by MySQL.
Your solution will need to combine the
customers table with the
orders table. Does it do so?
To combine the
orders tables together, an
INNER JOIN query will be most effective. Does the solution you've created use an
INNER JOIN statement?
Don't forget that in order to find the total number of orders placed by e-mail address, you'll need to use SQL's
SUM functions, which also necessitate a
GROUP BY clause in your query. Does your solution contain those features?
Don't forget that we're only pulling orders from the year
2016, so we'll need to restrict our pull to that year only. We'll also need to arrange our results in descending order by total
COUNT. Have you got those features in your query?
Great! Sounds like you're on the right track, so let's move on to the solution.
Here's our full solution to the problem:
SELECT customers.email, COUNT(*) AS 2016_num_orders FROM orders INNER JOIN customers on orders.customer_id = customers.customer_id WHERE orders.order_placed_date BETWEEN CAST('2016-01-01' AS DATE) AND CAST('2016-12-31' AS DATE) GROUP BY customers.email ORDER BY 2016_num_orders DESC; /* Results: +------------------------------+-----------------+ | email | 2016_num_orders | +------------------------------+-----------------+ | firstname.lastname@example.org | 14 | | email@example.com | 10 | | firstname.lastname@example.org | 10 | ...99 rows in set (0.01 sec) */
Check out what's happening above:
First, we use a
SELECT statement to pull specific fields from our database. We'll pull the
customers table, and the aggregated
COUNT(*) of records, using the
AS clause to refer to this column as
We'll use a
FROM orders clause to note that we're pulling this data from our
orders table. But since the
orders table, we'll also need to perform an
INNER JOIN linking the
orders.customer_id field to the
We'll use a
WHERE clause to restrict our data range to 2016 only using two criteria:
>= '2016-01-01' and
Since we're using a
COUNT(*) function to sum records, we'll need to use a
GROUP BY clause to tell SQL that we want to group our results by
Finally, we'll tell SQL that we want our results arranged by number of orders, from greatest to least, using
Our query is complete!