Functions

-- Current timestamp
SELECT NOW();

-- Extract parts
SELECT
  YEAR(created) AS year,
  MONTH(created) AS month,
  DAY(created) AS day
FROM my_stripe.charges;

-- Truncate to period
SELECT DATE_TRUNC('month', created) AS month
FROM my_stripe.charges;

-- Date arithmetic
SELECT created + INTERVAL '7 days' AS one_week_later
FROM my_stripe.charges;

-- Difference in days
SELECT DATE_PART('day', NOW() - created) AS days_ago
FROM my_stripe.charges;

-- Concatenation
SELECT firstname || ' ' || lastname AS full_name
FROM crm.contacts;

-- Lower/upper case
SELECT LOWER(email), UPPER(name)
FROM my_stripe.customers;

-- Substring
SELECT SUBSTRING(email FROM 1 FOR 5)
FROM my_stripe.customers;

-- Split and extract
SELECT SPLIT_PART(email, '@', 2) AS domain
FROM my_stripe.customers;

-- Replace
SELECT REPLACE(phone, '-', '')
FROM crm.contacts;

-- Length
SELECT email, LENGTH(email) AS email_length
FROM my_stripe.customers;

-- Rounding
SELECT ROUND(amount / 100.0, 2) AS dollars
FROM my_stripe.charges;

-- Floor/ceiling
SELECT FLOOR(amount / 100.0), CEIL(amount / 100.0)
FROM my_stripe.charges;

-- Absolute value
SELECT ABS(refund_amount)
FROM my_stripe.charges;

-- Formatting
SELECT FORMAT('{:,.2f}', amount / 100.0) AS formatted
FROM my_stripe.charges;

-- CASE expression
SELECT
  email,
  CASE
    WHEN amount >= 10000 THEN 'high'
    WHEN amount >= 1000 THEN 'medium'
    ELSE 'low'
  END AS tier
FROM my_stripe.charges;

-- COALESCE (first non-null)
SELECT COALESCE(name, email, 'Unknown') AS display_name
FROM my_stripe.customers;

-- NULLIF
SELECT NULLIF(status, 'pending') AS active_status
FROM my_stripe.charges;

-- IIF (inline if)
SELECT IIF(amount > 5000, 'Large', 'Small') AS size
FROM my_stripe.charges;

-- Running total
SELECT
  created,
  amount,
  SUM(amount) OVER (ORDER BY created) AS running_total
FROM my_stripe.charges
WHERE status = 'succeeded';

-- Rank
SELECT
  email,
  SUM(amount) AS total,
  RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM my_stripe.charges
JOIN my_stripe.customers ON charges.customer = customers.id
GROUP BY email;

-- Lag/Lead (previous/next row)
SELECT
  created,
  amount,
  LAG(amount) OVER (ORDER BY created) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY created) AS change
FROM my_stripe.charges;

-- Extract from JSON
SELECT
  metadata->>'order_id' AS order_id,
  metadata->>'source' AS source
FROM my_stripe.charges
WHERE metadata IS NOT NULL;

-- JSON array access
SELECT metadata->'items'->>0 AS first_item
FROM orders;