ERP Agent: SQL artifacts executed by PostgreSQL

1. 平均每个员工在职多久？

SELECT AVG(COALESCE(leave_date, CURRENT_DATE) - hire_date) AS average_tenure_days FROM employees

PASS: independent Python reference matched

2. 每个部门有多少在职员工？

SELECT department, COUNT(*) AS active_count FROM employees WHERE leave_date IS NULL GROUP BY department

PASS: independent Python reference matched

3. 哪个部门员工平均级别最高？

SELECT department
FROM employees
GROUP BY department
ORDER BY AVG(level) DESC
LIMIT 1

PASS: independent Python reference matched

4. 每个部门今年和去年各新入职多少人？

SELECT department,
       COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE)) AS this_year_count,
       COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) AS last_year_count
FROM employees
GROUP BY department
HAVING this_year_count > 0 OR last_year_count > 0

(no rows)

FAIL: UndefinedColumn: column "this_year_count" does not exist LINE 6: HAVING this_year_count > 0 OR last_year_count > 0 ^

5. 前年3月到去年5月，A部门平均工资是多少？

SELECT AVG(s.salary)
FROM employees e
JOIN salaries s ON e.emp_id = s.emp_id
WHERE e.department = '研发部'
  AND s.pay_date BETWEEN make_date(EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 2, 3, 1)
                      AND make_date(EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 1, 5, 31)

PASS: independent Python reference matched

6. 去年A部门和B部门平均工资哪个高？

SELECT e.department, AVG(s.salary) AS average_salary
FROM employees e
INNER JOIN salaries s ON e.emp_id = s.emp_id
WHERE e.department IN ('研发部', '销售部')
  AND s.pay_date >= date_trunc('year', CURRENT_DATE) - INTERVAL '1 year'
  AND s.pay_date < date_trunc('year', CURRENT_DATE)
GROUP BY e.department

PASS: independent Python reference matched

7. 今年每个级别的员工平均工资是多少？

SELECT e.level, AVG(s.salary) AS average_salary
FROM employees e
JOIN salaries s ON e.emp_id = s.emp_id
WHERE EXTRACT(YEAR FROM s.pay_date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY e.level

PASS: independent Python reference matched

8. 入职一年内、一到两年、两到三年的员工，最近一个月平均工资是多少？

WITH latest_salaries AS (
    SELECT DISTINCT ON (emp_id) emp_id, salary
    FROM salaries
    ORDER BY emp_id, pay_date DESC
)
SELECT 
    CASE 
        WHEN (CURRENT_DATE - e.hire_date)::INTEGER < 365 THEN '入职一年内'
        WHEN (CURRENT_DATE - e.hire_date)::INTEGER BETWEEN 365 AND 729 THEN '一到两年'
        WHEN (CURRENT_DATE - e.hire_date)::INTEGER BETWEEN 730 AND 1094 THEN '两到三年'
    END AS bucket,
    AVG(ls.salary) AS average_latest_salary
FROM employees e
JOIN latest_salaries ls ON e.emp_id = ls.emp_id
WHERE e.leave_date IS NULL
    AND (CURRENT_DATE - e.hire_date)::INTEGER <= 1094
GROUP BY bucket

PASS: independent Python reference matched

9. 去年到今年涨薪幅度最大的10位员工是谁？

WITH employee_salary_avg AS (
    SELECT 
        emp_id,
        AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) AS avg_prev_year,
        AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE)) AS avg_curr_year
    FROM salaries
    GROUP BY emp_id
    HAVING AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) IS NOT NULL
       AND AVG(salary) FILTER (WHERE EXTRACT(YEAR FROM pay_date) = EXTRACT(YEAR FROM CURRENT_DATE)) IS NOT NULL
)
SELECT e.name, (esa.avg_curr_year - esa.avg_prev_year) AS raise_amount
FROM employee_salary_avg esa
JOIN employees e ON esa.emp_id = e.emp_id
ORDER BY raise_amount DESC
LIMIT 10

PASS: independent Python reference matched

10. 有没有拖欠工资的情况（某个月还在职却没有发薪）？

WITH employed_months AS (
    SELECT 
        e.emp_id,
        generate_series(
            date_trunc('month', e.hire_date),
            date_trunc('month', COALESCE(e.leave_date, CURRENT_DATE)),
            interval '1 month'
        ) AS month
    FROM employees e
)
SELECT 
    em.emp_id,
    to_char(em.month, 'YYYY-MM') AS month
FROM employed_months em
LEFT JOIN salaries s 
    ON em.emp_id = s.emp_id 
    AND em.month = date_trunc('month', s.pay_date)
WHERE s.emp_id IS NULL

PASS: independent Python reference matched