| 1757 | |
| 1758 | |
| 1759 | def test_sql_interview_Q28(): |
| 1760 | EmployeeInfo = T( |
| 1761 | """ |
| 1762 | EmpID|EmpFname|EmpLname|Department|Project|Address|DOB|Gender |
| 1763 | 1|Sanjay|Mehra|HR|P1|Hyderabad(HYD)|01/12/1976|M |
| 1764 | 2|Ananya|Mishra|Admin|P2|Delhi(DEL)|02/05/1968|F |
| 1765 | 3|Rohan|Diwan|Account|P3|Mumbai(BOM)|01/01/1980|M |
| 1766 | 4|Sonia|Kulkarni|HR|P1|Hyderabad(HYD)|02/05/1992|F |
| 1767 | 5|Ankit|Kapoor|Admin|P2|Delhi(DEL)|03/07/1994|M |
| 1768 | """ |
| 1769 | ) |
| 1770 | sql_query = """ |
| 1771 | SELECT Department, COUNT(EmpID) as 'EmpNo' FROM EmployeeInfo GROUP BY Department HAVING COUNT(EmpID) < 2; |
| 1772 | """ |
| 1773 | res_table = pw.sql(sql_query, EmployeeInfo=EmployeeInfo) |
| 1774 | expected_table = ( |
| 1775 | EmployeeInfo.groupby(pw.this.Department) |
| 1776 | .reduce(pw.this.Department, EmpNo=pw.reducers.count()) |
| 1777 | .filter(pw.this.EmpNo < 2) |
| 1778 | ) |
| 1779 | assert_table_equality(res_table, expected_table) |
| 1780 | |
| 1781 | |
| 1782 | def test_sql_interview_Q29(): |