SELECT
  user_id,
  plan,
  expires_at::DATE - CURRENT_DATE                AS days_remaining,      -- DATE - DATE → INTEGER days; positive=remaining, negative=overdue
  CASE                                                                   -- Classify expiration status into three levels
    WHEN expires_at::DATE < CURRENT_DATE            THEN 'Expired'
    WHEN (expires_at::DATE - CURRENT_DATE) <= 30   THEN 'Expiring Soon'
    ELSE                                                    'Active'
  END                                              AS status,
  TO_CHAR(                                                               -- Next renewal = current expiration + one year; format with TO_CHAR
    expires_at::DATE + INTERVAL '1 year',
    'YYYY-MM-DD'
  )                                                AS renewal_at
FROM   subscriptions
ORDER BY days_remaining;                                                 -- Ascending days remaining, with expired rows first

/*
  Execution order (logical SQL evaluation order):
  1. FROM subscriptions                    → Read all four rows
  2. expires_at::DATE - CURRENT_DATE       → Calculate days remaining
  3. CASE WHEN                             → Classify each row into three statuses
  4. expires_at::DATE + INTERVAL '1 year'  → Calculate the date one year later
  5. TO_CHAR(..., 'YYYY-MM-DD')            → Format the date as text
  6. ORDER BY days_remaining               → Return rows by ascending days remaining
  */

-- Calculate the discount rate in a CTE first to keep the CASE expression DRY
WITH ranked AS (
  SELECT
    order_id,
    customer_id,
    customer_rank,
    subtotal,
    CASE customer_rank                                          -- Simple CASE equality form, equivalent to CASE WHEN rank = 'x'
      WHEN 'platinum' THEN 0.20                                 -- Platinum: 20% discount
      WHEN 'gold'     THEN 0.10                                 -- Gold: 10% discount
      WHEN 'silver'   THEN 0.05                                 -- Silver: 5% discount
      ELSE                 0.00                                 -- Bronze or another value: no discount
    END                            AS discount_rate
  FROM   orders
)
SELECT
  order_id,
  customer_rank,
  subtotal,
  discount_rate,
  ROUND(subtotal * (1 - discount_rate))        AS discounted,   -- Round the discounted amount, using the CTE's discount_rate
  GREATEST(                                                     -- Guarantee a minimum price of 500 with a lower clamp
    ROUND(subtotal * (1 - discount_rate)),
    500
  )                                             AS final_price
FROM   ranked
ORDER BY order_id;

/*
  Execution order (logical SQL evaluation order):
  1. CTE (WITH ranked)
  2. Main query
  */

SELECT
  category,
  COUNT(*)                                                          AS total_sales,
  COUNT(*) FILTER (WHERE status = 'completed')                   AS completed_count,       -- Count only completed transactions without changing group size
  SUM(amount) FILTER (WHERE status = 'completed')               AS completed_amount,       -- Sum completed amounts, excluding cancellations and refunds
  ROUND(                                                                                   -- Completion rate: use NUMERIC division and round to one decimal
    100.0 * COUNT(*) FILTER (WHERE status = 'completed')
           / COUNT(*),
    1
  )                                                                   AS completion_rate,
  STRING_AGG(DISTINCT product_name, ', '                                                   -- Deduplicate and sort all product names, regardless of status
    ORDER BY product_name)                                          AS products
FROM   sales
GROUP BY category
ORDER BY category;

/*
  Execution order (logical SQL evaluation order):
  1. FROM sales                           → Read all eight rows
  2. GROUP BY category                    → Form three groups
  3. COUNT(*)                             → Count every row in each group
  4. COUNT(*) FILTER (...)                → Count completed rows only
  5. SUM(amount) FILTER (...)             → Sum completed amounts
  6. ROUND(100.0 * completed / total, 1)  → Calculate the completion rate
  7. STRING_AGG(DISTINCT ...)             → Deduplicate and concatenate in lexical order
  8. ORDER BY category                    → Return categories ascending
  */

SELECT
  log_id,
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[1]          AS log_level,  -- Split into [1] level, [2] code, and [3] path
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[2]::INTEGER AS status_code,           -- Cast the second group, the status-code string, to INTEGER
  (REGEXP_MATCHES(log_message, '(\w+) (\d+) (.*)'))[3]          AS endpoint,              -- Third group: endpoint path
  REGEXP_REPLACE(log_date, '^(\d{4})(\d{2})(\d{2})$', '\1-\2-\3') AS formatted_date,  -- Reformat 'YYYYMMDD' to 'YYYY-MM-DD' with capture groups
  log_message ~ '^(ERROR|WARN)'                                  AS is_error  -- Match ERROR or WARN at the start of the line
FROM   app_logs
ORDER BY log_id;

/*
  Execution order (logical SQL evaluation order):
  1. FROM app_logs        → Read all five rows
  2. REGEXP_MATCHES(...)  → Extract three regular-expression groups
  3. REGEXP_REPLACE(...)  → Reformat the date string
  4. Test log_message prefix → Classify ERROR/WARN as BOOLEAN
  5. ORDER BY log_id      → Return rows ascending
  */