This End-to-End project show a cookies business by generating data for customers, orders, and cookies sold. The goal is to create a structured relational database for a bakery that tracks its sales performance and customer behavior. This project aims to showcase my skills and tools used and provide insightful data analysis like sales trends, repeat customer behavior, and forecasts based on historical data.
You can check the full dashboard: PowerBI_Cookies
The following SQL queries were used to build and populate a fictional Cookies Business Database consisting of four main tables: Cookies, Customers, Orders, and Order_Details.
Schema and Data: You can check the creation and importation of the data to the Database Engine (SQL Server Management Studio) Click here
- customer_id (Primary Key)
- name
- address
- order_id (Primary Key)
- customer_id (Foreign Key references Customers)
- order_date
- order_id (Foreign Key references Orders)
- cookie_name
- quantity
- total_price
- cookie_id (Primary Key)
- name
- description
- price
SQL Queries for Data Insertion: A Python script was used to generate 1,000 fictional customers, orders, and corresponding order details. Used Chatgpt to generate the python script.
- Handling Missing Values: Ensuring there are no NULL values in critical fields (e.g., customer_id, order_id).
- Standardizing Data Formats: Ensuring dates are in the correct format (ISO format).
- Removing Duplicates: Using DISTINCT to avoid duplicate records in the analysis.
- Data Transformation: Using functions like SUM, COUNT, ROUND, and aggregating data to calculate totals and averages.
- Conditional Logic: Utilizing CASE statements to categorize data (e.g., determining month names).
- Joining Tables: Using JOIN operations to combine data from different tables (e.g., linking Orders and Order_Details).
Subqueries used for aggregating data and filtering results based on specific criteria and Common Table Expressions (CTEs) to simplify complex queries and organize logic making SQL code more readable and maintainable.
--Sale SELECT c.name, SUM(od.total_price) AS total_sale FROM Order_Details od LEFT JOIN Cookies c ON od.cookie_id = c.cookie_id GROUP BY c.name ORDER BY total_sale DESC | name | total_sale |
|---|---|
| Double Chocolate | 4060.00 |
| Peanut Butter | 3827.25 |
| Chocolate Chip | 3121.50 |
| Oatmeal Raisin | 2498.75 |
| Sugar Cookie | 2172.00 |
--Order SELECT c.name, SUM(od.quantity) AS highest_order_cookie FROM Order_Details od LEFT JOIN Cookies c ON od.cookie_id = c.cookie_id GROUP BY c.name ORDER BY highest_order_cookie DESC | name | highest_order_cookie |
|---|---|
| Peanut Butter | 2187 |
| Sugar Cookie | 2172 |
| Chocolate Chip | 2081 |
| Double Chocolate | 2030 |
| Oatmeal Raisin | 1999 |
Analyze cookie product sales to determine top cookie sale and highest order
SELECT SUM(od.quantity) AS total_order_for_the_day, o.order_date, SUM(od.total_price) as total_sale FROM Order_Details od LEFT JOIN Orders o ON od.order_id = o.order_id GROUP BY o.order_date ORDER BY o.order_date ASC | total_order_for_the_day | order_date | total_sale |
|---|---|---|
| 7 | 2023-10-01 | 10.50 |
| 12 | 2023-10-03 | 15.25 |
| 36 | 2023-10-04 | 57.00 |
| 20 | 2023-10-06 | 20.00 |
| 17 | 2023-10-07 | 21.00 |
| 57 | 2023-10-08 | 86.25 |
| 38 | 2023-10-11 | 70.50 |
| 9 | 2023-10-12 | 31.50 |
| 21 | 2023-10-15 | 31.50 |
| 107 | 2023-10-16 | 140.50 |
| 6 | 2023-10-17 | 7.50 |
| 163 | 2023-10-19 | 244.50 |
| 46 | 2023-10-20 | 60.50 |
| 60 | 2023-10-21 | 105.25 |
| 28 | 2023-10-22 | 33.50 |
| 23 | 2023-10-23 | 41.50 |
| 107 | 2023-10-25 | 158.00 |
| 26 | 2023-10-26 | 62.00 |
| 39 | 2023-10-28 | 46.25 |
Visualizes sales performance trends across the year helping to track growth or decline.
WITH repeat_order AS ( SELECT customer_id, COUNT(*) AS repeat_orders FROM Orders GROUP BY customer_id HAVING COUNT(*) > 1 ) SELECT COUNT(repeat_orders) AS total_repeat_order, (SELECT COUNT(customer_id) FROM Orders) AS total_orders, CAST(COUNT(repeat_orders) AS DECIMAL(10, 2)) / CAST((SELECT COUNT(customer_id) FROM Orders) AS DECIMAL(10, 2)) * 100 AS percent_of_repeat_orders FROM repeat_order; | total_repeat_order | total_orders | percent_of_repeat_orders |
|---|---|---|
| 257 | 1000 | 25.70 |
Calculates the percentage of repeat customers, providing insight into customer loyalty and retention.
SELECT YEAR(o.order_date) AS year_cookies, CASE WHEN MONTH(o.order_date) = 1 THEN 'January' WHEN MONTH(o.order_date) = '2' THEN 'February' WHEN MONTH(o.order_date) = '3' THEN 'March' WHEN MONTH(o.order_date) = '4' THEN 'April' WHEN MONTH(o.order_date) = '5' THEN 'May' WHEN MONTH(o.order_date) = '6' THEN 'June' WHEN MONTH(o.order_date) = '7' THEN 'July' WHEN MONTH(o.order_date) = '8' THEN 'August' WHEN MONTH(o.order_date) = '9' THEN 'September' WHEN MONTH(o.order_date) = '10' THEN 'October' WHEN MONTH(o.order_date) = '11' THEN 'November' WHEN MONTH(o.order_date) = '12' THEN 'December' END AS month_cookies, SUM(od.total_price) as total_sale FROM Order_Details od LEFT JOIN Orders o ON od.order_id = o.order_id GROUP BY YEAR(o.order_date), MONTH(o.order_date) ORDER BY year_cookies DESC | year_cookies | month_cookies | total_sale |
|---|---|---|
| 2024 | January | 1310.25 |
| 2024 | February | 1286.00 |
| 2024 | March | 1158.00 |
| 2024 | April | 1361.50 |
| 2024 | May | 1488.50 |
| 2024 | June | 1333.50 |
| 2024 | July | 1613.25 |
| 2024 | August | 1305.50 |
| 2024 | September | 1015.00 |
| 2023 | October | 1366.50 |
| 2023 | November | 1025.25 |
| 2023 | December | 1416.25 |
Breaks down sales performance by month to help identify seasonality and sales trends.
5.1 Can we forecast which products will perform well in the future based on past performance? (9 months past performance in 2024 based on orders and sales)
--Order WITH nine_month_performance AS ( SELECT c.name AS cookies, SUM(od.quantity) AS highest_order_cookie, YEAR(o.order_date) AS year_cookies, CASE WHEN MONTH(o.order_date) = '1' THEN 'January' WHEN MONTH(o.order_date) = '2' THEN 'February' WHEN MONTH(o.order_date) = '3' THEN 'March' WHEN MONTH(o.order_date) = '4' THEN 'April' WHEN MONTH(o.order_date) = '5' THEN 'May' WHEN MONTH(o.order_date) = '6' THEN 'June' WHEN MONTH(o.order_date) = '7' THEN 'July' WHEN MONTH(o.order_date) = '8' THEN 'August' WHEN MONTH(o.order_date) = '9' THEN 'September' WHEN MONTH(o.order_date) = '10' THEN 'October' WHEN MONTH(o.order_date) = '11' THEN 'November' WHEN MONTH(o.order_date) = '12' THEN 'December' END AS month_cookies, SUM(od.total_price) as total_sale FROM Order_Details od LEFT JOIN Cookies c ON od.cookie_id = c.cookie_id LEFT JOIN Orders o ON od.order_id = o.order_id GROUP BY YEAR(o.order_date), MONTH(o.order_date), c.name ) SELECT cookies, SUM(highest_order_cookie) AS total_order FROM nine_month_performance WHERE month_cookies IN ('January','February','March','April','May','June','July','August','September') AND year_cookies = 2024 GROUP BY cookies ORDER BY total_order DESC; | cookies | total_order |
|---|---|
| Peanut Butter | 1712 |
| Sugar Cookie | 1657 |
| Chocolate Chip | 1612 |
| Double Chocolate | 1504 |
| Oatmeal Raisin | 1434 |
--Sale WITH nine_month_performance AS ( SELECT c.name AS cookies, SUM(od.quantity) AS highest_order_cookie, YEAR(o.order_date) AS year_cookies, CASE WHEN MONTH(o.order_date) = '1' THEN 'January' WHEN MONTH(o.order_date) = '2' THEN 'February' WHEN MONTH(o.order_date) = '3' THEN 'March' WHEN MONTH(o.order_date) = '4' THEN 'April' WHEN MONTH(o.order_date) = '5' THEN 'May' WHEN MONTH(o.order_date) = '6' THEN 'June' WHEN MONTH(o.order_date) = '7' THEN 'July' WHEN MONTH(o.order_date) = '8' THEN 'August' WHEN MONTH(o.order_date) = '9' THEN 'September' WHEN MONTH(o.order_date) = '10' THEN 'October' WHEN MONTH(o.order_date) = '11' THEN 'November' WHEN MONTH(o.order_date) = '12' THEN 'December' END AS month_cookies, SUM(od.total_price) as total_sale FROM Order_Details od LEFT JOIN Cookies c ON od.cookie_id = c.cookie_id LEFT JOIN Orders o ON od.order_id = o.order_id GROUP BY YEAR(o.order_date), MONTH(o.order_date), c.name ) SELECT cookies, SUM(total_sale) AS total_sale FROM nine_month_performance WHERE month_cookies IN ('January','February','March','April','May','June','July','August','September') AND year_cookies = 2024 GROUP BY cookies ORDER BY total_sale DESC; | cookies | total_sale |
|---|---|
| Double Chocolate | 3008.00 |
| Peanut Butter | 2996.00 |
| Chocolate Chip | 2418.00 |
| Oatmeal Raisin | 1792.50 |
| Sugar Cookie | 1657.00 |
5.2 Using the moving average method for a 4-quarter 2024. (Estimated sale in Oct, Nov, Dec).What are the expected sales for the next quarter based on historical trends?
WITH sale_per_month AS ( SELECT YEAR(o.order_date) AS year_cookies, CASE WHEN MONTH(o.order_date) = '1' THEN 'January' WHEN MONTH(o.order_date) = '2' THEN 'February' WHEN MONTH(o.order_date) = '3' THEN 'March' WHEN MONTH(o.order_date) = '4' THEN 'April' WHEN MONTH(o.order_date) = '5' THEN 'May' WHEN MONTH(o.order_date) = '6' THEN 'June' WHEN MONTH(o.order_date) = '7' THEN 'July' WHEN MONTH(o.order_date) = '8' THEN 'August' WHEN MONTH(o.order_date) = '9' THEN 'September' WHEN MONTH(o.order_date) = '10' THEN 'October' WHEN MONTH(o.order_date) = '11' THEN 'November' WHEN MONTH(o.order_date) = '12' THEN 'December' END AS month_cookies, SUM(od.total_price) as total_sale FROM Order_Details od LEFT JOIN Orders o ON od.order_id = o.order_id GROUP BY YEAR(o.order_date), MONTH(o.order_date) ) SELECT SUM(total_sale) AS first_qtr_sale, (SELECT SUM(total_sale) FROM sale_per_month WHERE month_cookies IN ('April','May','June') AND year_cookies = 2024) AS second_qtr_sale, (SELECT SUM(total_sale) FROM sale_per_month WHERE month_cookies IN ('July','August','September') AND year_cookies = 2024) AS third_qtr_sale, (SELECT ROUND(SUM(total_price)/ 3, 2) FROM Order_Details od LEFT JOIN Orders o ON od.order_id = o.order_id WHERE YEAR(o.order_date) = '2024') AS avg_fourth_qtr_sale FROM sale_per_month WHERE month_cookies IN ('January','February','March') AND year_cookies = 2024 | first_qtr_sale | second_qtr_sale | third_qtr_sale | avg_fourth_qtr_sale |
|---|---|---|---|
| 3754.25 | 4183.50 | 3933.75 | 3957.17 |
The moving average for a period of four quarters is typically calculated by taking the average of the sales from the last three quarters. In this case, since we only have three quarters of data for 2024. Formula (Total Sale(Q1,Q2,Q3/3 Quarters)) = Moving Average. Thus 3957.17, we can use this as an estimated sales figure for each month (October, November, December) in the fourth quarter of 2024.
- SQL Server Management Studio (SSMS): For creating the relational database, and inserting records.
- SQL: Run queries and data manipulation.
- Python (Faker Library): To automate the generation of 1000 fictional customer, order, and order details records.
- CSV Files: The data generated was saved in CSV format and then inserted into the SSMS database.
- Power BI: Used for building an interactive dashboard to visualize sales trends and customer behavior.
- Resolving foreign key constraint issues during data insertion.
- Handling and normalizing non-standard date formats.
- Managing large data inserts and query performance.
This project demonstrates practical skills in data generation, querying, and analysis to simulate a real-world cookies business database. The 1000 fictional data entries offer an excellent base for advanced analysis providing valuable insights for business growth and optimization strategies.