predict_sql_query_output.sql

/*
Imagine this is table named EmployeeTable in relational database

Role        Name         Building   Years_employed
Engineer    Becky A.     1e         4
Engineer    Dan B.       1e         2
Engineer    Sharon F.    1e         6
Engineer    Dan M.       1e         4
NULL        Malcom S.    1e         1
Artist      Tylar S.     2w         2
Artist      Sherman D.   2w         8
Artist      Jakob J.     2w         6
Artist      Lillia A.    2w         7
Artist      Brandon J.   2w         7
Manager     Scott K.     1e         9
Manager     Shirlee M.   1e         3
Manager     Daria O.     2w         6

 */



SELECT Building, COUNT(1) AS EmployeeCount
FROM EmployeeTable
GROUP BY Building;






SELECT Name
FROM EmployeeTable
WHERE Role = 'Manager'
  AND Years_employed > (SELECT AVG(Years_employed) FROM EmployeeTable);





/*
Repeating table for convenience: 


Role        Name         Building   Years_employed
Engineer    Becky A.     1e         4
Engineer    Dan B.       1e         2
Engineer    Sharon F.    1e         6
Engineer    Dan M.       1e         4
Engineer    Malcom S.    1e         1
Artist      Tylar S.     2w         2
Artist      Sherman D.   2w         8
Artist      Jakob J.     2w         6
Artist      Lillia A.    2w         7
Artist      Brandon J.   2w         7
Manager     Scott K.     1e         9
Manager     Shirlee M.   1e         3
Manager     Daria O.     2w         6

*/




WITH MaxYears AS (SELECT Building, MAX(Years_employed) AS MaxYears
                  FROM EmployeeTable
                  GROUP BY Building)
SELECT E.Name, E.Building
FROM EmployeeTable E
         JOIN MaxYears M ON E.Building = M.Building AND E.Years_employed = M.MaxYears;



SELECT Name, Role, Years_employed,
       RANK() OVER (PARTITION BY Role ORDER BY Years_employed DESC) AS RankWithinRole
FROM EmployeeTable;