SQL Batch Processing — Basics of Window Functions, UPSERT

SELECT
  oi.item_id,
  oi.order_id,
  p.name         AS product_name,
  COALESCE(c.name, 'Uncategorized') AS category_name, -- Uncategorized = a product with no LEFT JOIN match
  oi.qty,
  oi.price * oi.qty AS subtotal        -- Subtotal = unit price × quantity

FROM       order_items oi
INNER JOIN products   p             -- Products are required, so use INNER JOIN
  ON oi.product_id = p.product_id
LEFT JOIN  categories c             -- Keep products without categories (LEFT JOIN)
  ON p.category_id = c.category_id

ORDER BY oi.item_id;

/*
  Logical execution order (three-table JOIN query):
  1. FROM order_items oi          → Read rows
  2. INNER JOIN products p ON ... → Join (matching rows only)
  3. LEFT JOIN categories c ON ...→ Join (keep every row from the left table)
  4. SELECT                       → Evaluate columns (subtotal)
  5. ORDER BY oi.item_id          → Sort and return
*/

SELECT
  'email'  AS notification_type,  -- Add the type as a fixed value
  user_id,
  subject  AS content,            -- Normalize the column name to content
  sent_at
FROM  email_notifications

UNION ALL                         -- Stack vertically without deduplication (fast)
SELECT
  'push'   AS notification_type,  -- Add the type as a fixed value
  user_id,
  message  AS content,            -- Match the column name used by email
  sent_at
FROM  push_notifications

ORDER BY sent_at DESC;  -- Sort the entire UNION result newest first

/*
  Logical execution order (UNION ALL query):
  1. Upper SELECT from email_notifications → Read rows
  2. Lower SELECT from push_notifications  → Read rows
  3. UNION ALL                      → combine the sets
  4. ORDER BY sent_at DESC          → Sort and return
*/

BEGIN;  -- Start the transaction (uncommitted until COMMIT)

-- ① Add the order header to orders
INSERT INTO orders (order_id, user_id, total_amount, status)
VALUES (1001, 'U01', 1500, 'pending');

-- ② Add the order line item to order_items
INSERT INTO order_items (order_id, product_id, qty, price)
VALUES (1001, 'P01', 3, 500);

-- ③ Decrease products inventory by the purchased quantity
UPDATE products
SET   stock = stock - 3    -- Decrease stock by 3 (relative update)
WHERE product_id = 'P01';  -- WHERE is required (without it, every row is updated)

COMMIT;  -- All operations succeeded → commit permanently (visible to other sessions)

-- Verify the committed state
SELECT order_id, user_id, total_amount, status FROM orders ORDER BY order_id;
SELECT order_id, product_id, qty, price FROM order_items ORDER BY order_id, product_id;
SELECT product_id, name, stock FROM products ORDER BY product_id;

/*
  Logical execution order (success path):
  1. BEGIN               → Start the transaction
  2. INSERT orders       → Insert rows (uncommitted)
  3. INSERT order_items  → Insert rows (uncommitted)
  4. UPDATE products     → Update the row (uncommitted)
  5. COMMIT              → Commit the changes permanently
  6. Three SELECTs       → Verify the committed tables
  */

-- Sync U01: existing row → update name and updated_at (email is unchanged)
INSERT INTO user_profiles (user_id, name, email, updated_at)
VALUES (
  'U01',
  'Taro Tanaka (renamed)',
  'tanaka@example.com',
  NOW()                               -- Set the sync timestamp
)
ON CONFLICT (user_id)               -- What to do when user_id conflicts (an existing row is present)
DO UPDATE SET                       -- Run UPDATE when a conflict occurs
  name       = EXCLUDED.name,      -- EXCLUDED: keyword for referencing the new value that was about to be inserted
  updated_at = EXCLUDED.updated_at; -- Leaving email out of SET preserves its original value

-- Sync U03: new row → INSERT (no ON CONFLICT occurs)
INSERT INTO user_profiles (user_id, name, email, updated_at)
VALUES (
  'U03',
  'Ichiro Suzuki',
  'suzuki@example.com',
  NOW()
)
ON CONFLICT (user_id)               -- Same UPSERT syntax, but no conflict → normal INSERT
DO UPDATE SET
  name       = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

/*
  Logical execution order (UPSERT / U01 case):
  1. INSERT INTO user_profiles → Check the unique constraint
  2. Detect a unique-constraint violation → Conflict with the existing row
  3. ON CONFLICT DO UPDATE     → Update the existing row

  Logical execution order (UPSERT / U03 case):
  1. INSERT INTO user_profiles → Check the unique constraint
  2. No conflict                  → No constraint is violated
  3. normal INSERT             → Insert rows
*/