cswork / libraryOpen practice workspace

Coding questions · Apple

Write queries to compute salary and budget stats

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.

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).

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.

Table employees_source_a

columntypeconstraints
employee_idINTNOT NULL
employee_nameVARCHAR(50)NOT NULL
dept_idINTNOT NULL
salaryDECIMAL(12,2)NOT NULL
updated_atDATENOT NULL

Sample data:

dept_idemployee_idemployee_namesalaryupdated_at
101Alice1200002025-01-15
102Bob900002025-01-15
203Cara1100002025-01-15
304Dan1050002025-01-15

Table employees_source_b

columntypeconstraints
employee_idINTNOT NULL
employee_nameVARCHAR(50)NOT NULL
dept_idINTNOT NULL
salaryDECIMAL(12,2)NOT NULL
updated_atDATENOT NULL

Sample data:

dept_idemployee_idemployee_namesalaryupdated_at
203Cara1150002025-03-01
205Evan950002025-03-01
306Fiona1050002025-03-01
407George1300002025-03-01

Expected output

One row per employee_id after merging and deduplicating across both sources. Includes a chosen_source column indicating which source was kept.

chosen_sourcedept_idemployee_idemployee_namesalaryupdated_at
A101Alice1200002025-01-15
A102Bob900002025-01-15
B203Cara1150002025-03-01
A304Dan1050002025-01-15
B205Evan950002025-03-01
B306Fiona1050002025-03-01
B407George1300002025-03-01

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.

Table employees_source_a

columntypeconstraints
employee_idINTNOT NULL
employee_nameVARCHAR(50)NOT NULL
dept_idINTNOT NULL
salaryDECIMAL(12,2)NOT NULL
updated_atDATENOT NULL

Sample data:

dept_idemployee_idemployee_namesalaryupdated_at
101Alice1200002025-01-15
102Bob900002025-01-15
203Cara1100002025-01-15
304Dan1050002025-01-15

Table employees_source_b

columntypeconstraints
employee_idINTNOT NULL
employee_nameVARCHAR(50)NOT NULL
dept_idINTNOT NULL
salaryDECIMAL(12,2)NOT NULL
updated_atDATENOT NULL

Sample data:

dept_idemployee_idemployee_namesalaryupdated_at
203Cara1150002025-03-01
205Evan950002025-03-01
306Fiona1050002025-03-01
407George1300002025-03-01

Expected output

Employee(s) whose salary is the second highest distinct salary in the merged dataset.

employee_idemployee_namesalary
1Alice120000

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.

Table employees_source_a

columntypeconstraints
employee_idINTNOT NULL
employee_nameVARCHAR(50)NOT NULL
dept_idINTNOT NULL
salaryDECIMAL(12,2)NOT NULL
updated_atDATENOT NULL

Sample data:

dept_idemployee_idemployee_namesalaryupdated_at
101Alice1200002025-01-15
102Bob900002025-01-15
203Cara1100002025-01-15
304Dan1050002025-01-15

Table employees_source_b

columntypeconstraints
employee_idINTNOT NULL
employee_nameVARCHAR(50)NOT NULL
dept_idINTNOT NULL
salaryDECIMAL(12,2)NOT NULL
updated_atDATENOT NULL

Sample data:

dept_idemployee_idemployee_namesalaryupdated_at
203Cara1150002025-03-01
205Evan950002025-03-01
306Fiona1050002025-03-01
407George1300002025-03-01

Table departments

columntypeconstraints
dept_idINTPRIMARY KEY, NOT NULL
dept_nameVARCHAR(50)NOT NULL

Sample data:

dept_iddept_name
10Ads Ops
20Measurement
30Creative
40Sales

Table department_budget

columntypeconstraints
dept_idINTPRIMARY KEY, NOT NULL
annual_budgetDECIMAL(12,2)NOT NULL

Sample data:

annual_budgetdept_id
20000010
30000020
20500030
12000040

Expected output

Departments where total merged salary expense exceeds the annual budget, including the computed over_budget_amount.

annual_budgetdept_iddept_nameover_budget_amountsalary_expense
20000010Ads Ops10000210000
12000040Sales10000130000
20500030Creative5000210000

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.