PracHub
QuestionsPremiumCoachesLearningGuidesInterview Prep

Quick Overview

This question evaluates proficiency in relational data manipulation, including multi-table joins, grouping and aggregation to identify departments with multiple employees and top earners, tie-aware per-group selection, and filtering joined contact data.

  • easy
  • Microsoft
  • Data Manipulation (SQL/Python)
  • Data Scientist

Query departments and top earners

Company: Microsoft

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: easy

Interview Round: Technical Screen

You are given three tables: 1. `company` - `employee_id` INT - `first_name` VARCHAR - `last_name` VARCHAR - `department` VARCHAR - `salary` DECIMAL 2. `employee_contact` - `employee_id` INT - `email` VARCHAR - `phone_number` VARCHAR - `years_at_company` INT 3. `employee_social` - `employee_id` INT - `linkedin` VARCHAR `employee_id` is the key used to join the tables. There are no date or timestamp columns in this problem, so no time-window or timezone assumptions are needed. Write SQL for the following progressive tasks: 1. Return every department that has more than one employee. - Required output: `department` 2. For each department, return the employee or employees with the highest salary. - If multiple employees tie for the highest salary in a department, return all of them. - Required output: `department, employee_id, first_name, last_name, salary` 3. From the highest-paid employee or employees in each department, return the contact details for those who have been at the company for more than 3 years. - Required output: `department, employee_id, first_name, last_name, salary, email, phone_number, years_at_company` Note: The `employee_social` table is provided as part of the schema, but it is not required for these three tasks.

Quick Answer: This question evaluates proficiency in relational data manipulation, including multi-table joins, grouping and aggregation to identify departments with multiple employees and top earners, tie-aware per-group selection, and filtering joined contact data.

Last updated: May 7, 2026

Loading coding console...

PracHub

Master your tech interviews with 8,000+ real questions from top companies.

Product

  • Questions
  • Learning Tracks
  • Interview Guides
  • Resources
  • Premium
  • For Universities
  • Student Access

Browse

  • By Company
  • By Role
  • By Category
  • Topic Hubs
  • SQL Questions
  • Compare Platforms
  • Discord Community

Support

  • support@prachub.com
  • (916) 541-4762

Legal

  • Privacy Policy
  • Terms of Service
  • About Us

© 2026 PracHub. All rights reserved.

Related Coding Questions

  • Query email logs for deliverability insights - Microsoft (Medium)
  • Find common friends from directed edges - Microsoft (Medium)
  • Compute most popular location with weights - Microsoft (Medium)
  • Count words in a document robustly - Microsoft (Medium)