Description

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 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 );
(customer_id)
);
Can you write a query that will help the owner of Cocoa Confections find the
Sample Input:
Customers:
+—————–+—————+————–+———————+————+————-+——-+————+
| customer_id | first_name | last_name | email | address | city | state | zip_code |
+—————–+—————+————–+——————–+————+————-+——-+————-+
| 1 | abc | a | abc@mail.com | 1st street | chennai | TN | 60001 |
| 2 | efg | e | efg@mail.com | 2nd street | Bangalore | KA| 50002 |
| 3 | ijk | i | ijk@mail.com | 3nd street | Mumbai| MH | 40002 |
+——————+—————-+————+——————–+—————+———–+——-+———-+ Orders:
+———–+—————–+————————–+
| order_id | customer_id | order_placed_date |
+———–+—————–+————————–+
| 4 | 2 | 2015-06-01 |
| 5 | 1 | 2015-06-01 |
+———-+————-+——————————–+
Sample OutPut:
+——————–+—————–+
| email | order_count |
+——————–+—————–+
| abc@mail.com | 2 |
| efg@mail.com | 1 |
+——————–+—————–+