games:
  - type: command-sprint
    title: "SQL Query Sprint"
    challenges:
      - prompt: "Get all users from the users table."
        answer: "SELECT * FROM users;"
        alternates: ["SELECT * FROM users", "select * from users;"]
        timeLimit: 15
      - prompt: "Get all users older than 25. Table has columns id, name, age."
        answer: "SELECT * FROM users WHERE age > 25;"
        alternates: ["SELECT * FROM users WHERE age>25", "select * from users where age > 25;"]
        timeLimit: 18
      - prompt: "Get users with names starting with 'J'. Use the LIKE operator."
        answer: "SELECT * FROM users WHERE name LIKE 'J%';"
        alternates: ["SELECT * FROM users WHERE name LIKE 'J%'", "select * from users where name like 'J%';"]
        timeLimit: 20
      - prompt: "Get the count of users in the users table."
        answer: "SELECT COUNT(*) FROM users;"
        alternates: ["SELECT COUNT(*) FROM users", "select count(*) from users;"]
        timeLimit: 18
      - prompt: "Get users ordered by name alphabetically (A to Z)."
        answer: "SELECT * FROM users ORDER BY name ASC;"
        alternates: ["SELECT * FROM users ORDER BY name", "select * from users order by name asc;"]
        timeLimit: 20
      - prompt: "Get user names and their order totals. Join users (id, name) with orders (user_id, total)."
        answer: "SELECT users.name, orders.total FROM users INNER JOIN orders ON users.id = orders.user_id;"
        alternates: ["SELECT users.name, orders.total FROM users JOIN orders ON users.id = orders.user_id"]
        timeLimit: 22
      - prompt: "Get all users and their order count. Include users with zero orders. Table orders has user_id."
        answer: "SELECT users.name, COUNT(orders.id) FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id, users.name;"
        alternates: ["SELECT users.name, COUNT(orders.id) FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id, users.name"]
        timeLimit: 25
      - prompt: "Get department names and the average salary per department. Table employees has dept_id, salary; departments has id, name."
        answer: "SELECT departments.name, AVG(employees.salary) FROM departments INNER JOIN employees ON departments.id = employees.dept_id GROUP BY departments.id, departments.name;"
        alternates: ["SELECT d.name, AVG(e.salary) FROM departments d INNER JOIN employees e ON d.id = e.dept_id GROUP BY d.id, d.name"]
        timeLimit: 25
      - prompt: "Get departments with more than 5 employees. Join departments and employees on dept_id."
        answer: "SELECT departments.name FROM departments INNER JOIN employees ON departments.id = employees.dept_id GROUP BY departments.id, departments.name HAVING COUNT(*) > 5;"
        alternates: ["SELECT d.name FROM departments d INNER JOIN employees e ON d.id = e.dept_id GROUP BY d.id, d.name HAVING COUNT(*) > 5"]
        timeLimit: 25
      - prompt: "Get products with price above the average product price. Use a subquery."
        answer: "SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);"
        alternates: ["SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products)"]
        timeLimit: 22

  - type: bug-hunt
    title: "Query Bug Finder"
    snippets:
      - code: |
          SELECT department, name, COUNT(*)
          FROM employees
          GROUP BY department;
        bugLine: 1
        explanation: "The column 'name' is in SELECT but not in GROUP BY. Every non-aggregated column in SELECT must appear in the GROUP BY clause."
        hint: "Every non-aggregate column in SELECT must appear in GROUP BY."
      - code: |
          SELECT users.name, orders.total
          FROM users
          LEFT JOIN orders ON users.id = orders.user_id
          WHERE orders.total > 100;
        bugLine: 4
        explanation: "Using WHERE orders.total > 100 with a LEFT JOIN filters out users with no orders (NULL), turning it into an effective INNER JOIN. Use a subquery or move the filter to preserve LEFT JOIN semantics."
        hint: "LEFT JOIN + WHERE on the right table excludes NULL rows. What happens to users with no orders?"
      - code: |
          SELECT * FROM users
          WHERE age = 25
          ODER BY name;
        bugLine: 3
        explanation: "Typo: ODER should be ORDER. The SQL keyword for sorting is ORDER BY."
        hint: "Check the spelling of the sort keyword."
      - code: |
          SELECT category, SUM(price) AS total
          FROM products
          GROUP BY category
          WHERE total > 1000;
        bugLine: 4
        explanation: "WHERE cannot reference aggregate aliases or aggregates. Use HAVING for aggregate conditions: HAVING SUM(price) > 1000."
        hint: "WHERE runs before grouping. Aggregate conditions need HAVING."
      - code: |
          SELECT name, email
          FROM users
          JOIN orders ON id = orders.user_id;
        bugLine: 3
        explanation: "Ambiguous column: 'id' could refer to users.id or orders.id. Qualify with the table name: users.id."
        hint: "When two tables have a column with the same name, you must specify which table."
      - code: |
          UPDATE users
          SET status = 'inactive';
        bugLine: 2
        explanation: "Missing WHERE clause. This updates every row in the table. Always add a WHERE clause to limit which rows are updated."
        hint: "An UPDATE without WHERE affects every row. Always verify the filter."
      - code: |
          SELECT name, MAX(salary)
          FROM employees
          GROUP BY department;
        bugLine: 1
        explanation: "SELECT includes 'name' which is not in GROUP BY. Either add name to GROUP BY or use an aggregate/subquery for name."
        hint: "Non-aggregated columns in SELECT must appear in GROUP BY."
      - code: |
          SELECT users.name
          FROM users
          INNER JOIN orders ON id = orders.user_id;
        bugLine: 3
        explanation: "Ambiguous column reference: 'id' could mean users.id or orders.id. Qualify it as users.id."
        hint: "Which table does 'id' belong to? Both might have it."