## Problem You have two tables: ```sql -- employees(id, name, department_id, salary, hire_date, manager_id) -- departments(id, name, location) ``` **Query 1:** For each department, return the department name, headcount, average salary (rounded to 2 decimals), and the highest-paid employee's name. ```sql SELECT d.name AS department, COUNT(e.id) AS headcount, ROUND(AVG(e.salary), 2) AS avg_salary, MAX(e.name) KEEP (DENSE_RANK FIRST ORDER BY e.salary DESC) AS top_earner FROM departments d LEFT JOIN employees e ON e.department_id = d.id GROUP BY d.name; ``` **Query 2:** Find employees who earn more than their direct manager. ```sql SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary; ``` ## Follow-ups 1. Rewrite Query 1 in standard SQL (no vendor-specific `KEEP ... DENSE_RANK`). 2. Departments with no employees should still appear -- is your join correct? 3. Add a column showing each employee's salary percentile within their department. 4. How would you index these tables to make both queries efficient at 5 million rows?

InterviewDB Experience

Employees SQL: Query an HR Database to Report Headcount, Salaries, and Departmental Summaries

Interview Experience

Problem You have two tables: Query 1: For each department, return the department name, headcount, average salary (rounded to 2 decimals), and the highest-paid employee's name. Query 2: Find employees who earn more than their direct manager. Follow-ups Rewrite Query 1 in standard SQL (no vendor-specific KEEP ... DENSE_RANK). Departments with no employees should still appear -- is your join correct? Add a column showing each employee's salary percentile within their department. How would you index…

Full Details

🔒

Unlock all Square/Block questions

Full insider details, leaked discussions, and candidate experiences.

or every company, $100/year →

About This Question

This is a candidate experience report from a square/block interview.

It covers the following topics: Data Scientist, Sql .

Topics

Data Scientist Sql

More Square/Block Interview Questions

← All Square/Block questions Square/Block Data Scientist Questions → All Sql Questions →