SELECT
  id,
  SPLIT_PART(email, '@', 1)               AS email_user,      -- Split by '@' and take the 1st field (username)
  SPLIT_PART(email, '@', 2)               AS email_domain,    -- Split by '@' and take the 2nd field (domain)
  REGEXP_REPLACE(phone, '-', '', 'g')    AS phone_normalized  -- Replace every '-' with an empty string ('g'=all)
FROM   contacts
ORDER BY id;

/*
  Execution order (SQL logical evaluation order):
  1. FROM contacts                        → Read all 3 rows
  2. SPLIT_PART(email, '@', 1)            → Split by '@' and return the 1st field (left side)
  3. REGEXP_REPLACE(phone, '-', '', 'g')  → Replace all parts matching '-' with ''
  4. ORDER BY id                          → Output in ascending order of id
  */

SELECT
  product_id,
  name,
  price,
  CASE category                              -- Simple CASE: determine the tax rate by category
    WHEN 'food' THEN 0.08                    -- Food gets the reduced tax rate of 8%
    ELSE            0.10                     -- Everything else gets the standard 10%
  END                        AS tax_rate,
  ROUND(                                     -- Round the fractional part to an integer
    price * (1 + CASE category              -- Inline the CASE WHEN inside the computation
      WHEN 'food' THEN 0.08
      ELSE 0.10
    END)
  )                          AS price_with_tax,
  CASE                                       -- Searched CASE: judge the band on the tax-exclusive price
    WHEN price >= 3000 THEN 'premium'        -- Write the higher condition first (the rule of searched CASE)
    WHEN price >= 1000 THEN 'standard'
    ELSE                    'budget'
  END                        AS price_rank
FROM   products
ORDER BY product_id;

/*
  Execution order (SQL logical evaluation order):
  1. FROM products             → Read all 5 rows
  2. CASE category             → Determine the tax rate by category
  3. price * (1 + rate) / ROUND  → Compute the tax-included amount and round
  4. CASE WHEN price …         → Rank by price
  5. ORDER BY product_id       → Output in ascending order
  */

SELECT
  project_id,
  COUNT(*)                                               AS task_count,
  SUM(CASE WHEN completed THEN 1 ELSE 0 END)           AS completed_count, -- Add 1 only for completed tasks
  COALESCE(SUM(points), 0)                              AS total_points,    -- Ignore NULL rows; 0 if all rows are NULL
  ROUND(
    SUM(CASE WHEN completed THEN 1 ELSE 0 END)::NUMERIC  -- INTEGER→NUMERIC conversion to secure decimal arithmetic
    / NULLIF(COUNT(*), 0) * 100                           -- Return NULL when COUNT=0 to prevent zero division
  )                                                       AS completion_rate
FROM   tasks
GROUP BY project_id
ORDER BY project_id;

/*
  Execution order (SQL logical evaluation order):
  1. FROM tasks                                     → Read all 6 rows
  2. GROUP BY project_id
  3. COUNT(*)                                       → All row counts within the group (including NULL)
  4. SUM(CASE WHEN completed THEN 1 ELSE 0 END)     → Add 1 only for true rows
  5. COALESCE(SUM(points), 0)                       → SUM automatically ignores NULL rows
  6. SUM(...)::NUMERIC / NULLIF(COUNT(*), 0) * 100  → Compute the completion rate at decimal precision
  7. ROUND(...)                                     → Round the fractional part to an integer
  8. ORDER BY project_id                            → Output in ascending order of project_id
  */

SELECT
  'INV-' || LPAD(invoice_id::TEXT, 6, '0')  AS invoice_no,      -- ①Cast integer to TEXT ②Zero-pad to 6 digits ③Concatenate the prefix
  TO_CHAR(
    ROUND(amount * 1.1),                    -- Round the tax-included amount to an integer
    'FM999,999,999'                          -- FM=strip leading spaces, comma-separated format
  )                                          AS amount_with_tax,
  TO_CHAR(issued_at, 'YYYY/MM/DD')          AS issued_at_jp,    -- Convert the date to formatted form
  COALESCE(
    TO_CHAR(paid_at, 'YYYY-MM-DD'),          -- Convert paid_at to a date string if non-NULL
    'Unpaid'                                 -- 'Unpaid' if paid_at is NULL
  )                                          AS paid_status,
  CASE status                               -- Simple CASE: convert English status to a display label
    WHEN 'paid'    THEN 'Paid'
    WHEN 'pending' THEN 'Awaiting Payment'
    WHEN 'overdue' THEN 'Overdue'
    ELSE               'Unknown'            -- Always write ELSE to guard against unexpected values
  END                                        AS payment_label
FROM   invoices
ORDER BY invoice_id;

/*
  Execution order (SQL logical evaluation order):
  1. FROM invoices                           → Read all 4 rows
  2. invoice_id::TEXT                        → Cast integer to TEXT (type-matching the LPAD argument)
  3. amount * 1.1                            → Compute the tax-included amount (INTEGER×NUMERIC)
  4. TO_CHAR(issued_at, 'YYYY/MM/DD')        → Convert DATE to a formatted string
  5. TO_CHAR(paid_at, 'YYYY-MM-DD')          → Convert paid_at to a date string if non-NULL
  6. CASE status WHEN 'paid' THEN 'Paid' ...  → Convert the status code to a display label
  7. ORDER BY invoice_id                     → Output in ascending order of invoice_id
  */