5.2 Built-in SQL Expressions and an Integrated Query

4. Built-in SQL Functions and Expressions

Spark SQL provides hundreds of optimized built-in functions. Built-in functions expose expression semantics to Spark and often execute efficiently in JVM/vectorized paths. Code generation support and performance depend on the specific function; arbitrary Python UDF logic is opaque to Catalyst.

String Manipulation Functions

SELECT 
    concat_ws(' ', first_name, last_name) AS full_name,
    substring(postal_code, 1, 3) AS postal_district,
    split(email, '@')[1] AS email_domain
FROM sales.customers;

Date and Timestamp Functions

Spark SQL date math assumes calendar standards (ISO 8601) and supports microsecond timestamp precision:

SELECT 
    order_id,
    order_date,
    date_add(order_date, 30) AS payment_due_date,
    datediff(current_date(), order_date) AS days_since_order,
    date_trunc('month', order_date) AS order_month
FROM sales.orders;

Conditional Expressions and Null Handling

Spark SQL supports standard ANSI branching expressions and null substitution primitives:

• CASE WHEN: Standard multi-branch conditional evaluation.

  CASE 
      WHEN order_amount >= 1000 THEN 'Platinum'
      WHEN order_amount >= 500  THEN 'Gold'
      WHEN order_amount >= 100  THEN 'Silver'
      ELSE 'Standard'
  END AS customer_tier
  

• COALESCE(expr1, expr2, ...): Evaluates arguments from left to right and returns the first non-null expression. If all arguments are null, returns NULL.
• NVL(expr1, expr2): Two-argument shorthand equivalent to COALESCE(expr1, expr2). If expr1 is null, returns expr2.
• IF(condition, true_val, false_val): Functional ternary branch. Returns true_val if condition evaluates to true; otherwise returns false_val.
• NULLIF(expr1, expr2): Returns NULL if expr1 == expr2; otherwise returns expr1.
• NANVL(expr1, expr2): Used for floating-point columns. Returns expr2 if expr1 is NaN (Double.NaN or Float.NaN); otherwise returns expr1.

5. End-to-End Production Scenario Walkthrough

Consider an enterprise e-commerce platform that needs to compute customer loyalty risk tiers. The query must identify high-value customers, compute their days since last purchase, handle missing contact info, and flag inactive accounts:

WITH 
    -- Step 1: Clean transactions and filter for valid sales
    valid_sales AS (
        SELECT 
            order_id,
            customer_id,
            order_amount,
            to_date(order_timestamp) AS order_date
        FROM ecommerce.orders
        WHERE order_status = 'DELIVERED'
    ),
    -- Step 2: Compute per-customer aggregates
    customer_metrics AS (
        SELECT 
            customer_id,
            COUNT(order_id) AS total_orders,
            SUM(order_amount) AS total_revenue,
            MAX(order_date) AS latest_order_date
        FROM valid_sales
        GROUP BY customer_id
    ),
    -- Step 3: Identify VIP threshold using a scalar subquery
    vip_threshold AS (
        SELECT AVG(total_revenue) * 1.5 AS threshold_val
        FROM customer_metrics
    )
-- Final Step: Join with customer catalog and assign operational flags
SELECT 
    c.customer_id,
    concat_ws(' ', c.first_name, c.last_name) AS customer_name,
    COALESCE(c.phone_number, 'NOT_PROVIDED') AS contact_phone,
    cm.total_orders,
    round(cm.total_revenue, 2) AS total_revenue,
    cm.latest_order_date,
    datediff(current_date(), cm.latest_order_date) AS days_inactive,
    CASE 
        WHEN cm.total_revenue >= (SELECT threshold_val FROM vip_threshold) THEN 'VIP Tier'
        WHEN cm.total_orders >= 5 THEN 'Loyal Regular'
        ELSE 'Standard Customer'
    END AS loyalty_segment,
    IF(datediff(current_date(), cm.latest_order_date) > 90, 'CHURN_RISK', 'ACTIVE') AS risk_status
FROM customer_metrics cm
JOIN ecommerce.customers c ON cm.customer_id = c.customer_id
WHERE c.customer_id NOT IN (
    -- Filter out test and internal accounts, ensuring NULLs are excluded
    SELECT customer_id FROM ecommerce.internal_test_accounts WHERE customer_id IS NOT NULL
)
ORDER BY cm.total_revenue DESC;