Kata SQL

Caution: This kata does not currently have any known supported versions for SQL. It may not be completable due to dependencies on out-dated libraries/language versions.

Your manager has given you a task to create a report to measure the impact of the next company-wide salary increase. The rules for the salary increase are encapsulated in the function pctIncrease, which takes a department_id as a parameter and returns the percent increase as a value between 0 and 1.

You’ve managed to create a query that would produce the desired results, but it is currently very slow and doesn’t finish in the required time window.

Your objective in this kata is to optimize this query. The success criteria is to manage to run the query within the allowed kata solution time window (12 seconds for SQL katas).

The provided initial solution produces the correct result, but it does not run within the allowed time window.

Data model

Table: Employees
----------------
employee_id INT / PK
first_name TEXT
last_name TEXT
salary DECIMAL
department_id INT / FK
Table: Departments
------------------
department_id INT / PK
department_name TEXT

pctIncrease function signature:

pctIncrease(dept_id INT) RETURNS decimal

Solution given:

SELECT e.employee_id,
e.first_name,
e.last_name,
d.department_name,
e.salary AS old_salary,
e.salary * (1 + pctIncrease(e.department_id)) AS new_salary
FROM employees e,
departments d
WHERE e.department_id = d.department_id
ORDER BY 1;

Three ways to optimize:

  1. Use Temp Table
CREATE TEMP TABLE department(
department_id integer,
department_name text,
increase decimal);
INSERT INTO department SELECT
department_id, department_name, pctIncrease(department_id) as increase
FROM departments;
SELECT e.employee_id,
e.first_name,
e.last_name,
d.department_name,
e.salary AS old_salary,
e.salary * 1 + d.increase as new_salary
from Employees e,
department d
WHERE e.department_id = d.department_id
ORDER BY 1
  1. Use subquery
SELECT e.employee_id,
e.first_name,
e.last_name,
d.department_name,
e.salary as old_salary,
e.salary*d.pct as new_salary
from Employees e,
(SELECT department_id,
department_name,
1 + pctIncrease(department_id) pct
FROM departments) d
WHERE e.department_id = d.department_id
ORDER BY 1;
  1. Use CTE
WITH CTE AS (SELECT d.department_id,
d.department_name,
pctIncrease(d.department_id) as pct
FROM departments d
)SELECT e.employee_id,
e.first_name,
e.last_name,
e.salary as old_salary,
d.department_name,
e.salary*(1+d.pct) as new_salary
FROM Employees e
INNER JOIN CTE d
ON e.department_id = d.department_id
ORDER BY 1;

Link

Reference

--

--

--

In love with telling stories with data

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Flink Map, CoMap, RichMap and RichCoMap Functions

Part 4: The Journey…|| Why Flutter is art 🤷‍♂️

First-Bad-Version™

This week in DevOps #48 — GitOps Issue #30

Day 3–Python: Control Flow and Logical Operators

Productivity Boosters: My Top Five Developer Tools Worth The Money

Multi Threading in Python

Why Python needs the GIL

Amazon Monkey

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data

More from Medium

Embedded SQL and Dynamic SQL a

Window Functions in SQL

SQL Introduction 1

An internal hard drive