| 1579 | |
| 1580 | |
| 1581 | def test_sql_interview_Q22_bis(): |
| 1582 | EmployeeInfo = T( |
| 1583 | """ |
| 1584 | EmpID|EmpFname|EmpLname|Department|Project|Address|DOB|Gender |
| 1585 | 1|Sanjay|Mehra|HR|P1|Hyderabad(HYD)|01/12/1976|M |
| 1586 | 2|Ananya|Mishra|Admin|P2|Delhi(DEL)|02/05/1968|F |
| 1587 | 3|Rohan|Diwan|Account|P3|Mumbai(BOM)|01/01/1980|M |
| 1588 | 4|Sonia|Kulkarni|HR|P1|Hyderabad(HYD)|02/05/1992|F |
| 1589 | 5|Ankit|Kapoor|Admin|P2|Delhi(DEL)|03/07/1994|M |
| 1590 | """ |
| 1591 | ) |
| 1592 | sql_query = """ |
| 1593 | SELECT EmpID, EmpFname, Department, COUNT(*) as cnt |
| 1594 | FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department |
| 1595 | HAVING cnt > 1; |
| 1596 | """ |
| 1597 | res_table = pw.sql(sql_query, EmployeeInfo=EmployeeInfo) |
| 1598 | expected_table = ( |
| 1599 | EmployeeInfo.groupby(pw.this.EmpID, pw.this.EmpFname, pw.this.Department) |
| 1600 | .reduce( |
| 1601 | pw.this.EmpID, pw.this.EmpFname, pw.this.Department, cnt=pw.reducers.count() |
| 1602 | ) |
| 1603 | .filter(pw.this.cnt > 1) |
| 1604 | ) |
| 1605 | assert_table_equality_wo_index(res_table, expected_table) |
| 1606 | |
| 1607 | |
| 1608 | @pytest.mark.xfail(reason="NotImplementedError: distinct: DISTINCT not supported.") |