Advanced SQL Transformations

Quick Answer: Master multi-table JOINs, date/string functions, CASE WHEN conditional logic, and set operations (UNION, INTERSECT, EXCEPT). These are the building blocks for silver-to-gold transformations in the medallion architecture.

Multi-Table JOINs

Join Types

-- Multi-table join for gold layer aggregation
SELECT
    o.order_date,
    c.customer_name,
    c.segment,
    p.product_name,
    p.category,
    o.quantity,
    o.total_amount
FROM silver.orders o
INNER JOIN silver.customers c ON o.customer_id = c.customer_id
INNER JOIN silver.products p ON o.product_id = p.product_id
WHERE o.order_date >= '2026-01-01';

SEMI and ANTI Joins

-- LEFT SEMI JOIN: Customers who have placed orders
SELECT * FROM customers c
LEFT SEMI JOIN orders o ON c.customer_id = o.customer_id;

-- LEFT ANTI JOIN: Customers who have NEVER placed orders
SELECT * FROM customers c
LEFT ANTI JOIN orders o ON c.customer_id = o.customer_id;

On the Exam: LEFT SEMI JOIN is like EXISTS — it returns rows from the left table that have at least one match, without duplicating rows. LEFT ANTI JOIN is like NOT EXISTS — it returns rows with no match.

Date and Timestamp Functions

SELECT
    order_date,

    -- Formatting
    date_format(order_date, 'yyyy-MM-dd') AS formatted_date,
    date_format(order_date, 'EEEE') AS day_of_week,

    -- Arithmetic
    date_add(order_date, 30) AS plus_30_days,
    date_sub(order_date, 7) AS minus_7_days,
    datediff(current_date(), order_date) AS days_since_order,
    months_between(current_date(), order_date) AS months_since_order,

    -- Extraction
    year(order_date) AS order_year,
    month(order_date) AS order_month,
    dayofweek(order_date) AS day_num,

    -- Current values
    current_date() AS today,
    current_timestamp() AS now,

    -- Conversion
    to_date('2026-03-31', 'yyyy-MM-dd') AS parsed_date,
    to_timestamp('2026-03-31 14:30:00', 'yyyy-MM-dd HH:mm:ss') AS parsed_ts
FROM orders;

String Functions

SELECT
    -- Case conversion
    lower(customer_name) AS lower_name,
    upper(customer_name) AS upper_name,
    initcap(customer_name) AS title_case,

    -- Trimming
    trim(raw_input) AS trimmed,
    ltrim(raw_input) AS left_trimmed,
    rtrim(raw_input) AS right_trimmed,

    -- Extraction and manipulation
    substring(phone, 1, 3) AS area_code,
    concat(first_name, ' ', last_name) AS full_name,
    replace(phone, '-', '') AS clean_phone,
    split(address, ',') AS address_parts,

    -- Pattern matching
    regexp_extract(email, '(.+)@(.+)', 1) AS email_username,
    regexp_extract(email, '(.+)@(.+)', 2) AS email_domain,

    -- Measurement
    length(description) AS desc_length,

    -- Null handling
    COALESCE(middle_name, '') AS middle_name_safe
FROM customers;

Conditional Expressions

CASE WHEN

SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount >= 1000 THEN 'Premium'
        WHEN total_amount >= 100 THEN 'Standard'
        ELSE 'Basic'
    END AS order_tier
FROM orders;

IF Function

SELECT
    customer_id,
    IF(total_orders > 10, 'Frequent', 'Occasional') AS customer_type
FROM customer_summary;

Null-Handling Functions

Set Operations

-- UNION ALL: combine results (keep duplicates)
SELECT customer_id FROM online_orders
UNION ALL
SELECT customer_id FROM store_orders;

-- UNION: combine results (remove duplicates)
SELECT customer_id FROM online_orders
UNION
SELECT customer_id FROM store_orders;

-- INTERSECT: rows in both queries
SELECT customer_id FROM online_orders
INTERSECT
SELECT customer_id FROM store_orders;

-- EXCEPT: rows in first query but not second
SELECT customer_id FROM all_customers
EXCEPT
SELECT customer_id FROM churned_customers;

On the Exam: UNION ALL is preferred over UNION when duplicates are acceptable because UNION requires an expensive deduplication step. Use EXCEPT to find rows that exist in one dataset but not another.