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