| 1253 | |
| 1254 | @pytest.mark.xfail(reason="NotImplementedError: date comparisons") |
| 1255 | def test_sql_interview_Q11(): |
| 1256 | EmployeeInfo = T( |
| 1257 | """ |
| 1258 | EmpID|EmpFname|EmpLname|Department|Project|Address|DOB|Gender |
| 1259 | 1|Sanjay|Mehra|HR|P1|Hyderabad(HYD)|01/12/1976|M |
| 1260 | 2|Ananya|Mishra|Admin|P2|Delhi(DEL)|02/05/1968|F |
| 1261 | 3|Rohan|Diwan|Account|P3|Mumbai(BOM)|01/01/1980|M |
| 1262 | 4|Sonia|Kulkarni|HR|P1|Hyderabad(HYD)|02/05/1992|F |
| 1263 | 5|Ankit|Kapoor|Admin|P2|Delhi(DEL)|03/07/1994|M |
| 1264 | """ |
| 1265 | ) |
| 1266 | sql_query = "SELECT COUNT(*), Gender FROM EmployeeInfo WHERE DOB BETWEEN '02/05/1970 ' AND '31/12/1975' GROUP BY Gender;" # noqa E501 |
| 1267 | res_table = pw.sql(sql_query, EmployeeInfo=EmployeeInfo) |
| 1268 | expected_table = EmployeeInfo.filter(pw.this.DOB >= "02/05/1970") |
| 1269 | expected_table = expected_table.filter(pw.this.DOB <= "31/12/1975") |
| 1270 | expected_table = expected_table.groupby(pw.this.Gender).reduce( |
| 1271 | _col_0=pw.reducers.count(), Gender=pw.this.Gender |
| 1272 | ) |
| 1273 | assert_table_equality(res_table, expected_table) |
| 1274 | |
| 1275 | |
| 1276 | @pytest.mark.xfail(reason="NotImplementedError: ORDER BY") |