🚀 Data Analyst Roadmap — Part 17

🧠 SQL Level 7 — Date & Time Functions + Time-Based Analysis

Date and time analysis is one of the most important SQL skills for a Data Analyst.

Real business data is heavily time-dependent:

📈 Monthly revenue

📊 Year-over-year growth

🛒 Daily orders

👥 Customer activity

📦 Product demand

⏱️ Response time

📅 Retention and cohort analysis

To become strong in SQL, you need to know how to extract, filter, compare, group, and calculate differences between dates.

🔹 1. Understanding Date & Time Data Types

• DATE → Date only

• TIME → Time only

• DATETIME / TIMESTAMP → Date + time

Order_Date: 2026-01-15
Order_Timestamp: 2026-01-15 14:35:20

🔹 2. Extracting Parts of a Date

SELECT
    Order_Date,
    EXTRACT(YEAR FROM Order_Date) AS Order_Year,
    EXTRACT(MONTH FROM Order_Date) AS Order_Month
FROM Orders;

🔹 3. Grouping Sales by Year

SELECT
    EXTRACT(YEAR FROM Order_Date) AS Order_Year,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY EXTRACT(YEAR FROM Order_Date)
ORDER BY Order_Year;

🔹 4. Grouping Sales by Month

SELECT
    EXTRACT(YEAR FROM Order_Date) AS Order_Year,
    EXTRACT(MONTH FROM Order_Date) AS Order_Month,
    SUM(Sales) AS Total_Sales
FROM Orders
GROUP BY 
    EXTRACT(YEAR FROM Order_Date),
    EXTRACT(MONTH FROM Order_Date)
ORDER BY Order_Year, Order_Month;

⚠️ Don't group only by month number when data covers multiple years — Jan 2025 + Jan 2026 would merge incorrectly.

🔹 5. Filtering Data by Date

SELECT * FROM Orders
WHERE Order_Date >= '2026-01-01'
  AND Order_Date < '2026-02-01';

🔹 6. Why Date Ranges Matter

If Order_Timestamp = 2026-01-31 23:30:00, then

WHERE Order_Timestamp <= '2026-01-31' will miss it.

Use half-open range:

WHERE Order_Timestamp >= '2026-01-01'
  AND Order_Timestamp < '2026-02-01'

🔹 7. Date Difference

Conceptually: Date_Difference(First_Purchase_Date, Signup_Date)

Dialects vary: DATEDIFF(), DATE_DIFF(), subtraction, etc.

🔹 8. Customers Who Took >30 Days to Purchase

SELECT Customer_ID, Signup_Date, First_Purchase_Date
FROM Customers
WHERE DATEDIFF(day, Signup_Date, First_Purchase_Date) > 30;

🔹 9. Adding / Subtracting Dates

Signup_Date + INTERVAL '30' DAY  -- 30 days after signup
Order_Date - INTERVAL '7' DAY    -- 7 days before order

🔹 10. Current Date and Time

CURRENT_DATE, CURRENT_TIMESTAMP — useful for today's sales, active subs, overdue orders.

🔹 11. Recent Orders (Last 30 Days)

SELECT * FROM Orders
WHERE Order_Date >= CURRENT_DATE - INTERVAL '30' DAY;

🔹 12. Year-over-Year Analysis

Formula: (Current - Previous) / Previous * 100

In SQL:

LAG(Sales) OVER (ORDER BY Year)

🔹 13. Month-over-Month Growth

WITH Monthly_Sales AS (
    SELECT
        EXTRACT(YEAR FROM Order_Date) AS Year,
        EXTRACT(MONTH FROM Order_Date) AS Month,
        SUM(Sales) AS Total_Sales
    FROM Orders
    GROUP BY 1, 2
)

SELECT
    Year, Month, Total_Sales,
    LAG(Total_Sales) OVER (ORDER BY Year, Month) AS Previous_Month_Sales
FROM Monthly_Sales;

🔹 14. Quarter Analysis

EXTRACT(QUARTER FROM Order_Date)
-- Q1: Jan-Mar, Q2: Apr-Jun, Q3: Jul-Sep, Q4: Oct-Dec

🔹 15. First and Last Transaction

ROW_NUMBER() OVER (
    PARTITION BY Customer_ID
    ORDER BY Order_Date
)
-- rn = 1 is first transaction