SQL Batch Processing — Applied LATERAL, JSONB, EXPLAIN

-- (1) Extract users whose notifications are true (the @> containment operator)
SELECT
  user_id,
  settings ->> 'notifications' AS notifications  -- Extract the value as text
FROM user_settings
WHERE settings @> '{"notifications": true}'::jsonb; -- Check whether it contains the value

-- (2) Count users for each theme setting
SELECT
  settings ->> 'theme' AS theme,  -- Convert to text for the GROUP BY key
  COUNT(*) AS user_count
FROM user_settings
GROUP BY settings ->> 'theme'
ORDER BY theme;

-- (3) Update language from 'en' to 'ja' for user_id=1
UPDATE user_settings
SET settings = jsonb_set(
  settings,             -- JSONB column to update
  '{language}',         -- Path of the key to update
  '"ja"'::jsonb         -- New value (quote strings and cast to JSONB)
)
WHERE user_id = 1;      -- Restrict the update target (required)

/*
  Logical execution order:
  [Query (1)]
  1. FROM        → Read rows
  2. WHERE       → Filter rows
  3. SELECT      → Evaluate columns
  [Query (3)]
  1. FROM/WHERE  → Identify the row to update
  2. jsonb_set   → Prepare the value (generate JSONB)
  3. SET         → Update the column
*/

BEGIN;  -- Start the transaction

-- (1) Insert new products: rows in products_new but not in products_current
INSERT INTO products_current (product_id, name, price)
SELECT n.product_id, n.name, n.price
FROM products_new n
WHERE NOT EXISTS (
  SELECT 1 FROM products_current c WHERE c.product_id = n.product_id
);

-- (2) Delete obsolete products: rows in products_current but not in products_new
DELETE FROM products_current c
WHERE NOT EXISTS (
  SELECT 1 FROM products_new n WHERE n.product_id = c.product_id
);

-- (3) Update changed products: rows present in both tables whose values differ
UPDATE products_current c
SET
  name  = n.name,
  price = n.price
FROM products_new n
WHERE c.product_id = n.product_id
  AND (c.name <> n.name OR c.price <> n.price); -- Only rows with changes

COMMIT;  -- Commit all three DML operations together

SELECT product_id, name, price
FROM products_current
ORDER BY product_id;  -- Verify the synchronized state

/*
  Logical execution order:
  1. BEGIN   → Start the transaction
  2. INSERT  → Add new rows
  3. DELETE  → Remove obsolete rows
  4. UPDATE  → Update changed rows
  5. COMMIT  → Commit the changes
  6. SELECT  → Verify the three synchronized rows
*/

-- ============================================================
-- Step 1: Inspect the execution plan (collect measured values with ANALYZE)
-- ============================================================
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 'U01';

-- ============================================================
-- Step 2: Single-column index (improve Seq Scan to Index Scan)
-- ============================================================
CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders (user_id);                   -- Speed up searches on user_id

-- ============================================================
-- Step 3: Composite index (optimize multiple conditions and sorting)
-- ============================================================
CREATE INDEX CONCURRENTLY idx_orders_user_status_date
ON orders (user_id, status, ordered_at DESC);          -- Put equality conditions first and the sort condition last
-- ============================================================
-- Step 4: Functional index (index the result after applying a function)
-- ============================================================
CREATE INDEX CONCURRENTLY idx_products_name_lower
ON products (LOWER(name));                             -- Makes LOWER(name) searches use an index

-- ============================================================
-- Step 5: Update statistics (improve planner estimates)
-- ============================================================
ANALYZE orders;                        -- Refresh table statistics such as row counts and distribution

/*
  Index-selection flow:
  1. Use EXPLAIN ANALYZE to identify the bottleneck (such as Seq Scan).
  2. Equality condition in WHERE → B-tree single-column index.
  3. Multiple conditions + ORDER BY → composite index (equality conditions first).
  4. Search with a function such as LOWER() → functional index.
  5. JSONB search → GIN index.
*/

-- (1) ROLLUP: Generate hierarchical subtotals and totals from year to category
SELECT
  CASE
    WHEN GROUPING(year) = 1 THEN 'Grand total'          -- Grand total when year is aggregated
    WHEN GROUPING(month) = 1 THEN year::TEXT || ' total'
    ELSE year::TEXT
  END AS year_label,
  CASE
    WHEN GROUPING(month) = 1 THEN '—'
    WHEN GROUPING(category) = 1 THEN month::TEXT || ' total'
    ELSE month::TEXT
  END AS month_label,
  CASE
    WHEN GROUPING(category) = 1 THEN '—'
    ELSE category
  END AS category_label,
  SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(year, month, category)      -- Aggregate hierarchically (year > month > category)
ORDER BY
  GROUPING(year), year NULLS LAST,          -- Detail first, total rows (NULL) last
  GROUPING(month), month NULLS LAST,
  GROUPING(category), category NULLS LAST;

-- (2) CUBE: Generate every combination of the selected columns
SELECT
  year,
  category,
  SUM(amount) AS total_amount,
  GROUPING(year) AS g_year,          -- 1: year is aggregated
  GROUPING(category) AS g_category   -- 1: category is aggregated
FROM sales
GROUP BY CUBE(year, category)        -- 2^2 = 4 aggregate patterns
ORDER BY year NULLS LAST, category NULLS LAST;

/*
  Difference between generated aggregate groups:
  [ROLLUP(year, month, category)]
  1. (year, month, category) : Detail
  2. (year, month)           : Monthly total
  3. (year)                  : Yearly total
  4. ()                      : Grand total

  [CUBE(year, category)]
  1. (year, category) : Detail
  2. (year)           : Yearly total
  3. (category)       : Category total (not included in ROLLUP)
  4. ()               : Grand total
*/