| 1626 | |
| 1627 | |
| 1628 | def test_sql_interview_Q23_bis(): |
| 1629 | EmployeeInfo = T( |
| 1630 | """ |
| 1631 | EmpID|EmpFname|EmpLname|Department|Project|Address|DOB|Gender |
| 1632 | 1|Sanjay|Mehra|HR|P1|Hyderabad(HYD)|01/12/1976|M |
| 1633 | 2|Ananya|Mishra|Admin|P2|Delhi(DEL)|02/05/1968|F |
| 1634 | 3|Rohan|Diwan|Account|P3|Mumbai(BOM)|01/01/1980|M |
| 1635 | 4|Sonia|Kulkarni|HR|P1|Hyderabad(HYD)|02/05/1992|F |
| 1636 | 5|Ankit|Kapoor|Admin|P2|Delhi(DEL)|03/07/1994|M |
| 1637 | """ |
| 1638 | ) |
| 1639 | sql_query = """ |
| 1640 | Select E.EmpID, E.EmpFname, E.Department |
| 1641 | FROM EmployeeInfo E, EmployeeInfo E1 |
| 1642 | WHERE E.Department = E1.Department AND E.EmpID != E1.EmpID; |
| 1643 | """ |
| 1644 | res_table = pw.sql(sql_query, EmployeeInfo=EmployeeInfo) |
| 1645 | EmployeeInfoCopy = EmployeeInfo.copy() |
| 1646 | expected_table = ( |
| 1647 | EmployeeInfo.join( |
| 1648 | EmployeeInfoCopy, |
| 1649 | pw.left.Department == pw.right.Department, |
| 1650 | ) |
| 1651 | .filter(pw.left.EmpID != pw.right.EmpID) |
| 1652 | .select(pw.left.EmpID, pw.left.EmpFname, pw.left.Department) |
| 1653 | ) |
| 1654 | assert_table_equality_wo_index(res_table, expected_table) |
| 1655 | |
| 1656 | |
| 1657 | @pytest.mark.xfail( |