SQL Query Grader

Efficient JOIN with proper indexing

SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01' AND u.status = 'active'
GROUP BY u.id, u.name, u.email
HAVING COUNT(o.id) > 0 ORDER BY order_count DESC LIMIT 100;

Proper UNION ALL usage

SELECT name, email FROM active_users WHERE created_at >= '2024-01-01'
UNION ALL
SELECT name, email FROM inactive_users WHERE last_login >= '2023-01-01'
ORDER BY name;

UNION without ALL

SELECT product_name, category FROM products WHERE price > 100
UNION
SELECT product_name, category FROM archived_products WHERE archived_date > '2023-01-01'
ORDER BY product_name;

Functions in WHERE

SELECT * FROM users
WHERE UPPER(email) LIKE '%@GMAIL.COM' AND YEAR(created_at) = 2024
ORDER BY created_at DESC;

Slow correlated subquery

SELECT u.name, u.email,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u WHERE u.status = 'active';

Dangerous cartesian product

SELECT * FROM users u, orders o, products p
WHERE u.created_at > '2024-01-01' AND o.total > 100;