Combining Functions

Putting It All Together

Here’s the truth about SQL interviews and real work: they rarely test one concept in isolation. You’ll get a messy dataset with timestamps in different formats, product names that need cleaning, and a request to “show me monthly trends by category.” That requires combining everything we’ve covered.

In this lesson, you’ll apply date extraction, string manipulation, and the CASE WHEN patterns from Module 2 to solve realistic problems.

Quick CASE WHEN Refresher

Two forms, same purpose — conditional logic inside SQL:

-- Simple CASE (compare column to specific values)
CASE column
  WHEN 'value1' THEN 'result1'
  WHEN 'value2' THEN 'result2'
  ELSE 'default'
END

-- Searched CASE (any boolean conditions)
CASE
  WHEN condition1 THEN 'result1'
  WHEN condition2 THEN 'result2'
  ELSE 'default'
END

CASE WHEN with Date Functions

Combine conditional logic with date extraction for business categorization:

Weekend vs Weekday Analysis

Product teams love this analysis — is user behavior different on weekends?

-- Classify activity by day type (PostgreSQL)
SELECT
  DATE_TRUNC('day', event_timestamp) AS event_date,
  CASE
    WHEN EXTRACT(DOW FROM event_timestamp) IN (0, 6) THEN 'Weekend'
    ELSE 'Weekday'
  END AS day_type,
  COUNT(*) AS event_count
FROM events
GROUP BY 1, 2;

CASE WHEN with String Functions

Use CASE with string functions to categorize based on text patterns:

-- Categorize customers by email provider
SELECT
  customer_id,
  email,
  CASE
    WHEN email LIKE '%@gmail.com' THEN 'Gmail'
    WHEN email LIKE '%@yahoo.com' THEN 'Yahoo'
    WHEN email LIKE '%@outlook.com' THEN 'Outlook'
    ELSE 'Other'
  END AS email_provider
FROM customers;

Cleaning Inconsistent Data

Real data has ‘HR’, ‘hr’, ‘Human Resources’, and ‘ HR ’ all meaning the same thing:

-- Standardize messy department names
SELECT
  employee_id,
  CASE LOWER(TRIM(department))
    WHEN 'hr' THEN 'Human Resources'
    WHEN 'human resources' THEN 'Human Resources'
    WHEN 'admin' THEN 'Administration'
    WHEN 'administration' THEN 'Administration'
    ELSE INITCAP(TRIM(department))
  END AS clean_department
FROM employees;

Type Casting and Conversion

Sometimes you need to convert between types. PostgreSQL’s :: syntax is the shortest:

Dialect Comparison: Type Casting

• Cast syntax:
  • PostgreSQL uses ::type or CAST()
  • MySQL uses CAST()
  • MSSQL uses CAST() or CONVERT()
  • Oracle uses CAST() or TO_DATE()
• To date:
  • PostgreSQL uses ::DATE
  • MySQL uses CAST(DATE)
  • MSSQL uses CAST(DATE)
  • Oracle uses TO_DATE( 'YYYY-MM-DD')
• To integer:
  • PostgreSQL uses '123'::INTEGER
  • MySQL uses CAST('123' AS SIGNED)
  • MSSQL uses CAST('123' AS INT)
  • Oracle uses TO_NUMBER('123')

Complex Real-World Examples

Let’s tackle some scenarios that combine multiple techniques.

Project Budget Analysis

Calculate project risk based on budget runway:

SELECT
  project_name,
  budget,
  end_date - CURRENT_DATE AS days_remaining,
  CASE
    WHEN budget / NULLIF(end_date - CURRENT_DATE, 0) > 1000 THEN 'High Burn'
    WHEN budget / NULLIF(end_date - CURRENT_DATE, 0) > 500 THEN 'Medium Burn'
    ELSE 'On Track'
  END AS budget_status
FROM projects
WHERE end_date > CURRENT_DATE;

Risky Projects

Combine date arithmetic to calculate remaining time, then use CASE WHEN to categorize project risk.

Year-Over-Year Comparison

Tag products by how recently they launched:

SELECT
  product_id,
  product_name,
  launch_year,
  CASE
    WHEN launch_year = EXTRACT(YEAR FROM CURRENT_DATE) THEN 'New This Year'
    WHEN launch_year = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN 'Last Year'
    ELSE 'Catalog'
  END AS product_recency
FROM products;

Acceptance Rate Calculation

A common metric pattern — combine date casting with ratio calculation:

SELECT
  CAST(sent_at AS DATE) AS sent_date,
  COUNT(CASE WHEN action = 'accepted' THEN 1 END)::DECIMAL
  / NULLIF(COUNT(*), 0) AS acceptance_rate
FROM friend_requests
GROUP BY CAST(sent_at AS DATE)
ORDER BY sent_date;

Note the pattern: COUNT(CASE WHEN condition THEN 1 END) counts only matching rows. Dividing by COUNT(*) gives you the ratio. NULLIF prevents errors if a day has zero requests.

Acceptance Rate By Date

Cast timestamps to dates, then calculate the acceptance ratio using conditional counting.

Pattern: CTEs for Readability

When your query needs multiple transformation steps, CTEs make it maintainable:

Key Takeaways

• CASE WHEN combines with EXTRACT for date-based categorization (quarters, weekends, recency)
• Use CASE with LOWER/TRIM to standardize messy text before categorizing
• Type casting converts between DATE, TEXT, INTEGER — use CAST() for portability
• NULLIF(x, 0) prevents division by zero — a production essential
• CTEs separate cleaning, categorization, and aggregation for maintainable queries
• Real problems combine multiple function types — practice these patterns

Module Complete

You’ve finished Module 5. You can now extract and manipulate dates across dialects, clean and transform text data, split delimited strings, use regex for pattern matching, and combine everything with conditional logic.

These are the daily tools of production SQL. Dates and strings are messy in the real world, and now you know how to handle them.

Next up: Module 6 covers Window Functions — the most powerful (and most interview-relevant) SQL feature. Get ready for ROW_NUMBER, LAG/LEAD, running totals, and the patterns that separate junior from senior analysts.