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:
customers.email, COUNT(*) AS 2016_num_orders
customers on orders.customer_id = customers.customer_id
orders.order_placed_date >= '2016-01-01'
AND orders.order_placed_date <= '2016-12-31'
Check out what's happening above:
First, we use a
SELECT statement to pull specific fields from our database. We'll pull the
email field, which is located on 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
email field isn't located on 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!