Solve SQL and Python OA tasks
Problem Description
This assessment consists of two independent data manipulation tasks, one in SQL and one in Python.
Task A: SQL Query
You are provided with two tables:
customer:customer_id(INT)customer_name(VARCHAR)purchase_id(VARCHAR)
purchase:id(VARCHAR)purchase_price(DECIMAL(10,2))purchase_date(DATE)
The customer.purchase_id column links to purchase.id.
Objective:
Write a SQL query to identify the customer(s) with the highest single purchase_price within a specific historical 10-year window. This window starts on the absolute minimum purchase_date found in the purchase table and extends for exactly 10 years (exclusive of the end date). Assume purchase_date is stored as a DATE type in UTC.
If multiple customers share the highest qualifying purchase_price, all such customers should be returned.
Required Output Columns:
customer_namehighest_purchase_price
Task B: Python Function (Pandas)
You will be given a pandas DataFrame named students with the following schema:
student_id(INT)math_score(FLOAT, nullable)english_score(FLOAT, nullable)physics_score(FLOAT, nullable)
Objective:
Implement a Python function that processes the students DataFrame according to the following steps and returns the top 5 students.
Processing Steps:
- Filtering: Remove any student who has missing scores (null values) in two or more subjects.
- Imputation: For the students remaining after step 1, fill any still-missing subject scores with the median score for that specific subject. These medians must be computed only from the non-null scores of the students who remained after step 1.
- Sorting: Sort the processed table. The primary sort key is
math_scorein descending order, followed byphysics_scorein descending order. If further tie-breaking is necessary, sort bystudent_idin ascending order. - Selection: Return the top 5 rows from the sorted table. If fewer than 5 students remain after all processing, return all remaining rows.
The function should return the full cleaned rows for the selected students.
AI interview prep
Core dilemma
Ensuring absolute precision and correctness in multi-step data transformations, handling edge cases like nulls, ties, and dynamic windowing, while maintaining readability and reasonable performance for data processing tasks.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Solve SQL and Python OA tasks
Problem Description
This assessment consists of two independent data manipulation tasks, one in SQL and one in Python.
Task A: SQL Query
You are provided with two tables:
customer:customer_id(INT)customer_name(VARCHAR)purchase_id(VARCHAR)
purchase:id(VARCHAR)purchase_price(DECIMAL(10,2))purchase_date(DATE)
The customer.purchase_id column links to purchase.id.
Objective:
Write a SQL query to identify the customer(s) with the highest single purchase_price within a specific historical 10-year window. This window starts on the absolute minimum purchase_date found in the purchase table and extends for exactly 10 years (exclusive of the end date). Assume purchase_date is stored as a DATE type in UTC.
If multiple customers share the highest qualifying purchase_price, all such customers should be returned.
Required Output Columns:
customer_namehighest_purchase_price
Task B: Python Function (Pandas)
You will be given a pandas DataFrame named students with the following schema:
student_id(INT)math_score(FLOAT, nullable)english_score(FLOAT, nullable)physics_score(FLOAT, nullable)
Objective:
Implement a Python function that processes the students DataFrame according to the following steps and returns the top 5 students.
Processing Steps:
- Filtering: Remove any student who has missing scores (null values) in two or more subjects.
- Imputation: For the students remaining after step 1, fill any still-missing subject scores with the median score for that specific subject. These medians must be computed only from the non-null scores of the students who remained after step 1.
- Sorting: Sort the processed table. The primary sort key is
math_scorein descending order, followed byphysics_scorein descending order. If further tie-breaking is necessary, sort bystudent_idin ascending order. - Selection: Return the top 5 rows from the sorted table. If fewer than 5 students remain after all processing, return all remaining rows.
The function should return the full cleaned rows for the selected students.