10-date-and-string-functions.md

Chapter 10: Date and String Functions

Real-world data is messy and often needs careful formatting. Date and String functions allow you to extract precise information from timestamps, clean up text data, and perform advanced time-series analysis.

10.1 Extracting Date Parts

Different databases have different syntax for date extraction, but most support some version of EXTRACT or string manipulation functions like SUBSTR.

-- Monthly order trends
SELECT
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

10.2 Date Arithmetic — Analyzing Delivery Time

Calculating the difference between dates is essential for warehouse and logistics analysis.

-- Calculate average delivery time in days by state
SELECT
    c.customer_state,
    AVG(
        CAST(o.order_delivered_customer_date AS DATE) -
        CAST(o.order_purchase_timestamp AS DATE)
    ) AS avg_delivery_days
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL
GROUP BY 1
ORDER BY 2;

10.3 String Functions

SQL provides a variety of functions for manipulating text:

• UPPER / LOWER: Change case.
• LENGTH: Count characters.
• REPLACE: Substitute parts of a string.
• SUBSTR: Extract a portion of a string.
• TRIM: Remove leading/trailing whitespace.

-- Analyze review comment lengths
SELECT
    review_score,
    COUNT(*) AS num_reviews,
    AVG(LENGTH(review_comment_message)) AS avg_comment_length
FROM order_reviews
WHERE review_comment_message IS NOT NULL
GROUP BY review_score;

10.4 Casting Types

Use CAST or :: (in PostgreSQL) to convert data from one type to another, such as turning a number into text for concatenation.

SELECT
    order_id,
    'Total: $' || CAST(ROUND(price + freight_value, 2) AS TEXT) AS summary
FROM order_items
LIMIT 10;

10.5 Time-Series Patterns

Analyzing data by day of the week or hour of the day can reveal deep insights into customer behavior.

-- Hour-of-day analysis
SELECT
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 12, 2) AS hour_of_day,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1
ORDER BY 1;

---

Exercises

1. What is the average delivery time (in days) for each product category? Show the top 10 slowest.
2. Which month had the highest total revenue?
3. Analyze the day-of-week pattern for order reviews. On which day do customers write the most reviews?
4. Calculate the time between order approval and carrier pickup (in hours) by month.

Solutions

-- Exercise 1
SELECT COALESCE(t.product_category_name_english, p.product_category_name) AS category, AVG(CAST(o.order_delivered_customer_date AS DATE) - CAST(o.order_purchase_timestamp AS DATE)) AS avg_days FROM orders o JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id LEFT JOIN product_category_name_translation t ON p.product_category_name = t.product_category_name WHERE o.order_status = 'delivered' AND o.order_delivered_customer_date IS NOT NULL GROUP BY 1 HAVING COUNT(*) > 50 ORDER BY 2 DESC LIMIT 10;

-- Exercise 2
SELECT SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month, SUM(oi.price) FROM orders o JOIN order_items oi ON o.order_id = oi.order_id GROUP BY 1 ORDER BY 2 DESC LIMIT 1;

-- Exercise 3
SELECT CASE EXTRACT(DOW FROM CAST(review_creation_date AS TIMESTAMP)) WHEN 0 THEN 'Sun' WHEN 1 THEN 'Mon' WHEN 2 THEN 'Tue' WHEN 3 THEN 'Wed' WHEN 4 THEN 'Thu' WHEN 5 THEN 'Fri' WHEN 6 THEN 'Sat' END AS day, COUNT(*) FROM order_reviews GROUP BY EXTRACT(DOW FROM CAST(review_creation_date AS TIMESTAMP)) ORDER BY 2 DESC;

-- Exercise 4
SELECT SUBSTR(CAST(order_approved_at AS VARCHAR), 1, 7) AS year_month, AVG(EXTRACT(EPOCH FROM (CAST(order_delivered_carrier_date AS TIMESTAMP) - CAST(order_approved_at AS TIMESTAMP))) / 3600.0) AS avg_pickup_hours FROM orders WHERE order_approved_at IS NOT NULL AND order_delivered_carrier_date IS NOT NULL GROUP BY 1 ORDER BY 1;