In SQL, NULL represents a missing or unknown value. Arithmetic with NULL and string concatenation with NULL both produce NULL. Because a NULL in an API response can cause unexpected behavior, explicit NULL handling is essential.

COALESCE(col, 'default value')   -- Evaluate left to right and return the first non-NULL value
col IS NULL                       -- Test for NULL (= NULL is invalid; always use IS NULL)
col IS NOT NULL                   -- Test that a value is not NULL
NULLIF(col, 0)                   -- Return NULL when col is 0 (for example, to prevent division by zero)

From the users table, return all users. Display 'Not registered' when phone is NULL, and use name as a fallback when display_name is NULL. In a separate query, return the number of users whose phone is NULL.

1. Expected output (all users, NULLs handled):

2. Number of users whose phone is NULL:

-- 1. Return all users with safe defaults for NULLs
SELECT
  user_id,
  COALESCE(display_name, name) AS shown_name,  -- Use name when display_name is NULL
  COALESCE(phone, 'Not registered')    AS phone_display  -- Use 'Not registered' when NULL
FROM  users
ORDER BY user_id;

-- 2. Count users whose phone is NULL
SELECT
  COUNT(*) AS null_phone_count  -- Count after filtering with IS NULL
FROM  users
WHERE phone IS NULL;            -- Test NULL with IS NULL (= NULL is invalid)

/*
  Logical execution order (1. NULL-default query):
  1. FROM users          → Read rows
  2. SELECT              → Evaluate columns (correct NULLs with COALESCE)
  3. ORDER BY user_id    → Sort and return

  Logical execution order (2. NULL-count query):
  1. FROM users          → Read rows
  2. WHERE phone IS NULL → Filter rows
  3. SELECT null_phone_count → Evaluate the aggregate
*/

LIKE performs partial, prefix, and suffix string matching. ILIKE (a PostgreSQL extension) is LIKE without case sensitivity. It is a basic building block for a search API such as GET /products?q=keyword.

WHERE col LIKE  '%keyword%'     -- Partial match (% = any string of zero or more characters)
WHERE col LIKE  'keyword%'      -- Prefix match (starts with keyword)
WHERE col LIKE  '%keyword'      -- Suffix match (ends with keyword)
WHERE col LIKE  'k_yword'       -- _ = any one character (one-character wildcard)
WHERE col ILIKE '%Keyword%'     -- Case-insensitive partial match (PostgreSQL)

From the products table, return products whose name contains 'coffee', ordered by price from low to high (case-insensitive). In a separate query, count products whose SKU code (sku) starts with 'BEV-'.

1. Products whose name contains 'coffee' (price ascending):

2. Number of products whose SKU starts with 'BEV-':

-- 1. Return products whose name contains 'coffee', ordered by price
SELECT
  product_id,
  name,
  sku,
  price
FROM   products
WHERE  name ILIKE '%coffee%'  -- Case-insensitive partial match (PostgreSQL)
ORDER BY price ASC;

-- 2. Count products whose sku starts with 'BEV-'
SELECT
  COUNT(*) AS bev_count
FROM   products
WHERE  sku LIKE 'BEV-%';  -- Prefix match (starts with 'BEV-')

/*
  Logical execution order (1. ILIKE partial-match query):
  1. FROM products            → Read rows
  2. WHERE name ILIKE ...     → Filter rows
  3. SELECT                   → Evaluate columns
  4. ORDER BY price ASC       → Sort and return

  Logical execution order (2. LIKE prefix-count query):
  1. FROM products            → Read rows
  2. WHERE sku LIKE 'BEV-%'   → Filter rows
  3. SELECT bev_count         → Evaluate the aggregate
*/

IN tests whether a column value appears in a specified list or subquery result set. NOT IN returns rows that are not included. EXISTS checks whether a subquery returns at least one row.

WHERE col IN ('a', 'b', 'c')      -- col matches one of 'a', 'b', or 'c'
WHERE col IN (SELECT id FROM t)  -- Included in the subquery result set
WHERE col NOT IN ('x', 'y')      -- col is neither 'x' nor 'y'
WHERE EXISTS (                    -- Check whether the subquery finds one or more rows
  SELECT 1 FROM t
  WHERE  t.id = outer.id           -- Correlate with the outer query
)

1. From the orders table, return orders whose status is 'completed' or 'pending'. 2. From the same table, return orders whose purchase_id does not exist in the purchases table (unprocessed orders).

1. Orders with completed or pending status (order_id ascending):

2. Unprocessed orders not present in purchases (order_id: 2, 3, 5):

-- 1. Return completed or pending orders (using IN)
SELECT
  order_id,
  user_id,
  amount,
  status
FROM   orders
WHERE  status IN ('completed', 'pending')  -- Match either value concisely instead of OR
ORDER BY order_id;

-- 2. Orders not present in purchases (using NOT EXISTS)
SELECT
  o.order_id,
  o.user_id,
  o.amount,
  o.status
FROM   orders o
WHERE  NOT EXISTS (                  -- No subquery row means the order is absent
  SELECT 1                           -- The value is irrelevant for an existence test
  FROM   purchases p
  WHERE  p.purchase_id = o.order_id  -- Correlate with the outer query
)
ORDER BY o.order_id;

/*
  Logical execution order (1. IN query):
  1. FROM orders           → Read rows
  2. WHERE status IN (...) → Filter rows
  3. SELECT                → Evaluate columns
  4. ORDER BY order_id     → Sort and return

  Logical execution order (2. NOT EXISTS query):
  1. FROM orders o         → Read rows
  2. WHERE NOT EXISTS      → Evaluate the subquery and filter rows
  3. SELECT                → Evaluate columns
  4. ORDER BY o.order_id   → Sort and return
*/

Date and time functions are essential for aggregating logs over periods and scheduling batch jobs. Learn the main PostgreSQL functions.

NOW()                             -- Current timestamp (with time zone)
CURRENT_DATE                      -- Today’s date (without a time)
DATE_TRUNC('month', col)          -- Truncate col to the first instant of its month (year/day/hour also work)
EXTRACT(YEAR FROM col)           -- Extract only the year (MONTH, DAY, DOW, and others also work)
col ::DATE                        -- Cast a timestamp to a date
col + INTERVAL '7 days'          -- Timestamp seven days later
col - INTERVAL '1 month'         -- Timestamp one month earlier

From the orders table, aggregate the number of orders and total amount per month. Display the month in 'YYYY-MM' format and show the newest month first. Also write a separate query that returns only orders from the past 30 days.

1. Monthly aggregation (newest month first):

2. Orders from the past 30 days (reference date: 2024-06-03, ordered_at descending):

-- 1. Aggregate order count and total amount by month (newest first)
SELECT
  TO_CHAR(DATE_TRUNC('month', ordered_at), 'YYYY-MM') AS month,  -- Truncate to month start, then format as YYYY-MM
  COUNT(*)    AS order_count,
  SUM(amount) AS total_amount
FROM     orders
GROUP BY DATE_TRUNC('month', ordered_at)   -- Group by month
ORDER BY DATE_TRUNC('month', ordered_at) DESC;  -- Newest month first

-- 2. Return only orders from the past 30 days
SELECT
  order_id,
  user_id,
  amount,
  ordered_at
FROM   orders
WHERE  ordered_at >= NOW() - INTERVAL '30 days'  -- The last 30 days
ORDER BY ordered_at DESC;

/*
  Logical execution order (1. monthly aggregation):
  1. FROM orders             → Read rows
  2. GROUP BY DATE_TRUNC     → Group rows
  3. SELECT                  → Evaluate COUNT and SUM
  4. ORDER BY ... DESC       → Sort and return

  Logical execution order (2. past-30-days query):
  1. FROM orders             → Read rows
  2. WHERE ordered_at >= ... → Filter rows
  3. SELECT                  → Evaluate columns
  4. ORDER BY ordered_at DESC → Sort and return
*/