Real QA Scenarios — SQL That Saved Hours

-- Step 1: Does the user exist?
SELECT id, first_name, email, status FROM users
WHERE email = 'customer@email.com';

-- Step 2: Check their orders
SELECT order_id, status, total_amount, created_at
FROM orders
WHERE user_id = 1234
ORDER BY created_at DESC;

-- Step 3: Maybe the order has a different status
SELECT order_id, status, total_amount, created_at
FROM orders
WHERE user_id = 1234
  AND status IN ('cancelled', 'failed', 'refunded');

-- Step 4: Check if order exists but is assigned to wrong user
SELECT * FROM orders
WHERE order_id = 'ORD-5678';  -- Use the order ID from email confirmation

-- Check order status
SELECT order_id, status, total_amount, payment_status
FROM orders
WHERE order_id = 'ORD-5678';

-- Check payment records
SELECT * FROM payments
WHERE order_id = 'ORD-5678';

-- Check if multiple payment attempts exist
SELECT payment_id, amount, status, gateway_response, created_at
FROM payments
WHERE order_id = 'ORD-5678'
ORDER BY created_at;

-- This reveals the bug: payment succeeded but order status
-- was not updated. Classic race condition or callback failure.

-- Check 1: Any users with duplicate emails?
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;

-- Check 2: Any orders with negative amounts?
SELECT * FROM orders WHERE total_amount < 0;

-- Check 3: Any orphan records (orders for deleted users)?
SELECT o.* FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

-- Check 4: Any future-dated records?
SELECT * FROM orders WHERE created_at > NOW();

-- Check 5: Data consistency — order total vs sum of items
SELECT
  o.order_id,
  o.total_amount AS order_total,
  SUM(oi.quantity * oi.unit_price) AS calculated_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount != SUM(oi.quantity * oi.unit_price);