ERP Agent: SQL artifacts executed by PostgreSQL

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

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

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

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 COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE)) > 0
   OR COUNT(*) FILTER (WHERE EXTRACT(YEAR FROM hire_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1) > 0

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)

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

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

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 < 1095
GROUP BY bucket
ORDER BY bucket

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

WITH employee_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 employed_month
    FROM employees e
)
SELECT 
    em.emp_id,
    to_char(em.employed_month, 'YYYY-MM') AS month
FROM employee_employed_months em
LEFT JOIN salaries s 
    ON em.emp_id = s.emp_id 
    AND date_trunc('month', s.pay_date) = em.employed_month
WHERE s.emp_id IS NULL