Day 7 – Conditional Logic with CASE WHEN

1️⃣ Day Title

Day 7 – CASE WHEN Statements for Business Rules

2️⃣ Learning Objectives

By the end of Day 7, learners will be able to:

• Use CASE WHEN to apply conditions
• Create derived and categorized columns
• Implement business rules directly in SQL
• Prepare analysis-ready data for BI tools

3️⃣ Key Concepts Explained

🔹 What is CASE WHEN?

• SQL’s version of IF–ELSE logic
• Used to create conditional columns
• Very common in analytics and reporting

🔹 CASE WHEN Structure

CASE
  WHEN condition THEN result
  WHEN condition THEN result
  ELSE result
END        

🔹 Where CASE WHEN is Used

• Customer segmentation
• Order status labels
• Performance categories
• Pricing tiers

4️⃣ SQL Syntax & Examples

✅ Basic CASE WHEN

SELECT 
  order_id,
  total_amount,
  CASE
    WHEN total_amount >= 1000 THEN 'High Value'
    WHEN total_amount >= 500 THEN 'Medium Value'
    ELSE 'Low Value'
  END AS order_category
FROM orders;        

Explanation:

• Categorizes orders based on value

✅ CASE WHEN with Aggregation

SELECT 
  customer_id,
  SUM(total_amount) AS total_spent,
  CASE
    WHEN SUM(total_amount) >= 5000 THEN 'VIP'
    ELSE 'Regular'
  END AS customer_type
FROM orders
GROUP BY customer_id;        

✅ CASE WHEN in WHERE (Logical Filtering)

SELECT *
FROM orders
WHERE 
  CASE
    WHEN total_amount > 500 THEN 1
    ELSE 0
  END = 1;        

5️⃣ Real-World Business Use Case

📊 Business Scenario: Customer Segmentation

Problem: Marketing wants to segment customers by spending behavior.

SQL Solution:

SELECT 
  c.customer_id,
  c.name,
  SUM(o.total_amount) AS total_spent,
  CASE
    WHEN SUM(o.total_amount) >= 10000 THEN 'Platinum'
    WHEN SUM(o.total_amount) >= 5000 THEN 'Gold'
    ELSE 'Silver'
  END AS segment
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;        

Outcome:

• Clear customer segments
• Better personalized marketing

6️⃣ Practice Exercises

🟢 Easy

1. Label orders as High or Low value
2. Categorize products based on price

🟡 Medium

1. Classify customers as Active / Inactive
2. Create discount eligibility flag

🔴 Challenge

1. Create sales performance levels
2. Label customers by lifetime value
3. Assign risk category to low-value customers

7️⃣ Mini Project / Assignment

🧩 Mini Task: Sales Classification Report

Scenario: Sales manager wants order classifications.

Task:

• Classify orders into:
• Use CASE WHEN
• Prepare output for dashboard usage

8️⃣ Common Mistakes & Tips

❌ Common Mistakes

• Forgetting ELSE clause
• Incorrect condition order
• Mixing data types in CASE results

✅ Professional Tips

• Always order conditions carefully
• Keep logic readable
• Use meaningful aliases

9️⃣ Interview-Ready Questions

1. What is CASE WHEN used for?
2. Can CASE WHEN be used with aggregates?
3. Difference between CASE and IF?
4. Can CASE be used in WHERE?
5. Write SQL to classify customers by sales

✅ End of Day 7

👉 Reply “Continue to Day 8” when ready. Next: Date & Time Functions for Trend Analysis 📅

Continue to Day 8

Great 👍 Day 8 is extremely important for trend, growth, and time-based analysis.

📘 Day 8 – Date & Time Functions in SQL

1️⃣ Day Title

Day 8 – Date & Time Functions for Business & Trend Analysis

2️⃣ Learning Objectives

By the end of Day 8, learners will be able to:

• Work confidently with date and time data
• Extract year, month, day from dates
• Perform time-based filtering
• Create monthly, weekly, and daily reports

3️⃣ Key Concepts Explained

🔹 Date & Time Data Types

• DATE → Stores date (YYYY-MM-DD)
• TIMESTAMP / DATETIME → Stores date + time

🔹 Common Date Functions (Cross-Database)

PurposeFunctionCurrent dateCURRENT_DATEYearYEAR()MonthMONTH()DayDAY()Date differenceDATEDIFF()Add/Subtract dateDATE_ADD() / DATE_SUB()

4️⃣ SQL Syntax & Examples

✅ Get Current Date

SELECT CURRENT_DATE;        

✅ Extract Year & Month

SELECT 
  order_id,
  YEAR(order_date) AS order_year,
  MONTH(order_date) AS order_month
FROM orders;        

✅ Filter Last 30 Days Orders

SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL 30 DAY;        

✅ Monthly Sales Report

SELECT 
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;        

✅ Date Difference

SELECT 
  order_id,
  DATEDIFF(delivery_date, order_date) AS delivery_days
FROM orders;        

5️⃣ Real-World Business Use Case

📊 Business Scenario: Monthly Sales Trend

Problem: Management wants to track monthly sales trends.

SQL Solution:

SELECT 
  YEAR(order_date) AS year,
  MONTH(order_date) AS month,
  SUM(total_amount) AS total_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;        

Outcome:

• Identify growth or decline
• Support forecasting decisions

6️⃣ Practice Exercises

🟢 Easy

1. Extract year from order date
2. Find today’s date using SQL

🟡 Medium

1. Orders from last 7 days
2. Monthly total orders

🔴 Challenge

1. Year-over-year sales comparison
2. Average delivery time per month
3. Customers who ordered in last 90 days

7️⃣ Mini Project / Assignment

🧩 Mini Task: Sales Trend Dashboard Data

Scenario: BI team needs time-based data.

Task:

• Create:
• Sort chronologically
• Prepare dataset for Power BI

8️⃣ Common Mistakes & Tips

❌ Common Mistakes

• Treating dates as text
• Forgetting database-specific syntax
• Incorrect date ranges

✅ Professional Tips

• Always check date format
• Use YEAR/MONTH instead of text slicing
• Order by date for trend analysis

9️⃣ Interview-Ready Questions

1. How do you get current date in SQL?
2. How do you filter last 30 days data?
3. Difference between DATE and TIMESTAMP?
4. How to group data by month?
5. Write SQL for monthly sales report

👨💻 Developed by:

✅ Md. Sairul Islam

🔗 LinkedIn: https://lnkd.in/gacsTX2s

🔗 Facebook: https://lnkd.in/gkDRZ7sf

🔗 GitHub: https://lnkd.in/gi7j2PxZ

🔗 Portfolio: https://lnkd.in/gBYJMNTq 

🔗 Fiverr: https://www.fiverr.com/s/xX9eb3l