The main functions used to shape date and time data.

DATE_TRUNC('month', '2024-05-15 14:30:00'::TIMESTAMP)
                             -- → 2024-05-01 00:00:00 (truncate to the first day of the month)
DATE_TRUNC('year', ts)  -- → first day of the year (2024-01-01 00:00:00)
DATE_TRUNC('day', ts)   -- → that day at 00:00:00
EXTRACT(MONTH FROM ts)  -- → 5  (get the month as a number)
EXTRACT(YEAR FROM ts)   -- → 2024
TO_CHAR(ts, 'YYYY-MM-DD') -- → '2024-05-15' (convert to any format)

Aggregate the timestamps in the orders table by month. Retrieve month truncated to the first day of the month with DATE_TRUNC, month_label in 'YYYY-MM' format via TO_CHAR, the per-month count order_count, and the total amount total_amount, in ascending order of month.

Expected output (monthly aggregation):

※ May total: 8000+12500=20500, June total: 3200+6700=9900

SELECT
  DATE_TRUNC('month', ordered_at)               AS month,       -- truncate to the first day of the month, 00:00:00
  TO_CHAR(DATE_TRUNC('month', ordered_at),
           'YYYY-MM')                            AS month_label,  -- display format
  COUNT(*)                                      AS order_count,  -- number of orders per month
  SUM(amount)                                    AS total_amount  -- total amount per month
FROM   orders
GROUP BY DATE_TRUNC('month', ordered_at)  -- group by the truncated month
ORDER BY month;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM orders                      → read all 4 rows
  2. DATE_TRUNC('month', ordered_at)  → truncate each row's TIMESTAMP to the month start
  3. GROUP BY DATE_TRUNC(...)         → group by the same month start
  4. COUNT(*), SUM(amount)            → aggregate each group
  5. TO_CHAR(...)                     → format the month label as 'YYYY-MM'
  6. ORDER BY month                   → sort in ascending order of month
  */

Three kinds of rounding functions for handling the fractional part of a number.

ROUND(524.88)     -- → 525   round half up (round up at 0.5 or more)
ROUND(90.20)      -- → 90    (0.2 is rounded down)
ROUND(9.999, 2)  -- → 10.00 round to 2 decimal places
CEIL(90.20)       -- → 91    ceiling; if there is any fraction, always +1
CEIL(90.00)       -- → 90    exactly an integer, so no change
FLOOR(90.80)      -- → 90    floor; always drop the fractional part

From the pre-tax price and tax_rate in the products table, compute the tax-inclusive amount. Attach four columns so they can be compared: with the fraction (tax_raw), ROUND (tax_round), CEIL (tax_ceil), and FLOOR (tax_floor).

Expected output (comparison of rounding methods):

※ Notebook (90.20): note the difference CEIL=91 vs ROUND=FLOOR=90. Even a small fraction is always rounded up by CEIL.

SELECT
  product_id,
  name,
  price,
  tax_rate,
  price * (1 + tax_rate)                AS tax_raw,   -- tax-inclusive (with the fraction)
  ROUND(price * (1 + tax_rate))        AS tax_round,  -- round half up (round up at 0.5 or more)
  CEIL( price * (1 + tax_rate))        AS tax_ceil,   -- round up (if there is any fraction, always +1)
  FLOOR(price * (1 + tax_rate))        AS tax_floor   -- round down (always drop the fractional part)
FROM   products
ORDER BY product_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM products           → read all 3 rows
  2. price * (1 + tax_rate)  → compute the tax-inclusive amount (with the fraction)
  3. ROUND(x)                → round half up
  4. CEIL(x)                 → round up (if there is any fraction, +1)
  5. FLOOR(x)                → round down (drop the fractional part)
  6. ORDER BY product_id     → output in ascending order of product_id
  */

STRING_AGG(column, delimiter) is combined with GROUP BY to concatenate the string values within a group into a single string. It is equivalent to MySQL's GROUP_CONCAT.

STRING_AGG(product_name, ', ')
  -- → 'Coffee, Cake, Tea'  (order is nondeterministic)

STRING_AGG(product_name, ', ' ORDER BY product_name)
  -- → 'Cake, Coffee, Tea'  (order specified by ORDER BY)

ARRAY_AGG(product_name ORDER BY product_name)
  -- → ARRAY['Cake','Coffee','Tea']  (aggregated as an array)

From the order_items table, for each order retrieve products, the product names aggregated comma-separated (in alphabetical order of product_name), and the total quantity total_qty. Output in ascending order of order_id.

Expected output (products aggregated per order):

※ Because ORDER BY product_name is specified, the values are sorted alphabetically.

SELECT
  order_id,
  STRING_AGG(product_name, ', '
    ORDER BY product_name)    AS products,  -- concatenate alphabetically (ORDER BY makes the order deterministic)
  SUM(quantity)              AS total_qty   -- total quantity within the group
FROM   order_items
GROUP BY order_id             -- group by order_id
ORDER BY order_id;            -- output the result in ascending order of order_id

/*
  Execution order (SQL's logical evaluation order):
  1. FROM order_items             → read all 6 rows
  2. GROUP BY order_id            → group by order_id
  3. STRING_AGG(...ORDER BY ...)  → within each group, order product_name alphabetically and join with ', '
  4. SUM(quantity)                → total quantity of each group
  5. ORDER BY order_id            → output in ascending order of order_id
  */

Two kinds of functions that replace a specific pattern in a string with another string.

REPLACE('03-1234-5678', '-', '')
  -- → '0312345678'   replace all occurrences of the fixed string '-' with ''

REGEXP_REPLACE('(090) 9876-5432', '[^0-9]', '', 'g')
  -- → '09098765432'  regex: remove all non-digit characters ('g'=global)

REGEXP_REPLACE('hello world', '\s+', '_', 'g')
  -- → 'hello_world'  replace whitespace with '_'

The contacts table has phone numbers in inconsistent formats. Retrieve ① replace_hyphen, which removes only hyphens with REPLACE, and ② regexp_digits_only, which removes all non-digit characters with REGEXP_REPLACE, and observe the difference between the two results.

Expected output (note that rows 2 and 3 differ):

※ Row 2: REPLACE removes only hyphens → parentheses and spaces remain. REGEXP_REPLACE removes all non-digits. Row 3: REPLACE cannot remove the dots, but REGEXP_REPLACE can.

SELECT
  contact_id,
  phone                                          AS original,          -- the original phone number
  REPLACE(phone, '-', '')                        AS replace_hyphen,    -- remove only '-' (fixed string)
  REGEXP_REPLACE(phone, '[^0-9]', '', 'g')       AS regexp_digits_only -- remove all non-digits
FROM   contacts
ORDER BY contact_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM contacts            → read all 3 rows
  2. REPLACE(phone, '-', '')  → replace every '-' in each row with '' (empty string)
  3. REGEXP_REPLACE(phone,'[^0-9]','','g')
  4. ORDER BY contact_id
  */