SQL job interview. Episode 1.

Hello and welcome to the first episode of the SQL job interview tests series!

In this odyssey we will cover real-life tasks given by recruiters for SQL-related jobs.

Task 1:

1    Вывести всех клиентов, сумма заказов которых за декабрь 2013 больше 10 000 руб.

Таблица Заказ: Номер заказа, Дата, КлиентID

Таблица ТоварыЗаказа: Номер заказа, Цена, Наименование

Таблица Клиент: КлиентID, ФИО, Дата рождения, Паспорт, Дата занесения

Okay, first thing we need to do is figure out with what tables are we working with. We have three tables with some fields. We immediately spot the key fields that will be used for table joining. These are Номер заказа and КлиентID.
Now let's decompose the question and work step-by-step. So we need to select a list of client_id with two conditions. And also we need to join an additional table. Here's how it looks like:

SELECT 
КлиентID  
FROM Таблица Заказ AS T1
INNER JOIN  Таблица ТоварыЗаказа AS T2
ON  T1.Номер заказа = T2.Номер заказа
WHERE 1=1
AND  Дата BETWEEN 01-12-2013 AND 31-12-2013
AND  Цена > 10 000

And here's how you do the taks if they want you to aggregate sums of price:

SELECT
КлиентID , SUM(Цена) as SUM_PRICE
FROM
 (
       SELECT 
       КлиентID , Цена
       FROM Таблица Заказ AS T1
       INNER JOIN  Таблица ТоварыЗаказа AS T2
       ON  T1.Номер заказа = T2.Номер заказа
       WHERE 1=1
       AND  Дата BETWEEN 01-12-2013 AND 31-12-2013
) T_AGG
WHERE1=1
GROUP BY КлиентID 
HAVING  SUM(Цена) > 10 000
 

Task 2:

      Таблица Клиент: ID, ФИО, Дата рождения, Паспорт, Дата занесения

Вывести все дубликаты (по полям ФИО, дата рождения, Паспорт) записей таблицы «Клиент», исключая для каждой группы повторяющихся записей последнюю занесенную по полю «Дата занесения».

First of all I suggest we build a table filled with dummy data, just to help us think. 

ID	ФИО	Дата рождения	Паспорт	Дата занесения
123	Smith A.	01/01/1900	Qwe123	05/06/2005
456	Johnson B.	02/02/1800	Asd456	07/08/2006

Here's how to bring about duplicate rows:           

SELECT
    ФИО, дата рождения, Паспорт, COUNT(*)
FROM
    Таблица Клиент
GROUP BY
    ФИО, дата рождения, Паспорт
HAVING 
    COUNT(*) > 1

But there's a catch. They want us to delete a row based on date value. One way to do it is to use the

OVER (PARTITION BY) trick. Basically it's like a filter function in Excel and it adds an extra column. 

So we need to modify our base script to take that request into account:

SELECT
    ФИО, дата рождения, Паспорт, COUNT(*)
FROM

   (

    SELECT ФИО, дата рождения, Паспорт, Дата занесения,

    RANK() OVER (PARTITION BY ФИО, дата рождения, Паспорт ORDER BY Дата занесения DESC) AS rank 
    FROM Таблица Клиент

   ) AS T1

GROUP BY
    ФИО, дата рождения, Паспорт
HAVING
    COUNT(*) > 1 
WHERE rank > 1

Task 3:

      Таблица Посещений: ID, Name, Date_Visit

Вывести всех клиентов, у которых есть хотя бы один перерыв между посещениями длительностью более 90 дней.

 It's calculations time! Don't need to reinvent the wheel, here the answer:

https://stackoverflow.com/questions/17303755/sql-calculating-number-of-days-between-dates-of-one-column-in-different-rows

http://sqlfiddle.com/#!6/3ce4a/1

Task 4:

Given tables:

Part 1: Select all employees with salary higher that manger's.
Firstly you gotta make a table of managers:

 

select (id, department_id, chief_id, name, salary)

from employee 

where id = chief_id --this is how you select managers' rows

then you just put the appropriate restriction in the where clause:

select id, chief_id, t1salary, t2.salary
from employee 
left join
  (
    select (id, department_id, chief_id, name, salary) 
    from employee 

    where id = chief_id --this is how you select managers' rows

  ) as t2 on t1.ID=t2.ID

where t1salary > t2.salary

 

All the other part of the task are generally straight-forward, just do them step-by-step and use SQL-fiddle to have a feel for the data. 

 

The link to the full document: https://yadi.sk/i/Z6Lo-Xr93RJuhL

https://yadi.sk/i/Z6Lo-Xr93RJuhL

https://yadi.sk/i/Z6Lo-Xr93