| 1552 | |
| 1553 | |
| 1554 | def test_sql_interview_Q22(): |
| 1555 | EmployeeInfo = T( |
| 1556 | """ |
| 1557 | EmpID|EmpFname|EmpLname|Department|Project|Address|DOB|Gender |
| 1558 | 1|Sanjay|Mehra|HR|P1|Hyderabad(HYD)|01/12/1976|M |
| 1559 | 2|Ananya|Mishra|Admin|P2|Delhi(DEL)|02/05/1968|F |
| 1560 | 3|Rohan|Diwan|Account|P3|Mumbai(BOM)|01/01/1980|M |
| 1561 | 4|Sonia|Kulkarni|HR|P1|Hyderabad(HYD)|02/05/1992|F |
| 1562 | 5|Ankit|Kapoor|Admin|P2|Delhi(DEL)|03/07/1994|M |
| 1563 | """ |
| 1564 | ) |
| 1565 | sql_query = """ |
| 1566 | SELECT EmpID, EmpFname, Department, COUNT(*) as cnt |
| 1567 | FROM EmployeeInfo GROUP BY EmpID, EmpFname, Department |
| 1568 | HAVING COUNT(*) > 1; |
| 1569 | """ |
| 1570 | res_table = pw.sql(sql_query, EmployeeInfo=EmployeeInfo) |
| 1571 | expected_table = ( |
| 1572 | EmployeeInfo.groupby(pw.this.EmpID, pw.this.EmpFname, pw.this.Department) |
| 1573 | .reduce( |
| 1574 | pw.this.EmpID, pw.this.EmpFname, pw.this.Department, cnt=pw.reducers.count() |
| 1575 | ) |
| 1576 | .filter(pw.this.cnt > 1) |
| 1577 | ) |
| 1578 | assert_table_equality_wo_index(res_table, expected_table) |
| 1579 | |
| 1580 | |
| 1581 | def test_sql_interview_Q22_bis(): |