HolyJuice 2024 Sales Analysis 🍹📊
Introduction 👋
HolyJuice is a fresh fruit juice bar in the HolyWorld. You have been assigned by the company to analyze their 2024 sales data.
SQL Playground 💻
You can connect to the HolyJuice sales database here: https://holyselect.com/playsql
The interface includes:
- 🔍 A left panel to explore the database (toggle visibility with the database button)
- ✏️ A central SQL editor where you can write and execute statements (separate multiple statements with semicolons)
- 📋 A right panel displaying query results with sorting and filtering capabilities
To execute SQL:
- Select the statement(s) you want to run
- Click "Execute" or use the Ctrl+Enter shortcut
For the best experience, we recommend using:
- 🖥️ A desktop computer
- 💻 Laptop
- 📱 Tablet with physical keyboard
If the playground becomes unresponsive, try refreshing the page.
Important: The playground does not save your scripts. Be sure to save your work externally, as refreshing will erase all unsaved content.
Data Overview 📈
The dataset contains 2024 sales transactions along with reference data for products, customers, and discounts.
Products Data 🥤🍹🧃
Our juice bar offers various fresh juice products, each with:
- A unique product ID
- Product name (e.g., "Tropical Blend", "Green Detox")
- Base price in HolyCoin (the currency of Holyworld)
Customers Data 👥
We track information about registered customers including:
- Unique customer ID
- First name
- Last name (Note: Many sales are from walk-in customers without registration)
Discounts Data 💰
We regularly offer discounts to customers:
- Unique discount ID
- Discount name
- Discount rate (0.1 = 10% off, 1 = 100% off)
- Discount period
Sales Transactions 🧾
Each sale record includes:
- Purchased product
- Customer (if registered)
- Purchase date and time
- Quantity
- Applied discount (if any)
The sales amount formula is: (1 - Discount rate) × Base price × Quantity
Data Quality Considerations ⚠️
As with real-world data, this dataset may contain errors. Your analysis should account for these based on the requirements covered in this course.