10-date-and-string-functions.sql

-- ============================================================
-- SQL Masterclass — Chapter 10: Date and String Functions
-- ============================================================
-- 🔴 ADVANCED
--
-- In this chapter you will learn:
--   • Extracting parts of dates (year, month, day)
--   • Date arithmetic and differences
--   • Casting between types
--   • String functions (UPPER, LOWER, LENGTH, etc.)
--   • Text concatenation and formatting
--   • Time-series analysis patterns
-- ============================================================


-- ============================================================
-- 10.1  EXTRACTING DATE PARTS
-- ============================================================
-- Different databases have different syntax.
-- These examples use commonly supported functions.

-- Extract year and month from order timestamps
-- (SQLite-compatible using substr; PostgreSQL uses EXTRACT)
SELECT
    order_id,
    order_purchase_timestamp,
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 4)  AS order_year,
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 6, 2)  AS order_month,
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 9, 2)  AS order_day
FROM orders
LIMIT 10;


-- ============================================================
-- 10.2  MONTHLY ORDER TRENDS
-- ============================================================

-- Orders per month
SELECT
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
    COUNT(*) AS order_count
FROM orders
GROUP BY SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)
ORDER BY year_month;


-- Monthly revenue
SELECT
    SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
    COUNT(DISTINCT o.order_id)               AS num_orders,
    SUM(oi.price)                            AS total_revenue,
    AVG(oi.price)                            AS avg_item_price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 7)
ORDER BY year_month;


-- ============================================================
-- 10.3  DATE DIFFERENCES — Delivery time analysis
-- ============================================================

-- Calculate delivery time in days
-- (Using standard date subtraction)
SELECT
    order_id,
    order_purchase_timestamp,
    order_delivered_customer_date,
    CAST(
        CAST(order_delivered_customer_date AS DATE) -
        CAST(order_purchase_timestamp AS DATE)
    AS INTEGER) AS delivery_days
FROM orders
WHERE order_status = 'delivered'
  AND order_delivered_customer_date IS NOT NULL
ORDER BY delivery_days DESC
LIMIT 10;


-- Average delivery time by state
SELECT
    c.customer_state,
    COUNT(*) AS num_orders,
    AVG(
        CAST(o.order_delivered_customer_date AS DATE) -
        CAST(o.order_purchase_timestamp AS DATE)
    ) AS avg_delivery_days,
    MIN(
        CAST(o.order_delivered_customer_date AS DATE) -
        CAST(o.order_purchase_timestamp AS DATE)
    ) AS min_delivery_days,
    MAX(
        CAST(o.order_delivered_customer_date AS DATE) -
        CAST(o.order_purchase_timestamp AS DATE)
    ) AS max_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 c.customer_state
ORDER BY avg_delivery_days;


-- Approval time (purchase → approved)
SELECT
    SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7) AS year_month,
    AVG(
        CAST(order_approved_at AS DATE) -
        CAST(order_purchase_timestamp AS DATE)
    ) * 24 AS avg_approval_hours
FROM orders
WHERE order_approved_at IS NOT NULL
GROUP BY SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 1, 7)
ORDER BY year_month;


-- ============================================================
-- 10.4  CASTING TYPES
-- ============================================================

-- Cast price to integer (rounds down)
SELECT
    order_id,
    price,
    CAST(price AS INTEGER) AS price_rounded
FROM order_items
LIMIT 10;

-- Cast to text for concatenation
SELECT
    order_id,
    'Order total: $' || CAST(ROUND(price + freight_value, 2) AS TEXT) AS summary
FROM order_items
LIMIT 10;


-- ============================================================
-- 10.5  STRING FUNCTIONS
-- ============================================================

-- UPPER and LOWER
SELECT
    customer_city,
    UPPER(customer_city) AS city_upper,
    LOWER(customer_city) AS city_lower
FROM customers
LIMIT 10;

-- LENGTH — string length
SELECT
    customer_city,
    LENGTH(customer_city) AS city_name_length
FROM customers
ORDER BY city_name_length DESC
LIMIT 10;


-- REPLACE — substitute characters
SELECT DISTINCT
    customer_city,
    REPLACE(customer_city, ' ', '_') AS city_underscored
FROM customers
WHERE customer_city LIKE '% %'
LIMIT 10;


-- TRIM — remove whitespace
SELECT DISTINCT
    customer_city,
    TRIM(customer_city) AS city_trimmed
FROM customers
LIMIT 10;


-- SUBSTR — extract part of a string
-- Get the first 3 characters of each city
SELECT DISTINCT
    customer_city,
    SUBSTR(customer_city, 1, 3) AS city_prefix
FROM customers
LIMIT 10;


-- ============================================================
-- 10.6  STRING-BASED ANALYSIS
-- ============================================================

-- Analyze review comment lengths
SELECT
    review_score,
    COUNT(*) AS num_reviews,
    AVG(LENGTH(review_comment_message)) AS avg_comment_length,
    MAX(LENGTH(review_comment_message)) AS max_comment_length
FROM order_reviews
WHERE review_comment_message IS NOT NULL
  AND review_comment_message != ''
GROUP BY review_score
ORDER BY review_score;


-- ============================================================
-- 10.7  TIME-SERIES PATTERNS
-- ============================================================

-- Day-of-week analysis (0=Sunday in SQLite)
SELECT
    EXTRACT(DOW FROM CAST(order_purchase_timestamp AS TIMESTAMP)) AS day_of_week,
    CASE EXTRACT(DOW FROM CAST(order_purchase_timestamp AS TIMESTAMP))
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day_name,
    COUNT(*) AS order_count
FROM orders
GROUP BY day_of_week
ORDER BY day_of_week;


-- 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 SUBSTR(CAST(order_purchase_timestamp AS VARCHAR), 12, 2)
ORDER BY hour_of_day;


-- Quarterly revenue
SELECT
    SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 4) AS year,
    CASE
        WHEN CAST(SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 6, 2) AS INTEGER) <= 3  THEN 'Q1'
        WHEN CAST(SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 6, 2) AS INTEGER) <= 6  THEN 'Q2'
        WHEN CAST(SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 6, 2) AS INTEGER) <= 9  THEN 'Q3'
        ELSE 'Q4'
    END AS quarter,
    COUNT(DISTINCT o.order_id) AS num_orders,
    SUM(oi.price)              AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY
    SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 1, 4),
    CASE
        WHEN CAST(SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 6, 2) AS INTEGER) <= 3  THEN 'Q1'
        WHEN CAST(SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 6, 2) AS INTEGER) <= 6  THEN 'Q2'
        WHEN CAST(SUBSTR(CAST(o.order_purchase_timestamp AS VARCHAR), 6, 2) AS INTEGER) <= 9  THEN 'Q3'
        ELSE 'Q4'
    END
ORDER BY year, quarter;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: What is the average delivery time (in days) for
--             each product category? Show top 10 slowest.

-- Exercise 2: Which month had the highest total revenue?

-- Exercise 3: Analyze the day-of-week pattern for order reviews.
--             On which day do customers write the most reviews?

-- Exercise 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,
    COUNT(*) AS num_orders,
    AVG(
        CAST(o.order_delivered_customer_date AS DATE) -
        CAST(o.order_purchase_timestamp AS DATE)
    ) AS avg_delivery_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
  AND p.product_category_name IS NOT NULL
GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
HAVING COUNT(*) > 50
ORDER BY avg_delivery_days DESC
LIMIT 10;

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

-- Exercise 3
SELECT
    CASE EXTRACT(DOW FROM CAST(review_creation_date AS TIMESTAMP))
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day_name,
    COUNT(*) AS review_count
FROM order_reviews
GROUP BY EXTRACT(DOW FROM CAST(review_creation_date AS TIMESTAMP))
ORDER BY review_count 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 SUBSTR(CAST(order_approved_at AS VARCHAR), 1, 7)
ORDER BY year_month;