DELETE physically removes rows from a table. A soft delete keeps the row and records the deletion time in a deleted_at column. Soft deletion is common in API development because the data can be restored and audited.

-- Hard delete: permanently remove a row
DELETE FROM table_name             -- Target table
WHERE id = 1;                       -- Required; omitting it deletes every row

-- Soft delete: keep the row and record its deletion time
UPDATE table_name
SET    deleted_at = NOW()           -- Change NULL to the current timestamp
WHERE  id = 1
  AND  deleted_at IS NULL;          -- Only rows not yet deleted (idempotent)

In the comments table, soft-delete the comment with comment_id=3 by recording the current time in deleted_at. Also write a query that returns only active comments, excluding rows that have been soft-deleted.

Active comments after the soft delete (only rows where deleted_at IS NULL):

ID 3 is deleted by this operation, and ID 4 was already deleted, so both are excluded.

-- ① Soft-delete comment 3 by recording the current time
UPDATE comments
SET    deleted_at = NOW()   -- Record the deletion time; do not remove the row
WHERE  comment_id = 3
  AND  deleted_at IS NULL;  -- Only undeleted rows

-- ② Return active comments whose deleted_at is NULL
SELECT
  comment_id,
  post_id,
  body
FROM   comments
WHERE  deleted_at IS NULL  -- Undeleted rows only
ORDER BY comment_id ASC;

/*
  Logical execution order for the SELECT:
  1. FROM comments               → Read rows
  2. WHERE deleted_at IS NULL    → Filter rows
  3. SELECT                      → Evaluate columns
  4. ORDER BY comment_id         → Sort and return
*/

A CTE (Common Table Expression) defines a temporary named result set with WITH that later parts of the query can reference. CTEs make complex batch queries easier to read by separating them into clear steps.

WITH cte1 AS (                 -- Define the first CTE
  SELECT col1, col2
  FROM   table_a
  WHERE  ...
),
cte2 AS (                      -- Separate CTEs with commas
  SELECT col1
  FROM   cte1                  -- Reference an earlier CTE
  WHERE  ...
)
SELECT *
FROM cte2;                      -- Finish with the main query

Identify users who placed at least two orders in the last 30 days as VIP customers. Using the orders and users tables, split the query into two CTE stages and return each VIP user's user_id, name, and order_count in descending order of order_count. Assume the current date is 2024-06-01.

Return VIP users with at least two orders in the last 30 days, sorted by order_count descending:

U03 has no orders in the last 30 days and is excluded.

WITH
  recent_orders AS (               -- CTE 1: Count each user's orders in the last 30 days 
    SELECT
      user_id,
      COUNT(*) AS order_count
    FROM   orders
    WHERE  ordered_at >= CURRENT_DATE - INTERVAL '30 days'  -- last 30 days
    GROUP BY user_id
  ),
  vip_users AS (                   -- CTE 2: Keep users with at least two orders 
    SELECT user_id, order_count
    FROM   recent_orders            -- Read the result of the first CTE
    WHERE  order_count >= 2
  )
SELECT
  v.user_id,
  u.name,
  v.order_count
FROM       vip_users v
INNER JOIN users    u              -- join names to VIP users
  ON  v.user_id = u.user_id
ORDER BY v.order_count DESC;

/*
  Logical execution order:
  1. CTE recent_orders          → Build recent-order counts
  2. CTE vip_users              → Keep counts of at least two
  3. FROM vip_users v            → Read rows
  4. INNER JOIN users u          → Join matching rows
  5. SELECT                      → Evaluate columns
  6. ORDER BY order_count DESC   → Sort and return
*/

EXISTS evaluates to TRUE when its subquery returns at least one row and FALSE otherwise. It is frequently used in batch jobs to select rows that have related data or rows that have not yet been processed.

SELECT col1
FROM   table_a a
WHERE EXISTS (                 -- TRUE when the subquery returns a row
  SELECT 1                    -- The selected value is irrelevant
  FROM   table_b b
  WHERE  b.a_id = a.id       -- Correlate with the outer row
);

WHERE NOT EXISTS (...)            -- TRUE when the subquery returns no rows

From the users table, retrieve users who have never placed an order—that is, users with no matching row in orders. Return user_id and name, sorted by user_id ascending.

Users who have never placed an order (no matching row in orders):

User 1 has two orders and user 3 has one, so both are excluded.

SELECT
  u.user_id,
  u.name
FROM  users u
WHERE NOT EXISTS (              -- Keep only users with no orders 
  SELECT 1                      -- The value does not matter for an existence test
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- Correlate orders with the outer user
)
ORDER BY u.user_id ASC;

/*
  Logical execution order:
  1. FROM users u               → Read rows
  2. NOT EXISTS (subquery) → Check for matching orders
  3. SELECT                     → Evaluate columns
  4. ORDER BY u.user_id         → Sort and return
*/

CASE WHEN is SQL's if-else expression. It can be used in SELECT, WHERE, ORDER BY, GROUP BY, and many other contexts. It is common in APIs for converting stored values into labels and in batch aggregation for conditional counts.

-- Simple CASE: Branch on enumerated column values: 
CASE col1
  WHEN 'val1' THEN 'displayA'         -- col1 = 'val1'  
  WHEN 'val2' THEN 'displayB'         -- col1 = 'val2'  
  ELSE 'Other'                    -- When nothing matches (NULL if omitted)
END                                -- Always close CASE with END

-- Searched CASE: Branch on any condition; more flexible: 
CASE
  WHEN col2 > 100 THEN 'Large'         -- Comparison operators can be used
  WHEN col2 > 50  THEN 'Medium'         -- Evaluate top to bottom and return the first matching THEN
  ELSE 'Small'
END

For each row in orders, assign a purchase rank based on amount: premium for 10000 or more, standard for 5000 or more, and basic otherwise. Also convert status codes to display labels. Return order_id, amount, rank_label, and status_label, sorted by ordered_at descending.

Expected output, with amount mapped to a rank and status mapped to an English label:

SELECT
  order_id,
  amount,
  CASE                              --  purchase rank 
    WHEN amount >= 10000 THEN 'premium'
    WHEN amount >=  5000 THEN 'standard'  -- Evaluate top to bottom (use the first match)
    ELSE                       'basic'
  END AS rank_label,
  CASE status                      -- Branch on a column value (simple CASE)
    WHEN 'completed' THEN 'Completed'
    WHEN 'pending'   THEN 'Pending'
    WHEN 'cancelled' THEN 'Cancelled'
    ELSE 'Unknown'                      -- Fallback for unexpected values
  END AS status_label

FROM   orders
ORDER BY ordered_at DESC;

/*
  Logical execution order:
  1. FROM orders                → Read rows
  2. SELECT                     → Evaluate rank_label and status_label
  3. ORDER BY ordered_at        → Sort and return
*/