SQL questions

Department Top 3 Salaries

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

Employee table

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Department table

The Department table holds all departments of the company.

Id	Name
1	IT
2	Sales

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).

Sample output:

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000

In the IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

Answer:

SELECT d.Name Department, e1.Name Employee, e1.Salary
FROM Employee e1
INNER JOIN Department d on d.Id = e1.DepartmentId
WHERE
-- Number of Salaries greater than itself is less than 3
3 > (SELCT COUNT(DISTINCT e2.Salary)
     FROM Employee e2
     WHERE e2.Salary > e1.Salary
     AND e2.DepartmentId = e1.DepartmentId)

Explanation:

It is important to note that another way of choosing top 3 salaries is choosing salaries that have less than 3 salaries that are greater than itself. This is executed by the following:

SELECT e1.Name Employee, e1.Salary
FROM Employee e1
WHERE
3 >
  (SELECT COUNT(DISTINCT e2.Salary)
  FROM Employee e2
  WHERE e2.Salary > e1.salary)

Back to top

---

Exchange Seats

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

The column id is continuous increment.

Mary wants to change seats for the adjacent students.

Write a SQL query to output the result for Mary.

Note: If the number of students is odd, there is no need to change the last one's seat.

Seat table

Id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	James

Output:

Id	Student
1	Doris
2	Abbot
3	Green
4	Emerson
5	James

Answer

SELECT
  (CASE
      -- considering when count is odd
      WHEN mod(id,2) != 0 AND id != counts THEN id + 1
      WHEN mod(id,2) != 0 AND id = counts THEN id
      -- considering when count is even
      ELSE id - 1
    END) AS id,
  student
FROM seat, (SELECT COUNT(*) AS counts FROM seat) AS seat_counts
ORDER BY id ASC
;

Explanation

1. First we want to count number of students to see whether the count is odd or even.

(SELECT COUNT(*) AS counts FROM seat) AS seat_counts

2. Then we want to divide into different cases:

(CASE
      -- case when id is odd and total number is not equal
      WHEN mod(id,2) != 0 AND id != counts THEN id + 1
      -- case when id is odd and total number is equal
      WHEN mod(id,2) != 0 AND id = counts THEN id
      -- case when id is even
      ELSE id - 1
END)

Back to top

---

Department Highest Salary

The Employee table holds all employees. Every employee has an id, salary and there is also a column for the department id.

Employee table:

Id	Name	Salary	DepartmentId
1	Joe	70000	1
2	Jim	90000	1
3	Henry	80000	2
4	Sam	60000	2
5	Max	90000	1

Department table holds all departments of the company:

Id	Name
1	IT
2	Sales

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

Sample output:

Department	Employee	Salary
IT	Max	90000
IT	Jim	90000
Sales	Henry	80000

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

Answer:

SELECT d.Name Department, e.Name Employee e.Salary Salary
FROM Employee e
INNER JOIN Department d on e.DepartmentId = d.Id
WHERE (e.DepartmentId, e.Salary)
IN
  (SELECT DepartmentId, MAX(Salary)
  FROM Employee
  GROUP BY DepartmentId)
;

Explanation:

1. Note we want to first create a table that lists out the maximum salaries for each of the department.

(SELECT DepartmentId, MAX(Salary)
FROM Employee
GROUP BY DepartmentId)

2. Afterwards, we make a query that outputs all employees that are in the table that we created in step 1.

WHERE (e.DepartmentId, e.Salary) IN
  (SELECT DepartmentId, MAX(Salary)
  FROM Employee
  GROUP BY DepartmentId)

Back to top

---

Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

Logs table:

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

Sample output:

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

|ConsecutiveNums|

|1|

Answer:

SELECT l1.Num as ConsecutiveNums
FROM logs l1
LEFT JOIN logs l2 on l1.Id = l2.Id -1
LEFT JOIN logs l3 on l1.Id = l3.Id -2
WHERE l1.Num = l2.Num
AND l1.Num = l3.Num

Explanation:

1. We want to align two tables along side the Logs table such that the second table next to Logs table will start from the second row of the Logs table. The following is an example:

Id	Num	Id	Num
1	1	2	1
2	1	3	1
...	...	...	...
7	2	null	null

LEFT JOIN logs l2 on l1.Id = l2.Id -1

2. Similarly, the third table will look like the following:

Id	Num	Id	Num	Id	Num
1	1	2	1	3	1
2	1	3	1	4	2
...	...	...	...	...	...
7	2	null	null	null	null

LEFT JOIN logs l3 on l1.Id = l3.Id -2

3. After we have the three tables lined up, we filter by conditioning by the following:

WHERE l1.Num = l2.Num
AND l1.Num = l3.Num

Back to top

---

Active users retention

Assume you have the below tables on user actions. Write a query to get the active user retention by month.

user_actions table:

column name	type
user_id	integer
event_id	string
timestamp	datetime

-- DATETIME - format: YYYY-MM-DD HH:MI:SS
SELECT EXTRACT(MONTH FROM timestamp) as month, SUM(DISTINCT user_id)
FROM user_actions
GROUP BY month

Back to top

---

Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

Scores table:

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

Output:

For example, given the above Scores table, your query should generate the following report (order by highest score):

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

Answer 1:

SELECT
  Score,
-- We first count the number of distinct scores that are greater than
-- or equal to itself
  (SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s.Score) Rank
FROM Scores s
-- Make sure to order scores in decreasing order
ORDER BY Score DESC

Explanation

• We want to create a column called Rank that ranks the scores in decreasing order.
• We can do this by first selecting the Score column with only DISTINCT entries.
• To turn this into the Rank column that we want, we COUNT the number of distinct Scores.
• Now our next issue is to deal with repeated entries from Score column, i.e. what if you have more than two scores?
• We can deal with this issue by adding WHERE Score >= s.Score.

Answer 2:

SELECT
  Score,
  @x := @x + (@y <> (@y := Score)) Rank
FROM
  Scores,
  (SELECT @x := 0, @y := -1) init
ORDER BY Score DESC

Explanation

• We want to create a column called Rank that indicates the rank of a score.

ORDER BY Score DESC

• We will make a table that includes two variables that start from 0 and -1 accordingly. Let the variables be x,y accordingly.

SELECT @x := 0, @y := -1)

• Then we will order Scores table in DECREASING order by score to start counting the ranks.
• As we move down the score column, we will add 1 to x and add 0 if the scores are the same.

-- rank = rank + (0 if prev == Score else 1) // set prev = Score at the same time
@x := @x + (@y <> (@y := Score))

Back to top

---

Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.

Employee table:

Id	Salary
1	100
2	200
3	300

For example, given the above table, the nth highest salary where n = 2 is 200. If there is no highest salary, output null.

Output table:

getHighestSalary(2)
200

Answer:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M int;
SET M = N - 1;
RETURN (
	SELECT DISTINCT Salary -- we want to get only a single value if there are more than one same value
	FROM Employee
	ORDER BY Salary DESC -- order by descending order to get the highest
	LIMIT M, 1 -- same as LIMIT 1 OFFSET M (Show first value after disregarding first M entries)
);
END

Explanation:

• We want to first order the Salary column in descending order and also not to forget to query only DISTINCT values.
• Intuitively, if we want to get the nth highest salary, this means that we will have to count down from the highest Salary to the nth highest salary.
• For example, if we want to get the 4th highest salary, we will skip the first entries from the Salary column and stop at the 4th entry.
• To translate this process into a query, we will make use of LIMIT A, OFFSET B. Essentially LIMIT A, OFFSET B means that we will skip the first B amount of entries and only show the next A entries.
• In our case, we will skip the first N-1 entries and how only the Nth entry (i.e. LIMIT 1, OFFSET N-1 OR LIMIT N-1, 1
• So we start by initializing the variable M as SET M = N-1; to set the number of entries to skip until the Nth highest salary.

Steps in summary:

1. Initialize SET M = N - 1;
2. Query Salary in descending order, not forgetting to display only distinct values.
3. Skip the first N-1 entries and show the Nth entry, i.e. LIMIT M,1

Back to top

---

Company Query

Given two tables, query out names of people and the names of their previous employers. Limit the list to the people currently working with the companies which were left by the most number of people. Print the name of the employee and the previous employer.

People table:

Name	Type	Description
ID	STRING	ID of the employee
NAME	STRING	Name of the employee
PREV_COMPANY_ID	STR	ID of the previous company
CUR_COMPANY_ID	STR	ID of the current company

Companies table:

Name	Type	Description
ID	STRING	ID of the company
NAME	STRING	Name of the company

Sample Input: People table

ID	NAME	CUR_COMPANY_ID	PREV_COMPANY_ID
1	Chris Michael	345	123
2	Sandra Park	567	234
3	Ashley Gibon	456	234
4	Matthew Lopez	456	345
5	Pattrck Heinz	234	345
6	Alex Arnolds	123	345
7	Helen Smith	567	456
8	Louisa Sanchez	345	456
9	Clark Henderson	123	456
10	Clara Mayon	123	456

Sample Input: Companies table

ID	NAME
123	Ann-Sullivan
234	Harmon Kardon
345	Smith-McKinsey
456	Google
567	Facebook

Sample Output:

Ashley Gibon Google
Matthew Lopez Google

Answer:

SELECT tbl2.p_name,tbl2.c_name
FROM
-- query to rank employers with the most number of employees left
	(SELECT c.name c_name, count(p.name) cnt
	FROM people p, companies c
	WHERE p.prev_company_id = c.id
	GROUP BY c.name
	ORDER BY cnt desc
	LIMIT 1) tbl1,
-- join people table with companies in terms of p.cur_company_id = c.id
	(SELECT p.name p_name, c.name c_name
	FROM people p, companies c
	WHERE p.cur_company_id = c.id) tbl2
WHERE tbl1.c_name = tbl2.c_name;

Explanation

We first divide the query into 3 different parts.

1. We first join PEOPLE and COMPANIES table by PREV_COMPANY_ID to see employees and their previous company names.
2. Then we GROUP BY the company names and COUNT the number of employees for each companies to OBTAIN THE NUMBER OF PREVIOUS EMPLOYEES FOR EACH COMPANY.
3. Make sure to ORDER BY the employee count in DESCending order.
4. We LIMIT by 1 to only show the MAXimum count.

(SELECT c.name c_name, count(p.name) cnt
FROM people p, companies c
WHERE p.prev_company_id = c.id
GROUP BY c_name
ORDER BY cnt desc
LIMIT 1) tbl1

Then, we want to match CURRENT company with the result we have from tbl1. So we query the employee name and company name joined by p.cur_company_id = c.id.

(SELECT p.name p_name, c.name c_name
FROM people p, companies c
WHERE p.cur_company_id = c.id) tbl2
;

Lastly, we will join the two tables, tbl1 and tbl2 based on tbl1.c_name = tbl2.c_name!

We can also see below that the company that had the most workers leaving was Google.

ID	NAME	CUR_COMPANY_ID	PREV_COMPANY_ID
1	Chris Michael	Smith-McKinsey	Ann-Sullivan
2	Sandra Park	Facebook	Harmon Kardon
3	Ashley Gibon	Google	Harmon Kardon
4	Matthew Lopez	Google	Smith-McKinsey
5	Pattrck Heinz	Harmon Kardon	Smith-McKinsey
6	Alex Arnolds	Ann-Sullivan	Smith-Mckinsey
7	Helen Smith	Facbook	Google
8	Louisa Sanchez	Smith-McKinsey	Google
9	Clark Henderson	Ann-Sullivan	Google
10	Clara Mayon	Ann-Sullivan	Google

Back to top

---

Earnings by country

Write a query to get the city names and earnings from each city. 'Earnings' are the sum of all the fares from the rides for a given city. Please display the output as the following: 'CITIES.Name EARNINGS' Sort the output according the earnings in ascending order and city names in ascending order.

We are given three different tables below

CITIES:

Name	Type	Description
ID	STR	Id of the city
Name	STR	Name of the city

USERS:

Name	Type	Description
ID	STR	User ID
city_id	STR	City Id
name	STR	User Name
email	STR	User email

RIDES:

Name	Type	Description
id	STR	Ride Id
user_id	STR	User ID
distance	INT	Distance traveled
fare	INT	Fare of the ride

Sample Input:CITIES

id	Name
1	San Francisco
2	Columbia

Sample Input:USERS

id	city_id	name	email
1	2	Roberto Carlos	[email protected]
2	2	Tom Hardy	[email protected]
3	1	Jordan Peters	[email protected]
4	1	Bill Gait	[email protected]
5	1	Frank Ribery	[email protected]
6	1	Morgan John	[email protected]

Sample Input: RIDES

id	user_id	distance	fare
1	1	21	200
2	3	6	55
3	2	30	230
4	1	21	300
5	2	1234	320
6	4	4352	1000
7	5	43652	300
8	6	343	355

Sample Output:

Columbia 1140
San Francisco 1710

Answer:

SELECT c.name, SUM(ur.fare) earnings
FROM cities c,
	(SELECT u.city_id,u.name, r.fare
	FROM users u, rides r
	WHERE u.id = r.user_id) ur
WHERE ur.city_id = c.id
GROUP BY c.id,c.name
ORDER BY earnings ASC, c.name ASC;

Explanation

1. First we want to join Users and Rides tables together by u.id = r.user_id to get ride informatino of the users.
2. Then we join the table from step 1 with cities table based on city_id.
3. We will group by cities.id and cities.name and SUM fares from the table from step 1.
4. Make sure to order earnings and cities.name in ascending order

Back to top

---