Dominate your SQL interview.

Nervous your SQL skills aren't up to snuff?
Sign up for a free 5-day course and walk into your SQL interview with confidence.

Enter your e-mail to receive a free 5-day course on acing your SQL interview

Or, explore our free content

As seen at:

Fast Company

Free SQL interview practice question

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(11) NOT NULL AUTO_INCREMENT,
	`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,
	PRIMARY KEY (`customer_id`)

CREATE TABLE `orders` (
	`order_id` int(11) NOT NULL AUTO_INCREMENT,
	`customer_id` int(11) NOT NULL,
	`order_placed_date` date NOT NULL,
	PRIMARY KEY (`order_id`),
	KEY `customer_id` (`customer_id`),
	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?

Hint: Combine tables

The owner of Cocoa Confections wants to find the COUNT of orders placed by e-mail address. But the email field is located within the customers table, not the orders table. What type of statement do we need to use to combine the two tables together?

Hint: Use an INNER JOIN

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:

	customers ON orders.customer_id = customers.customer_id;
Hint: Use COUNT(*)

Now that we have a complete list of orders with e-mail addresses included, we can use the COUNT(*) and 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.

	email, COUNT(*)
	customers on orders.customer_id = customers.customer_id
Hint: Use WHERE and ORDER BY

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:

	order_placed_date >= '2016-01-01'
	order_placed_date <= '2016-12-31'
	2016_num_orders DESC;
Does your answer combine tables?

Your solution will need to combine the customers table with the orders table. Does it do so?

Do you use an INNER JOIN?

To combine the customers and orders tables together, an INNER JOIN query will be most effective. Does the solution you've created use an INNER JOIN statement?

Does your answer count total orders?

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 COUNT(*) or SUM functions, which also necessitate a GROUP BY clause in your query. Does your solution contain those features?

Have you included results from only 2016 and ordered descending?

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?

The solution

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, COUNT(*) AS 2016_num_orders
		customers on orders.customer_id = customers.customer_id
		orders.order_placed_date >= '2016-01-01'
		orders.order_placed_date <= '2016-12-31'
		2016_num_orders DESC;

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

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 customers.customer_id field.

We'll use a WHERE clause to restrict our data range to 2016 only using two criteria: >= '2016-01-01' and <= '2016-12-31'.

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

Finally, we'll tell SQL that we want our results arranged by number of orders, from greatest to least, using ORDER BY.

Our query is complete!

Next question

Was this practice question helpful? Want to see more like it?
Sign up for a free 5-day e-mail course to brush up on your SQL skills.

Want to see what else Deskbright offers? Check out our collection of business analytics resources below.

Online Excel lessons

Spreadsheet skills are a prerequisite for many modern jobs. Our resources will help you get up to speed. Here's what we offer:

  • Excel video courses. Check out our professional video courses, with easy-to-follow instruction and live exercises.
  • Excel interview questions. Ace the interview with our comprehensive overview of Excel interview questions.
  • The Ultimate Excel Book. Learn spreadsheet secrets used by the pros — from the ground up.
  • Free online lessons. Check out our free series of online Excel lessons used by tens of thousands of visitors each month.
Tutorial screenshot

Uber analytics test prep

Uber, one of the world's fastest-growing companies, asks applicants for analytical positions to complete a rigorous Excel test before interviewing.

If you're interviewing at Uber, make sure you are qualified for the job and prepared for your interview with our Uber analytics test prep resources.

Learn more

Uber analytics guide cover