7 SQL задач, которые задают на собеседовании в телеком-компании – Часть 1
Сегодня предлагаю подразмять мозги и порешать задачки на SQL. Первые 4 из 7 заданий не должны доставить проблем:
1. Представлены таблицы C и D:
select * from C| id | val |
|---|---|
| 1 | A |
| null | B |
| 1 | C |
| 2 | D |
select * from D| id | val |
|---|---|
| 1 | A |
| null | B |
| 1 | C |
Что вернет inner join этих таблиц по полю id?
INNER JOIN возвращает только те строки, у которых есть совпадение по объединяющему полю, заданному в ON или USING, в обеих таблицах.
При этом NULL игнорируются, т.к. они не являются значением и не могут быть сравнены с другими значениями.
В результате для каждой строки из таблицы C с id = 1 будет найдена и присоединена каждая строка из таблицы D с id = 1.
Результат будет следующим:
| id | C.val | D.val |
|---|---|---|
| 1 | A | A |
| 1 | A | C |
| 1 | C | A |
| 1 | C | C |
2. Представлены таблицы A и B:
select * from A| id | val |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
select * from B| id | val |
|---|---|
| 1 | A |
| 2 | B |
| 3 | B |
| 4 | A |
Будут ли отличаться результаты, возвращаемые следующими запросами?
select *from A left join B on A.id = B.id and B.val = 'A'select *from A left join B on A.id = B.idwhere B.val = 'A'LEFT JOIN возвращает все значения из левой таблицы + совпавшие строки из правой.
Если мы осуществляем дополнительную фильтрацию в конструкции ON, то мы фильтруем строки, которые будут выбраны из таблицы B, а если в WHERE — в результирующей таблице.
Это обусловлено логическим планом выполнения запроса:
FROM → JOIN → WHERE.
Соответственно, результаты будут отличаться. А именно:
Первый запрос вернет все строки из таблицы A и строки из таблицы B, которые удовлетворяют условию (a.id = b.id и b.val = 'A'). Для неподошедших значений из таблицы B в результирующей таблице будут проставлены NULL.
Итого, будет возвращено 3 строки:
| A.id | A.val | B.id | B.val |
|---|---|---|---|
| 1 | A | 1 | A |
| 2 | B | Null | Null |
| 3 | C | Null | Null |
Для второго запроса будут выведены только те строки, которые подходят под условия:
a.id = b.idb.val = 'A'
В итоге будет возвращена 1 строка:
| A.id | A.val | B.id | B.val |
|---|---|---|---|
| 1 | A | 1 | A |
3. Представлены таблицы product — информация о том, к какой категории принадлежит продукт в указанном интервале дат:
select * from product| PROD_NM | PROD_CAT | EFF_DT | EXP_DT |
|---|---|---|---|
| Продукт1 | Категория1 | 2018-01-01 00:00:00 | 2018-01-11 00:00:00 |
| Продукт1 | Категория2 | 2018-01-11 00:00:00 | 2018-01-31 00:00:00 |
| Продукт2 | Категория3 | 2018-01-01 00:00:00 | 2018-01-31 00:00:00 |
И sale — факты продаж продукта с указанием даты продажи, цены за единицу товара и количества купленных единиц:
select * from sale| SALE_DT | PROD_NM | PRICE | CNT |
|---|---|---|---|
| 2018-01-01 00:00:00 | Продукт1 | 10 | 5 |
| 2018-01-05 00:00:00 | Продукт1 | 15 | 2 |
| 2018-01-10 00:00:00 | Продукт2 | 0,9 | 300 |
| 2018-01-15 00:00:00 | Продукт1 | 12 | 7 |
Необходимо написать запрос, возвращающий общую выручку по каждой категории:
| PROD_CAT | TOTAL_AMT |
|---|---|
| Категория1 | 80 |
| Категория2 | 84 |
| Категория3 | 270 |
Для решения задачи необходимо объединить таблицы product и sale с учетом временных рамок действия категории для каждого продукта, и затем вычислить общую выручку для каждой категории.
Для того, чтобы вывести категории с нулевой выручкой, необходимо использовать LEFT JOIN и COALESCE. Это позволит включить в результат категории, для которых нет продаж, и, логично, выручка будет равна нулю.
SELECT p.PROD_CAT, COALESCE(SUM(s.PRICE * s.CNT), 0) AS TOTAL_AMTFROM product pLEFT JOIN sale s ON s.PROD_NM = p.PROD_NM AND s.SALE_DT BETWEEN p.EFF_DT AND p.EXP_DTGROUP BY p.PROD_CAT4. Имеется таблица с цветами:
| color |
|---|
| Красный |
| Зеленый |
| Синий |
| Желтый |
| Фиолетовый |
| Пурпурный |
| Белый |
Необходимо вывести уникальные пары цветов, например:
- Красный-зеленый
- Зеленый-красный — не верно
- Красный-синий
- Синий-красный — не верно
Эта задача решается при помощи SELF JOIN для создания двух столбцов с цветами.
Также необходимо добавить условие в ON соединения: c1.color < c2.color.
Благодаря нему, каждая пара будет уникальной и не будет повторяться в обратном порядке.
Это работает за счет того, что SQL сравнивает строки лексикографически. Так, например, слово “зеленый” будет считаться больше чем “красный”. За счет этого и будет гарантированно выведено одно сочетание для каждой из пар.
SELECT c1.color AS color1, c2.color AS color2FROM color c1INNER JOIN color c2 ON c1.color < c2.colorВ следующий раз мы разберем оставшиеся 3 из 7 задач. Они уже будут посложнее.
Готовишься к собеседованию на аналитика?
Посмотри базу реальных тестовых заданий и разборы кейсов.