SQL Functions and Aggregation

SELECT first_name || ' ' || last_name AS full_name
FROM users;

# Join multiple strings into one value.

SELECT UPPER(email) AS email_upper, LOWER(email) AS email_lower
FROM users;

# Convert strings to uppercase or lowercase.

SELECT TRIM(name) AS cleaned_name
FROM customers;

# Remove spaces from the left, right, or both sides.

SELECT SUBSTRING(phone FROM 1 FOR 3) AS area_code
FROM contacts;

# Return part of a string.

SELECT REPLACE(phone, '-', '') AS digits_only
FROM contacts;

# Substitute one substring for another.

SELECT CURRENT_DATE AS today, CURRENT_TIMESTAMP AS now_ts;

# Return the current date and current timestamp.

SELECT
  EXTRACT(YEAR FROM created_at) AS year_num,
  EXTRACT(MONTH FROM created_at) AS month_num
FROM orders;

# Return year, month, day, and other pieces from a date or timestamp.

SELECT created_at, created_at + INTERVAL '7 days' AS plus_seven_days
FROM orders;

# Shift a date or timestamp forward or backward.

SELECT CAST(created_at AS DATE) AS created_date
FROM orders;

# Remove the time portion from a timestamp.

SELECT order_id,
       CASE
         WHEN amount >= 1000 THEN 'large'
         WHEN amount >= 100 THEN 'medium'
         ELSE 'small'
       END AS bucket
FROM orders;

# Return different values based on conditions.

SELECT COALESCE(phone, mobile_phone, 'N/A') AS preferred_phone
FROM contacts;

# Return the first non-NULL value in a list.

SELECT revenue / NULLIF(order_count, 0) AS revenue_per_order
FROM daily_metrics;

# Return NULL when two expressions are equal.

SELECT CAST(total AS DECIMAL(12,2)) AS total_decimal
FROM invoices;

# Cast an expression to a different type.

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

# Count unique entries in a column.

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders
FROM orders;

# Compute multiple categorized totals in one query.

SELECT CAST(created_at AS DATE) AS day, COUNT(*) AS total
FROM events
GROUP BY CAST(created_at AS DATE)
ORDER BY day;

# Roll up metrics by calendar day.