Quick Overview

This set of tasks evaluates data manipulation and analytical competencies, specifically the ability to compute order-statistics on numeric columns, perform relational joins to enrich event records, and reason about constrained selection for maximizing hires using SQL and pandas.

Write queries to compute salary and budget stats

Company: Apple

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: easy

Interview Round: Onsite

You are given the following interview tasks. Write solutions in **SQL and/or Python (pandas)** as appropriate. ## Task 1 — Second highest salary You have a table: **employees** - `employee_id` INT (PK) - `name` VARCHAR - `salary` INT Return the **second highest distinct salary**. - If there is **no** second distinct salary, return `NULL`. **Required output** - `second_highest_salary` (INT or NULL) ## Task 2 — Merge two datasets You are given two datasets that share a common key: **users** - `user_id` INT (PK) - `country` VARCHAR - `signup_date` DATE **user_events** - `user_id` INT (FK → users.user_id) - `event_time` TIMESTAMP - `event_type` VARCHAR Merge them so that each event row is enriched with user attributes. - Keep only events that have a matching `user_id` in `users` (inner join). **Required output** - `user_id`, `country`, `signup_date`, `event_time`, `event_type` ## Task 3 — “Run out the budget” (maximize hires) You have a list/table of employees with their salaries and a total hiring budget `B`. - Each employee costs exactly their `salary`. - You can hire at most once per employee. - Goal: **maximize the number of employees hired** without exceeding the budget. **Input** - `employees(employee_id, salary)` and an integer `B` **Required output** - `max_hires` (INT) Clarify any assumptions you need (e.g., what to do if `B <= 0`).

Overview: This set of tasks evaluates data manipulation and analytical competencies, specifically the ability to compute order-statistics on numeric columns, perform relational joins to enrich event records, and reason about constrained selection for maximizing hires using SQL and pandas.

Read the full Apple Data Scientist interview experience this question came from

Merge two employee datasets (deduplicate by employee_id)

You are given two employee datasets from two different sources: employees_source_a and employees_source_b. Both tables have the same columns. Create a merged result set that contains exactly one row per employee_id using these rules: 1) If an employee_id exists in both tables, keep the row with the later updated_at date. 2) If updated_at is the same in both tables (edge case), prefer the row from employees_source_b. 3) If an employee_id exists in only one table, keep that row. Return the merged employees with columns: employee_id, employee_name, dept_id, salary, updated_at, chosen_source. Sort by employee_id.

Tables

employees_source_a(employee_id INT, employee_name VARCHAR(50), dept_id INT, salary DECIMAL(12,2), updated_at DATE)

employees_source_b(employee_id INT, employee_name VARCHAR(50), dept_id INT, salary DECIMAL(12,2), updated_at DATE)

Hints

  1. UNION ALL the two sources and rank rows per employee_id.
  2. Use ROW_NUMBER() with ORDER BY updated_at DESC, then a source priority tie-breaker.

Find the second highest distinct salary (after merging)

Using the same two source tables (employees_source_a and employees_source_b), first create the merged employee dataset using the same deduplication rules as in the previous task (latest updated_at wins; tie goes to source B). From the merged dataset, return the employee(s) who have the second highest DISTINCT salary (i.e., if the highest salary is 130k, the second highest distinct might be 120k even if multiple people share 105k). Return: employee_id, employee_name, salary. Sort by employee_id.

Tables

employees_source_a(employee_id INT, employee_name VARCHAR(50), dept_id INT, salary DECIMAL(12,2), updated_at DATE)

employees_source_b(employee_id INT, employee_name VARCHAR(50), dept_id INT, salary DECIMAL(12,2), updated_at DATE)

Hints

  1. Use a CTE to build the merged dataset first.
  2. Use DENSE_RANK() to handle distinct salary ranking and ties correctly.

Find departments that are over budget (salary expense)

Using the merged employee dataset created from employees_source_a and employees_source_b (same deduplication rules: latest updated_at wins; tie goes to source B), treat each employee's salary as an expense. Given a department_budget table, compute total salary expense per department and find departments where salary_expense > annual_budget. Return: dept_id, dept_name, annual_budget, salary_expense, over_budget_amount (salary_expense - annual_budget). Sort by over_budget_amount descending, then dept_id ascending.

Tables

employees_source_a(employee_id INT, employee_name VARCHAR(50), dept_id INT, salary DECIMAL(12,2), updated_at DATE)

employees_source_b(employee_id INT, employee_name VARCHAR(50), dept_id INT, salary DECIMAL(12,2), updated_at DATE)

departments(dept_id INT, dept_name VARCHAR(50))

department_budget(dept_id INT, annual_budget DECIMAL(12,2))

Hints

  1. Build the merged employee dataset first, then aggregate salaries by dept_id.
  2. Compare the aggregate expense to department_budget and filter where expense is greater.

Loading coding console...