SQL Project for Data Analyst Portfolio

Introduction

• Presenter: Adam Feiner
• Objective: Design and build a SQL database, write custom SQL queries, connect the database to a BI tool to create interactive dashboards.
• Scenario: Ben's new pizzeria, focused on take-out and delivery.

Project Breakdown

Part 1: Database Design

• Key Areas: Customer orders, stock levels, and staff.
• Database Design Approach:
  • Spec out fields for data collection.
  • Normalize data by reducing redundancy and defining relationships between tables.
  • Use Quick Database Diagrams (QuickDBD) for designing the database.

Orders Table Design

• Fields Needed:
  • Order ID (primary key)
  • Item name, Item price, Quantity
  • Customer name, Delivery address split into parts
• Normalization: Create separate tables for customers and delivery addresses to reduce redundancy.

Stock Control

• Objective: Allow Ben to monitor stock levels and know when to reorder.
• Use information on ingredients and pizza recipes to create tables for ingredients and recipes.
• Add an inventory table for stock levels.

Staff Data

• Objective: Track staff work hours and calculate costs.
• Use tables for staff, shifts, and rota.
• Define relationships to calculate staff costs per order.

Part 2: Writing Custom SQL Queries

Orders Data Queries

• Required Views:
  • Total orders, total sales, total items sold, average order value.
  • Sales by category, top selling items, orders & sales by hour.
  • Orders by delivery method and address.

Inventory and Stock Levels

• Queries Needed:
  • Total quantity by ingredient, total cost of ingredients.
  • Cost per pizza, percentage stock remaining.
  • Use subqueries and create views for complex calculations.

Staff Data Queries

• Queries Needed:
  • Total staff cost, total hours worked, hours worked and cost per staff member.

Part 3: Building Dashboards

Tools

• BI Tool: Google Data Studio
• Database: Google Cloud MySQL Instance

Orders Dashboard

• Visualize total orders, sales, items, and average order value.
• Use scorecards, bar charts, line charts, and maps for visualization.

Inventory Dashboard

• Visualize ingredient stock levels, costs, and reorder needs.
• Use tables with conditional formatting to highlight reorder needs.

Staff Dashboard

• Display staff costs and hours worked using scorecards and tables.
• Apply date filters for relevant data.

Design and Presentation

• Themes and Layout: Use extracted themes from images.
• Design Considerations: Minimize ink-to-data ratio, avoid unnecessary borders.
• Final Touches: Add titles and adjust visualization placements for clarity.

Conclusion

• All required visualizations are created and organized in dashboards.
• Encouraged viewers to recreate the project using provided project files.