This SQL exercise is a part of a section dedicated for data analysts , providing data analysis tasks that require both business understanding and technical skills.
 

About this Exercise

FoodCart is a grocery store located in the US. in this exercise you’re to explore its Order Management System, and analyze data related to orders, items, and customers.

  • Orders – lists the current orders
  • Items – contains the different items of every order
  • Customers – lists the customers

 

Diagram



 

Download

The database creation script can be downloaded using the following link

 

Exercises

The following exercises are constructed in ascending difficulty order. First ones are pretty easy, made for warm-up. The advanced exercises, on the other hand, can be quite challenging.

 
Basic Analysis

  1. Display the customers distribution by gender in percentage (for example : 20% Males and 80% Females)
  2. Which customer has ordered the highest amount of items ?
  3. What is the average item price ?

 
Advanced Analysis

  1. What are the top-5 selling products?
  2. How many orders customer 4 has made, display it along with his/her gender?
  3. Active customer is a customer that ordered at least one item, list the non active male customers this grocery store has
  4. Display the customer who spent the highest amount of money
  5. Display the customers who purchased at least one of the products he/she made (using one query)
  6. Disply the product whose price is closest to the average item price
  7. What is the most profitable day of the week (where the quantity of products-sold is the highest)

 

Solutions