Advanced SQL: CASE, String Functions, Dates & Set Operations
This is the lesson that turns SQL into a full data-cleaning language. CASE WHEN replaces Excel formulas. COALESCE saves your aggregates from NULL contamination. STRFTIME pivots raw timestamps into monthly buckets. By the end you'll write production-grade SQL that ingestion pipelines and analytics dashboards actually use.
Learning Objectives
After this lesson, you will be able to:
Write CASE WHEN expressions for conditional logic directly inside SQL queries
Use COALESCE and NULLIF to handle NULL values gracefully in production data
Apply CAST to convert between data types for correct comparisons and calculations
Manipulate strings with UPPER, LOWER, SUBSTR, REPLACE, LENGTH, and TRIM (and Postgres' regexp_replace for real-world cleaning)
Extract and format dates with DATE_TRUNC (Postgres) / STRFTIME (SQLite) and date arithmetic
Combine result sets with UNION, INTERSECT, and EXCEPT for complex data analysis
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
data qualityData QualityData quality encompasses accuracy, completeness, consistency, and timeliness; poor data quality is the most common cause of ML project failure.Learn more →
DATE_TRUNC and date arithmetic show up in every cohort, retention, and MoM query. Stripe revenue by month, Spotify retention by week, GitHub commit activity by day. Time bucketing is its own subfield of SQL, covered first here
-- Categorize employees by salary tier
SELECT
name,
salary,
CASE
WHEN salary >= 150000 THEN 'Senior'
WHEN salary >= 100000 THEN 'Mid-Level'
WHEN salary >= 60000 THEN 'Junior'
ELSE 'Intern'
END AS level
FROM employees
ORDER BY salary DESC;
This is one of the most powerful patterns in SQL -- turning rows into columns:
sql
-- Count orders by status (pivot from rows to columns)
SELECT
DATE(order_date) AS order_day,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled,
COUNT(*) AS total_orders
FROM orders
GROUP BY DATE(order_date)
ORDER BY order_day;
sql
-- Calculate pass/fail rates per course
SELECT
course_name,
COUNT(*) AS total_students,
SUM(CASE WHEN grade >= 60 THEN 1 ELSE 0 END) AS passed,
SUM(CASE WHEN grade < 60 THEN 1 ELSE 0 END) AS failed,
ROUND(100.0 * SUM(CASE WHEN grade >= 60 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_rate
FROM student_grades
GROUP BY course_name
ORDER BY pass_rate DESC;
Loading visualization...
What Do You Think?
What does COUNT(CASE WHEN status = 'completed' THEN 1 END) return when there are no completed orders for a given day?
It returns 0. When no condition matches and there is no ELSE clause, CASE returns NULL. COUNT ignores NULL values. So COUNT of a bunch of NULLs is 0 -- exactly what we want. This is why the CASE WHEN ... THEN 1 END pattern (without ELSE) is so popular for conditional counting.
NULLIF returns NULL if two values are equal, otherwise returns the first value. Its main use: preventing division by zero.
sql
-- Without NULLIF: division by zero error when total_visits = 0
SELECT page, conversions / total_visits AS rate FROM pages;
-- With NULLIF: returns NULL instead of crashing
SELECT
page,
ROUND(100.0 * conversions / NULLIF(total_visits, 0), 2) AS conversion_rate
FROM pages;
-- Convert between types
SELECT
CAST('42' AS INTEGER) AS num, -- string to integer
CAST(3.14159 AS INTEGER) AS truncated, -- 3 (truncates, does not round)
CAST(42 AS TEXT) AS text_num, -- integer to string
CAST('2024-01-15' AS DATE) AS date_val; -- string to date
-- Common use: force decimal division
-- Integer division: 7/2 = 3 (wrong!)
-- Cast to real: CAST(7 AS REAL)/2 = 3.5 (correct!)
SELECT
department,
CAST(SUM(salary) AS REAL) / COUNT(*) AS avg_salary
FROM employees
GROUP BY department;
-- Clean and standardize customer data
SELECT
TRIM(UPPER(first_name)) || ' ' || TRIM(UPPER(last_name)) AS full_name,
LOWER(TRIM(email)) AS clean_email,
REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '') AS digits_only,
CASE
WHEN LENGTH(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '')) = 10
THEN 'Valid'
ELSE 'Invalid'
END AS phone_status
FROM customers;
Loading visualization...
Try it! Modify the query above to also extract the username part (everything before the @). Hint: use SUBSTR with INSTR to find the @ position.
Dates are the backbone of time-series analysis, reporting, and business intelligence. SQLite uses these core functions:
sql
-- Current date and time
SELECT DATE('now'); -- '2024-06-15' (current date)
SELECT DATETIME('now'); -- '2024-06-15 14:30:00'
-- Date arithmetic
SELECT DATE('now', '-7 days'); -- 7 days ago
SELECT DATE('now', '+1 month'); -- 1 month from now
SELECT DATE('now', 'start of month'); -- first day of current month
SELECT DATE('now', 'start of year'); -- first day of current year
-- STRFTIME: format dates (SQLite-specific, very powerful)
SELECT STRFTIME('%Y', '2024-06-15'); -- '2024' (year)
SELECT STRFTIME('%m', '2024-06-15'); -- '06' (month)
SELECT STRFTIME('%d', '2024-06-15'); -- '15' (day)
SELECT STRFTIME('%W', '2024-06-15'); -- '24' (week number)
SELECT STRFTIME('%w', '2024-06-15'); -- '6' (day of week, 0=Sunday)
SELECT STRFTIME('%Y-%m', '2024-06-15'); -- '2024-06' (year-month)
-- Calculate age in days
SELECT JULIANDAY('now') - JULIANDAY('2024-01-01') AS days_since_jan1;
-- Monthly revenue report
SELECT
STRFTIME('%Y-%m', order_date) AS month,
COUNT(*) AS order_count,
ROUND(SUM(total), 2) AS revenue,
ROUND(AVG(total), 2) AS avg_order_value
FROM orders
WHERE order_date >= DATE('now', '-12 months')
GROUP BY STRFTIME('%Y-%m', order_date)
ORDER BY month;
-- Day-of-week analysis: when do users sign up?
SELECT
CASE CAST(STRFTIME('%w', created_at) AS INTEGER)
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_of_week,
COUNT(*) AS signups
FROM users
GROUP BY STRFTIME('%w', created_at)
ORDER BY CAST(STRFTIME('%w', created_at) AS INTEGER);
What Do You Think?
You write: SELECT STRFTIME('%Y-%m', order_date) AS month, SUM(total) FROM orders GROUP BY month. An order on '2024-01-15' and an order on '2024-01-28' -- do they end up in the same group?
Yes, both dates produce '2024-01' from STRFTIME('%Y-%m', ...), so they land in the same group. This is the standard pattern for monthly aggregation in SQL. The date part (day 15 vs day 28) is stripped away by the format string, leaving only year-month as the grouping key.
Set operations combine the results of two or more SELECT statements. They work on entire result sets, not individual rows.
Key rules
Both SELECTs must return the same number of columns
Corresponding columns must have compatible data types
Column names come from the first SELECT
sql
-- UNION: combine results, remove duplicates
-- "Give me all people who are either customers OR employees"
SELECT name, email FROM customers
UNION
SELECT name, email FROM employees;
-- UNION ALL: combine results, keep duplicates (faster, no dedup)
SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM employees;
-- INTERSECT: only rows that appear in BOTH results
-- "Who is both a customer AND an employee?"
SELECT name, email FROM customers
INTERSECT
SELECT name, email FROM employees;
-- EXCEPT: rows in first result but NOT in second
-- "Who is a customer but NOT an employee?"
SELECT name, email FROM customers
EXCEPT
SELECT name, email FROM employees;
-- Find products that sold this month but NOT last month (new sellers)
SELECT DISTINCT product_id FROM orders
WHERE order_date >= DATE('now', 'start of month')
EXCEPT
SELECT DISTINCT product_id FROM orders
WHERE order_date >= DATE('now', 'start of month', '-1 month')
AND order_date < DATE('now', 'start of month');
-- Build a unified activity feed from multiple event types
SELECT user_id, 'purchase' AS event_type, created_at FROM purchases
UNION ALL
SELECT user_id, 'login' AS event_type, login_time FROM logins
UNION ALL
SELECT user_id, 'support_ticket' AS event_type, opened_at FROM tickets
ORDER BY created_at DESC
LIMIT 100;
Here is a query that uses CASE, COALESCE, string functions, date functions, and set logic together:
sql
-- Customer health report: combine purchase and engagement data
SELECT
c.customer_id,
UPPER(TRIM(c.first_name)) || ' ' || UPPER(TRIM(c.last_name)) AS name,
COALESCE(c.phone, 'N/A') AS phone,
STRFTIME('%Y-%m-%d', c.created_at) AS signup_date,
CAST(JULIANDAY('now') - JULIANDAY(c.created_at) AS INTEGER) AS days_since_signup,
COALESCE(p.total_purchases, 0) AS total_purchases,
COALESCE(p.total_spent, 0) AS total_spent,
CASE
WHEN p.last_purchase >= DATE('now', '-30 days') THEN 'Active'
WHEN p.last_purchase >= DATE('now', '-90 days') THEN 'At Risk'
WHEN p.last_purchase IS NOT NULL THEN 'Churned'
ELSE 'Never Purchased'
END AS customer_status,
CASE
WHEN COALESCE(p.total_spent, 0) >= 1000 THEN 'VIP'
WHEN COALESCE(p.total_spent, 0) >= 200 THEN 'Regular'
ELSE 'New'
END AS tier
FROM customers c
LEFT JOIN (
SELECT
customer_id,
COUNT(*) AS total_purchases,
SUM(amount) AS total_spent,
MAX(purchase_date) AS last_purchase
FROM purchases
GROUP BY customer_id
) p ON c.customer_id = p.customer_id
ORDER BY total_spent DESC;
Try it! Read through this query line by line. Notice how every function from this lesson appears: CASE for categorization, COALESCE for NULL safety, UPPER/TRIM for string cleaning, STRFTIME/JULIANDAY for dates, and a subquery that could be rewritten with set operations. This is what production SQL looks like.
You have now learned the core building blocks. Every complex SQL query is just a combination of these fundamental operations -- CASE for logic, COALESCE for safety, string functions for cleaning, date functions for time, and set operations for combining results.