WITH current_senior_employees AS (
    SELECT *
    FROM employee
    WHERE age>=45
        AND is_current = true
        AND years_in_company >= 6
),
performance_last_5years AS (
    SELECT *
    FROM performance_review
    WHERE year BETWEEN 2016 AND 2020
)
SELECT
    ARBITRARY(first_name), 
    ARBITRARY(last_name), 
    ARBITRARY(email),    
    SUM(num_of_managed_employees) AS total_num_managed_employees,
    SUM(num_of_managed_projects) AS total_num_of_managed_projects,
    SUM(revenue_generated_by_managed_projects) AS total_revenue
FROM current_senior_employees
LEFT JOIN avg_performance_last_5years
ON id = employee_id
GROUP BY employee_id