SQL задачи

Как найти дубликат столбца email в таблице users?

Один из самых распространенных и эффективных методов — это использование SQL-запроса с функцией GROUP BY и условием HAVING.

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

• GROUP BY email: Группирует записи в таблице users по значению в колонке email.

• COUNT(*): Подсчитывает количество записей в каждой группе.

• HAVING COUNT(*) > 1: Оставляет только те группы, где количество записей больше одного, то есть, те, где есть дубликаты.

Как найти и удалить дубликат столбца email в таблице users?

Сначала нужно определить, какие записи содержат дублирующиеся email. Для этого можно использовать следующий запрос:

SELECT email, COUNT(*) 
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Потом удалить дубликаты. Для удаления дубликатов можно использовать CTE (Common Table Expressions) и оператор DELETE. Например:

WITH duplicates AS (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
        FROM users
    ) t
    WHERE t.row_num > 1
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates);

Этот запрос:

• Внутренний запрос ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) присваивает каждой записи порядковый номер внутри группы одинаковых email.

• Запрос WHERE row_num > 1 выбирает все записи, начиная со второй в каждой группе.

• Внешний запрос удаляет все записи, идентификаторы которых попали в список дубликатов.

У вас есть таблица с товарами

product_id	quantity
1	10
2	5
1	15
2	20

Мы хотим узнать общее количество проданных единиц для каждого продукта. Напишите запрос и объясните что вы получите

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

• Группировка: GROUP BY product_id берет все строки, где product_id одинаков, и собирает их в одну группу. В данном случае, все строки с product_id = 1 будут в одной группе, а все строки с product_id = 2 будут в другой группе.

• Агрегирование: Затем SUM(quantity) вычисляет сумму quantity для каждой группы. Для product_id = 1 сумма будет 10 + 15 = 25, а для product_id = 2 — 5 + 20 = 25.

Результат запроса:

product_id	total_quantity
1	25
2	25

GROUP BY позволяет объединить строки с одинаковыми значениями в один набор и затем применять агрегатные функции (такие как сумма, среднее значение) к этим группам. Это полезно для получения обобщенной информации из данных.

Описание задачи: У вас есть таблица sales, содержащая записи о продажах. Вам нужно найти общую сумму продаж для каждого продукта.

Таблица sales:

product_id	sale_date	amount
1	2024-01-01	100
2	2024-01-02	200
1	2024-01-03	150
3	2024-01-04	300
2	2024-01-05	250

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Результат:

product_id	total_sales
1	250
2	450
3	300

Объяснение: GROUP BY product_id группирует записи по product_id, то есть собирает все продажи для каждого продукта вместе. Функция SUM(amount) вычисляет сумму продаж для каждой группы. Результат показывает общую сумму продаж по каждому продукту.

Описание задачи: Найдите все продукты, у которых общая сумма продаж превышает 400.

Таблица sales:

product_id	sale_date	amount
1	2024-01-01	100
2	2024-01-02	200
1	2024-01-03	150
3	2024-01-04	300
2	2024-01-05	250

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id
HAVING SUM(amount) > 400;

Результат:

product_id	total_sales
2	450
3	300

Объяснение: GROUP BY product_id группирует записи по продуктам. HAVING SUM(amount) > 400 фильтрует группы, оставляя только те, у которых общая сумма продаж больше 400. В данном случае продукт с product_id = 2 и product_id = 3 удовлетворяют этому условию.

Описание задачи: Найдите среднюю зарплату по каждому отделу.

Таблица employees:

employee_id	department	salary
1	Sales	5000
2	HR	4500
3	Sales	6000
4	HR	4000
5	IT	5500

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Результат:

department	average_salary
Sales	5500
HR	4250
IT	5500

Объяснение: GROUP BY department группирует сотрудников по отделам. Функция AVG(salary) вычисляет среднюю зарплату для каждой группы. Результат показывает среднюю зарплату в каждом отделе.

Описание задачи: Найдите все отделы, в которых минимальная зарплата сотрудников ниже 3000.

Таблица employees:

employee_id	department	salary
1	Sales	5000
2	HR	4500
3	Sales	2500
4	HR	4000
5	IT	5500

SELECT department, MIN(salary) AS min_salary
FROM employees
GROUP BY department
HAVING MIN(salary) < 3000;

Результат:

department	min_salary
Sales	2500

Объяснение: GROUP BY department группирует сотрудников по отделам. HAVING MIN(salary) < 3000 фильтрует группы, оставляя только те, где минимальная зарплата ниже 3000. В данном случае только отдел Sales удовлетворяет этому условию.

Описание задачи: Найдите количество заказов для каждого клиента.

Таблица orders:

order_id	customer_id	order_date
1	101	2024-01-01
2	102	2024-01-02
3	101	2024-01-03
4	103	2024-01-04
5	102	2024-01-05

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id;

Результат:

customer_id	order_count
101	2
102	2
103	1

Объяснение: GROUP BY customer_id группирует заказы по клиентам. COUNT(order_id) подсчитывает количество заказов для каждого клиента. Результат показывает количество заказов для каждого клиента.

Описание задачи: Найдите продукты с наибольшим количеством продаж и отсортируйте их по убыванию.

Таблица sales:

product_id	sale_date	quantity
1	2024-01-01	50
2	2024-01-02	20
1	2024-01-03	30
3	2024-01-04	40
2	2024-01-05	10

SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id
ORDER BY total_quantity DESC;

Результат:

product_id	total_quantity
1	80
3	40
2	30

Объяснение: GROUP BY product_id группирует продажи по продуктам. SUM(quantity) вычисляет общее количество продаж для каждого продукта. ORDER BY total_quantity DESC сортирует результаты по убыванию общего количества продаж, чтобы сначала отображались продукты с наибольшими продажами.

Описание задачи: Найдите среднюю зарплату в отделах, где есть работники с зарплатой выше 7000.

Таблица employees:

employee_id	department	salary
1	Sales	8000
2	HR	4500
3	Sales	6000
4	HR	4000
5	IT	7500

SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE salary > 7000
GROUP BY department;

Результат:

department	average_salary
Sales	8000
IT	7500

Объяснение: WHERE salary > 7000 фильтрует сотрудников с зарплатой выше 7000. GROUP BY department группирует оставшиеся записи по отделам. AVG(salary) вычисляет среднюю зарплату для каждой группы. Результат показывает среднюю зарплату в отделах, где есть сотрудники с высокой зарплатой.

Описание задачи: Используйте таблицу orders, чтобы найти количество заказов на каждого клиента за последние 30 дней.

Таблица orders:

order_id	customer_id	order_date
1	101	2024-07-01
2	102	2024-07-10
3	101	2024-07-15
4	103	2024-07-20
5	102	2024-07-25

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;

Результат:

customer_id	order_count
101	2
102	2
103	1

Объяснение: WHERE order_date >= CURRENT_DATE - INTERVAL '30 days' фильтрует заказы, сделанные за последние 30 дней. GROUP BY customer_id группирует заказы по клиентам. COUNT(order_id) подсчитывает количество заказов для каждого клиента за указанный период. Результат показывает количество заказов на каждого клиента за последние 30 дней.

Описание задачи: У вас есть таблица employees. Найдите ранг каждого сотрудника по зарплате внутри его отдела.

Таблица employees:

employee_id	department	salary
1	Sales	5000
2	HR	4500
3	Sales	6000
4	HR	4000
5	IT	5500

SELECT employee_id, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Результат:

employee_id	department	salary	salary_rank
3	Sales	6000	1
1	Sales	5000	2
2	HR	4500	1
4	HR	4000	2
5	IT	5500	1

Объяснение: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) присваивает уникальный ранг каждому сотруднику в пределах их отдела, основываясь на зарплате в порядке убывания. Результат показывает, как сотрудники ранжируются по зарплате внутри каждого отдела.

Описание: Найдите кумулятивную сумму продаж по каждому продукту по мере продвижения во времени.

Таблица sales:

sale_date	product_id	amount
2024-01-01	1	100
2024-01-02	1	150
2024-01-03	2	200
2024-01-04	1	50
2024-01-05	2	100

SELECT sale_date, product_id, amount,
       SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_sum
FROM sales;

sale_date	product_id	amount	cumulative_sum
2024-01-01	1	100	100
2024-01-02	1	150	250
2024-01-04	1	50	300
2024-01-03	2	200	200
2024-01-05	2	100	300

Объяснение: Функция SUM() вычисляет кумулятивную сумму amount для каждого product_id по мере продвижения во времени (sale_date). Сумма увеличивается по мере добавления новых значений.

Описание: Выведите уникальный номер каждой строке в таблице, упорядоченной по дате продажи.

Таблица sales:

sale_date	product_id	amount
2024-01-01	1	100
2024-01-15	1	150
2024-01-25	2	200
2024-02-01	2	50

SELECT sale_date, product_id, amount,
       ROW_NUMBER() OVER (ORDER BY sale_date) AS row_number
FROM sales;

Результат:

sale_date	product_id	amount	row_number
2024-01-01	1	100	1
2024-01-15	1	150	2
2024-01-25	2	200	3
2024-02-01	2	50	4

Объяснение: Функция ROW_NUMBER() назначает уникальный номер каждой строке, упорядоченной по sale_date.

Описание: Назначьте уникальный ранг каждому сотруднику в зависимости от его зарплаты в каждой компании.

Таблица employees:

employee_id	company_id	salary
1	1	5000
2	1	6000
3	1	7000
4	2	4500
5	2	5500

SELECT employee_id, company_id, salary,
       ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY salary DESC) AS rank
FROM employees;

Результат:

employee_id	company_id	salary	rank
3	1	7000	1
2	1	6000	2
1	1	5000	3
5	2	5500	1
4	2	4500	2

Объяснение: Функция ROW_NUMBER() нумерует сотрудников внутри каждой компании (company_id), упорядоченных по salary в убывающем порядке.

Описание: Найдите номер заказа для каждого клиента, упорядоченный по дате заказа.

Таблица orders:

order_date	customer_id	order_id
2024-01-01	1	101
2024-01-05	1	102
2024-01-10	2	201
2024-01-15	2	202

SELECT order_date, customer_id, order_id,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_number
FROM orders;

Результат:

order_date	customer_id	order_id	order_number
2024-01-01	1	101	1
2024-01-05	1	102	2
2024-01-10	2	201	1
2024-01-15	2	202	2

Объяснение: ROW_NUMBER() нумерует заказы каждого клиента в порядке возрастания order_date.

Описание: Назначьте уникальный номер каждой транзакции клиента, упорядоченный по дате транзакции.

Таблица transactions:

transaction_date	client_id	transaction_id
2024-01-01	1	1001
2024-01-02	1	1002
2024-01-03	2	2001
2024-01-04	2	2002

SELECT transaction_date, client_id, transaction_id,
       ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY transaction_date) AS transaction_number
FROM transactions;

Результат:

transaction_date	client_id	transaction_id	transaction_number
2024-01-01	1	1001	1
2024-01-02	1	1002	2
2024-01-03	2	2001	1
2024-01-04	2	2002	2

Объяснение: ROW_NUMBER() нумерует транзакции внутри каждой группы клиентов (client_id) по дате транзакции в порядке возрастания.

Описание: Ранжируйте студентов по их оценкам в каждом курсе.

Таблица student_grades:

student_id	course_id	grade
1	1	90
2	1	85
3	1	88
4	2	92
5	2	91

SELECT student_id, course_id, grade,
       ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY grade DESC) AS grade_rank
FROM student_grades;

Результат:

student_id	course_id	grade	grade_rank
1	1	90	1
3	1	88	2
2	1	85	3
4	2	92	1
5	2	91	2

Объяснение: ROW_NUMBER() ранжирует студентов по их оценкам в каждом курсе (course_id), начиная с самой высокой оценки.

Описание: Ранжируйте сотрудников по их зарплате внутри каждого отдела, не пропуская ранги для сотрудников с одинаковыми зарплатами.

Таблица employees:

employee_id	department_id	salary
1	1	60000
2	1	60000
3	1	50000
4	2	70000
5	2	65000
6	2	65000

SELECT employee_id, department_id, salary,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

Результат:

employee_id	department_id	salary	salary_rank
1	1	60000	1
2	1	60000	1
3	1	50000	2
4	2	70000	1
5	2	65000	2
6	2	65000	2

Объяснение: DENSE_RANK() назначает одинаковый ранг сотрудникам с одинаковой зарплатой в пределах одного отдела. Ранги не пропускаются, даже если несколько сотрудников имеют одинаковую зарплату.

Описание: Ранжируйте заказы по их сумме внутри каждого месяца, не пропуская ранги при одинаковых суммах.

Таблица orders:

order_id	order_date	amount
1	2024-01-05	500
2	2024-01-10	300
3	2024-01-15	500
4	2024-02-05	700
5	2024-02-10	600
6	2024-02-15	600

SELECT order_id, order_date, amount,
       DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('month', order_date) ORDER BY amount DESC) AS amount_rank
FROM orders;

Результат:

order_id	order_date	amount	amount_rank
1	2024-01-05	500	1
3	2024-01-15	500	1
2	2024-01-10	300	2
4	2024-02-05	700	1
5	2024-02-10	600	2
6	2024-02-15	600	2

Объяснение: DENSE_RANK() ранжирует заказы по сумме в пределах каждого месяца. Суммы, равные друг другу, получают одинаковый ранг, и следующие ранги не пропускаются.

Описание: Ранжируйте продукты по количеству продаж в каждом квартале, не пропуская ранги при одинаковых количествах.

Таблица sales:

product_id	sale_date	quantity
1	2024-01-15	100
2	2024-01-20	150
1	2024-02-10	100
2	2024-02-15	150
3	2024-03-05	200

SELECT product_id, sale_date, quantity,
       DENSE_RANK() OVER (PARTITION BY DATE_TRUNC('quarter', sale_date) ORDER BY quantity DESC) AS sales_rank
FROM sales;

Результат:

product_id	sale_date	quantity	sales_rank
1	2024-01-15	100	2
2	2024-01-20	150	1
1	2024-02-10	100	2
2	2024-02-15	150	1
3	2024-03-05	200	1

Объяснение: DENSE_RANK() используется для ранжирования продуктов по количеству продаж в пределах каждого квартала. Продукты с одинаковым количеством продаж получают одинаковый ранг.

Описание: Ранжируйте сотрудников по времени отклика на запросы, не пропуская ранги при одинаковых значениях времени отклика.

Таблица responses:

employee_id	response_time
1	15
2	10
3	15
4	20
5	10

SELECT employee_id, response_time,
       DENSE_RANK() OVER (ORDER BY response_time) AS response_rank
FROM responses;

Результат:

employee_id	response_time	response_rank
2	10	1
5	10	1
1	15	2
3	15	2
4	20	3

Объяснение: DENSE_RANK() присваивает ранги сотрудникам на основе их времени отклика, где наименьшее время получает ранг 1, и одинаковые времена отклика получают одинаковый ранг.

Описание: Ранжируйте продавцов по количеству сделанных продаж в каждый месяц, не пропуская ранги при одинаковом числе продаж.

Таблица sales_reps:

sales_rep_id	month	sales_count
1	2024-01-01	30
2	2024-01-01	45
3	2024-01-01	30
4	2024-02-01	50
5	2024-02-01	50
6	2024-02-01	25

SELECT sales_rep_id, month, sales_count,
       DENSE_RANK() OVER (PARTITION BY month ORDER BY sales_count DESC) AS sales_rank
FROM sales_reps;

Результат:

sales_rep_id	month	sales_count	sales_rank
2	2024-01-01	45	1
1	2024-01-01	30	2
3	2024-01-01	30	2
4	2024-02-01	50	1
5	2024-02-01	50	1
6	2024-02-01	25	2

Объяснение: DENSE_RANK() присваивает ранги продавцам по количеству сделанных продаж в каждом месяце. Продавцы с одинаковым числом продаж получают одинаковый ранг, и следующий ранг идет без пропусков.

Описание: Ранжируйте фильмы по их рейтингам в каждом жанре, не пропуская ранги при одинаковых рейтингах.

Таблица movies:

movie_id	genre	rating
1	Action	8.5
2	Action	9.0
3	Action	8.5
4	Comedy	7.0
5	Comedy	8.0
6	Comedy	8.0

SELECT movie_id, genre, rating,
       DENSE_RANK() OVER (PARTITION BY genre ORDER BY rating DESC) AS rating_rank
FROM movies;

Результат:

movie_id	genre	rating	rating_rank
2	Action	9.0	1
1	Action	8.5	2
3	Action	8.5	2
5	Comedy	8.0	1
6	Comedy	8.0	1
4	Comedy	7.0	2

Объяснение: DENSE_RANK() назначает ранги фильмам в каждом жанре по их рейтингам, при этом одинаковые рейтинги получают одинаковый ранг.

Описание: Ранжируйте сотрудников по количеству проектов, в которых они участвуют, не пропуская ранги при одинаковом количестве проектов.

Таблица employee_projects:

employee_id	project_id
1	101
1	102
2	101
2	103
3	101
3	102
3	103

SELECT employee_id, COUNT(project_id) AS project_count,
       DENSE_RANK() OVER (ORDER BY COUNT(project_id) DESC) AS project_rank
FROM employee_projects
GROUP BY employee_id;

Результат:

employee_id	project_count	project_rank
3	3	1
1	2	2
2	2	2

Объяснение: DENSE_RANK() используется для ранжирования сотрудников по количеству проектов. Сотрудники с одинаковым количеством проектов получают одинаковый ранг.

Описание: Ранжируйте клиентов по общей сумме их покупок в каждой категории, не пропуская ранги при одинаковых суммах.

Таблица purchases:

client_id	category_id	purchase_amount
1	A	100
2	A	200
3	A	100
4	B	300
5	B	250
6	B	250

SELECT client_id, category_id, SUM(purchase_amount) AS total_amount,
       DENSE_RANK() OVER (PARTITION BY category_id ORDER BY SUM(purchase_amount) DESC) AS purchase_rank
FROM purchases
GROUP BY client_id, category_id;

Результат:

client_id	category_id	total_amount	purchase_rank
2	A	200	1
1	A	100	2
3	A	100	2
4	B	300	1
5	B	250	2
6	B	250	2

Объяснение: DENSE_RANK() ранжирует клиентов по общей сумме покупок в каждой категории, где одинаковые суммы покупок получают одинаковый ранг.

Описание: Разделите сотрудников на 4 группы (квартали) по их зарплате.

Таблица employees:

employee_id	salary
1	30000
2	50000
3	40000
4	70000
5	60000
6	35000
7	80000
8	45000
9	55000

SELECT employee_id, salary,
       NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

Результат:

employee_id	salary	salary_quartile
1	30000	1
6	35000	1
3	40000	2
8	45000	2
9	55000	3
2	50000	3
5	60000	4
4	70000	4
7	80000	4

Объяснение: NTILE(4) делит сотрудников на 4 группы по зарплате. Сотрудники с наименьшей зарплатой попадают в первую группу, а с наибольшей — в последнюю.

Описание: Разделите студентов на 3 группы (трети) по их оценкам.

Таблица student_grades:

student_id	grade
1	85
2	92
3	88
4	75
5	90
6	82
7	95
8	78
9	87

SELECT student_id, grade,
       NTILE(3) OVER (ORDER BY grade DESC) AS grade_quartile
FROM student_grades;

Результат:

student_id	grade	grade_quartile
7	95	1
5	90	1
2	92	1
3	88	2
9	87	2
1	85	2
6	82	3
8	78	3
4	75	3

Объяснение: NTILE(3) делит студентов на 3 группы по оценкам. Студенты с высокими оценками попадают в первую группу, средние в вторую, и низкие в третью.

Описание: Разделите продукты на 5 групп по объему продаж.

Таблица products:

product_id	sales
1	2000
2	1500
3	2500
4	1000
5	3000
6	1800
7	1700
8	2200
9	1600

SELECT product_id, sales,
       NTILE(5) OVER (ORDER BY sales DESC) AS sales_quintile
FROM products;

Результат:

product_id	sales	sales_quintile
5	3000	1
3	2500	1
8	2200	1
9	1600	2
6	1800	2
7	1700	2
2	1500	3
1	2000	3
4	1000	5

Объяснение: NTILE(5) разделяет продукты на 5 групп по объему продаж. Продукты с наибольшими продажами попадают в первую группу, а с наименьшими — в последнюю.

Описание: Разделите проекты на 3 группы по их бюджету.

Таблица projects:

project_id	budget
1	50000
2	70000
3	30000
4	100000
5	60000
6	40000
7	80000
8	20000
9	90000

SELECT project_id, budget,
       NTILE(3) OVER (ORDER BY budget DESC) AS budget_quartile
FROM projects;

Результат:

project_id	budget	budget_quartile
4	100000	1
9	90000	1
7	80000	1
2	70000	2
5	60000	2
1	50000	2
6	40000	3
3	30000	3
8	20000	3

Объяснение: NTILE(3) делит проекты на 3 группы по бюджету. Проекты с наибольшим бюджетом попадают в первую группу, средние — во вторую, и с наименьшим — в третью.

Описание: Для каждого сотрудника покажите разницу между зарплатой в текущем месяце и предыдущем месяце.

Таблица salaries:

employee_id	month	salary
1	2024-01	3000
1	2024-02	3200
1	2024-03	3100
2	2024-01	2500
2	2024-02	2600
2	2024-03	2700

SELECT employee_id, month, salary,
       LAG(salary) OVER (PARTITION BY employee_id ORDER BY month) AS previous_salary,
       salary - LAG(salary) OVER (PARTITION BY employee_id ORDER BY month) AS salary_change
FROM salaries;

Результат:

employee_id	month	salary	previous_salary	salary_change
1	2024-01	3000	NULL	NULL
1	2024-02	3200	3000	200
1	2024-03	3100	3200	-100
2	2024-01	2500	NULL	NULL
2	2024-02	2600	2500	100
2	2024-03	2700	2600	100

Объяснение: LAG(salary) возвращает зарплату за предыдущий месяц для каждого сотрудника. Мы вычисляем разницу между текущей и предыдущей зарплатой, чтобы увидеть, как она изменяется.

Задача 2: Отслеживание изменения цен товаров

Описание: Для каждого товара покажите изменение цены по сравнению с предыдущим значением.

Таблица product_prices:

product_id	date	price
1	2024-01-01	100
1	2024-02-01	110
1	2024-03-01	105
2	2024-01-01	200
2	2024-02-01	210
2	2024-03-01	220

SELECT product_id, date, price,
       LAG(price) OVER (PARTITION BY product_id ORDER BY date) AS previous_price,
       price - LAG(price) OVER (PARTITION BY product_id ORDER BY date) AS price_change
FROM product_prices;

Результат:

product_id	date	price	previous_price	price_change
1	2024-01-01	100	NULL	NULL
1	2024-02-01	110	100	10
1	2024-03-01	105	110	-5
2	2024-01-01	200	NULL	NULL
2	2024-02-01	210	200	10
2	2024-03-01	220	210	10

Объяснение: LAG(price) позволяет увидеть изменение цены товара по сравнению с предыдущим значением. Мы вычисляем разницу между текущей и предыдущей ценой.

Описание: Для каждого заказа покажите предыдущий статус и дату, когда он был изменен.

Таблица order_statuses:

order_id	status	status_date
1	Shipped	2024-01-10
1	Delivered	2024-01-15
2	Pending	2024-01-12
2	Shipped	2024-01-18

SELECT order_id, status, status_date,
       LAG(status) OVER (PARTITION BY order_id ORDER BY status_date) AS previous_status,
       LAG(status_date) OVER (PARTITION BY order_id ORDER BY status_date) AS previous_status_date
FROM order_statuses;

Результат:

order_id	status	status_date	previous_status	previous_status_date
1	Shipped	2024-01-10	NULL	NULL
1	Delivered	2024-01-15	Shipped	2024-01-10
2	Pending	2024-01-12	NULL	NULL
2	Shipped	2024-01-18	Pending	2024-01-12

Объяснение: LAG(status) позволяет увидеть предыдущий статус и дату изменения статуса для каждого заказа.

Описание: Определите время ожидания между этапами для каждого проекта.

Таблица project_stages:

project_id	stage	start_date
1	Planning	2024-01-01
1	Execution	2024-02-01
1	Review	2024-03-01
2	Planning	2024-01-05
2	Execution	2024-02-10
2	Review	2024-03-15

SELECT project_id, stage, start_date,
       LAG(start_date) OVER (PARTITION BY project_id ORDER BY start_date) AS previous_start_date,
       start_date - LAG(start_date) OVER (PARTITION BY project_id ORDER BY start_date) AS wait_time
FROM project_stages;

Результат:

project_id	stage	start_date	previous_start_date	wait_time
1	Planning	2024-01-01	NULL	NULL
1	Execution	2024-02-01	2024-01-01	31 days
1	Review	2024-03-01	2024-02-01	28 days
2	Planning	2024-01-05	NULL	NULL
2	Execution	2024-02-10	2024-01-05	36 days
2	Review	2024-03-15	2024-02-10	34 days

Объяснение: LAG(start_date) позволяет узнать, когда начался предыдущий этап и вычислить время ожидания между этапами.

Описание: Вычислите изменение количества продаж по сравнению с предыдущим днем.

Таблица daily_sales:

date	sales
2024-01-01	100
2024-01-02	150
2024-01-03	120
2024-01-04	180
2024-01-05	160

SELECT date, sales,
       LAG(sales) OVER (ORDER BY date) AS previous_sales,
       sales - LAG(sales) OVER (ORDER BY date) AS sales_change
FROM daily_sales;

Результат:

date	sales	previous_sales	sales_change
2024-01-01	100	NULL	NULL
2024-01-02	150	100	50
2024-01-03	120	150	-30
2024-01-04	180	120	60

Описание: Определите разницу в количестве посещений для каждого пользователя по сравнению с предыдущим посещением.

Таблица user_visits:

user_id	visit_date	visit_count
1	2024-01-01	5
1	2024-01-05	8
1	2024-01-10	7
2	2024-01-02	4
2	2024-01-07	6
2	2024-01-15	5

SELECT user_id, visit_date, visit_count,
       LAG(visit_count) OVER (PARTITION BY user_id ORDER BY visit_date) AS previous_visit_count,
       visit_count - LAG(visit_count) OVER (PARTITION BY user_id ORDER BY visit_date) AS visit_change
FROM user_visits;

Результат:

user_id	visit_date	visit_count	previous_visit_count	visit_change
1	2024-01-01	5	NULL	NULL
1	2024-01-05	8	5	3
1	2024-01-10	7	8	-1
2	2024-01-02	4	NULL	NULL
2	2024-01-07	6	4	2
2	2024-01-15	5	6	-1

Объяснение: LAG(visit_count) позволяет увидеть количество посещений в предыдущий раз для каждого пользователя и вычислить разницу.

Описание: Для каждого региона рассчитайте разницу в объемах продаж между текущим и предыдущим месяцем.

Таблица monthly_sales:

region	month	sales
East	2024-01	1200
East	2024-02	1300
East	2024-03	1250
West	2024-01	1500
West	2024-02	1400
West	2024-03	1600

SELECT region, month, sales,
       LAG(sales) OVER (PARTITION BY region ORDER BY month) AS previous_sales,
       sales - LAG(sales) OVER (PARTITION BY region ORDER BY month) AS sales_change
FROM monthly_sales;

Результат:

region	month	sales	previous_sales	sales_change
East	2024-01	1200	NULL	NULL
East	2024-02	1300	1200	100
East	2024-03	1250	1300	-50
West	2024-01	1500	NULL	NULL
West	2024-02	1400	1500	-100
West	2024-03	1600	1400	200

Объяснение: LAG(sales) показывает продажи в предыдущем месяце для каждого региона, и мы вычисляем изменение в продажах.

Описание: Для каждого клиента покажите информацию о первом заказе.

Таблица customer_orders:

customer_id	order_date	order_amount
1	2024-01-10	150
1	2024-03-15	200
2	2024-02-01	300
2	2024-04-20	250
3	2024-01-25	120

SELECT customer_id, order_date, order_amount,
       FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_date,
       FIRST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order_amount
FROM customer_orders;

Результат:

customer_id	order_date	order_amount	first_order_date	first_order_amount
1	2024-01-10	150	2024-01-10	150
1	2024-03-15	200	2024-01-10	150
2	2024-02-01	300	2024-02-01	300
2	2024-04-20	250	2024-02-01	300
3	2024-01-25	120	2024-01-25	120

Объяснение: FIRST_VALUE(order_date) и FIRST_VALUE(order_amount) возвращают дату и сумму первого заказа для каждого клиента.

Описание: Для каждого проекта покажите первый этап и его дату начала.

Таблица project_stages:

project_id	stage	start_date
1	Planning	2024-01-01
1	Execution	2024-02-01
1	Review	2024-03-01
2	Planning	2024-02-01
2	Execution	2024-03-01
2	Review	2024-04-01

SELECT project_id, stage, start_date,
       FIRST_VALUE(stage) OVER (PARTITION BY project_id ORDER BY start_date) AS first_stage,
       FIRST_VALUE(start_date) OVER (PARTITION BY project_id ORDER BY start_date) AS first_stage_start_date
FROM project_stages;

Результат:

project_id	stage	start_date	first_stage	first_stage_start_date
1	Planning	2024-01-01	Planning	2024-01-01
1	Execution	2024-02-01	Planning	2024-01-01
1	Review	2024-03-01	Planning	2024-01-01
2	Planning	2024-02-01	Planning	2024-02-01
2	Execution	2024-03-01	Planning	2024-02-01
2	Review	2024-04-01	Planning	2024-02-01

Объяснение: FIRST_VALUE(stage) и FIRST_VALUE(start_date) возвращают первый этап проекта и дату его начала.

Описание: Определите первый доход, полученный каждым продуктом в течение года.

Таблица product_revenue:

product_id	revenue_date	revenue_amount
1	2024-01-10	500
1	2024-03-15	600
2	2024-02-01	300
2	2024-05-20	400
3	2024-01-25	200

SELECT product_id, revenue_date, revenue_amount,
       FIRST_VALUE(revenue_date) OVER (PARTITION BY product_id ORDER BY revenue_date) AS first_revenue_date,
       FIRST_VALUE(revenue_amount) OVER (PARTITION BY product_id ORDER BY revenue_date) AS first_revenue_amount
FROM product_revenue;

Результат:

product_id	revenue_date	revenue_amount	first_revenue_date	first_revenue_amount
1	2024-01-10	500	2024-01-10	500
1	2024-03-15	600	2024-01-10	500
2	2024-02-01	300	2024-02-01	300
2	2024-05-20	400	2024-02-01	300
3	2024-01-25	200	2024-01-25	200

Объяснение: FIRST_VALUE(revenue_date) и FIRST_VALUE(revenue_amount) возвращают дату и сумму первого дохода для каждого продукта.

Описание: Определите процентиль для каждой оценки студентов по предмету. Это поможет понять, как оценки распределены в классе.

Таблица student_scores:

student_id	subject	score
1	Math	85
2	Math	90
3	Math	78
4	Math	92
5	Math	88

SELECT student_id, subject, score,
       PERCENT_RANK() OVER (PARTITION BY subject ORDER BY score) AS percentile_rank
FROM student_scores;

Результат:

student_id	subject	score	percentile_rank
3	Math	78	0.00
1	Math	85	0.25
5	Math	88	0.50
2	Math	90	0.75
4	Math	92	1.00

Объяснение: PERCENT_RANK() вычисляет процентиль для каждой оценки в рамках предмета. Например, оценка 78 находится на 0-й процентиле, а оценка 92 на 100-й процентиле.

Описание: Определите процентиль для продаж сотрудников в конкретном месяце, чтобы понять их относительное положение.

Таблица employee_sales:

employee_id	sales_month	sales_amount
1	2024-01	5000
2	2024-01	6000
3	2024-01	5500
4	2024-01	7000
5	2024-01	6500

SELECT employee_id, sales_month, sales_amount,
       PERCENT_RANK() OVER (PARTITION BY sales_month ORDER BY sales_amount) AS percentile_rank
FROM employee_sales;

Результат:

employee_id	sales_month	sales_amount	percentile_rank
1	2024-01	5000	0.00
3	2024-01	5500	0.25
5	2024-01	6500	0.50
2	2024-01	6000	0.75
4	2024-01	7000	1.00

Объяснение: PERCENT_RANK() вычисляет процентиль для каждой продажи в рамках месяца. Это помогает понять, как продажи сотрудников распределены.

Описание: Определите процентиль для производительности сотрудников на основе числа выполненных задач.

Таблица employee_performance:

employee_id	tasks_completed
1	10
2	15
3	12
4	20
5	18

SELECT employee_id, tasks_completed,
       PERCENT_RANK() OVER (ORDER BY tasks_completed) AS percentile_rank
FROM employee_performance;

Результат:

employee_id	tasks_completed	percentile_rank
1	10	0.00
3	12	0.25
2	15	0.50
5	18	0.75
4	20	1.00

Объяснение: PERCENT_RANK() вычисляет процентиль для числа выполненных задач, позволяя сравнить производительность сотрудников.

Найти сотрудников, чья зарплата выше средней зарплаты в компании

Таблица employees:

employee_id	name	salary
1	Alice	50000
2	Bob	60000
3	Charlie	55000
4	David	70000
5	Eve	65000

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Объяснение: Скалярный подзапрос (SELECT AVG(salary) FROM employees) вычисляет среднюю зарплату по всей таблице, а основной запрос возвращает имена сотрудников с зарплатой выше этой средней.

Результат:

name	salary
Bob	60000
Charlie	55000
David	70000
Eve	65000

Найти сотрудников, работающих в самой большой команде

Таблица teams:

team_id	team_name
1	Development
2	Marketing
3	HR

Таблица employees:

employee_id	name	team_id
1	Alice	1
2	Bob	1
3	Charlie	2
4	David	1
5	Eve	3

SELECT name, team_id
FROM employees
WHERE team_id = (SELECT team_id
                 FROM employees
                 GROUP BY team_id
                 ORDER BY COUNT(*) DESC
                 LIMIT 1);

Объяснение: Внутренний подзапрос определяет team_id самой крупной команды (с максимальным числом сотрудников). Основной запрос возвращает сотрудников, работающих в этой команде.

Результат:

name	team_id
Alice	1
Bob	1
David	1

Описание задачи: У вас есть две таблицы: Orders и Customers. Вам нужно найти список всех заказов с именами клиентов, которые их разместили.

Таблицы:

Таблица Orders:

order_id	customer_id	order_date
1	101	2024-01-01
2	102	2024-01-02
3	103	2024-01-03
4	101	2024-01-04

Таблица Customers:

customer_id	customer_name
101	Иван Иванов
102	Мария Смирнова
103	Алексей Кузнецов

SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Результат:

order_id	customer_name	order_date
1	Иван Иванов	2024-01-01
2	Мария Смирнова	2024-01-02
3	Алексей Кузнецов	2024-01-03
4	Иван Иванов	2024-01-04

Объяснение: INNER JOIN объединяет таблицы Orders и Customers по полю customer_id. В результате получается таблица, содержащая информацию о заказах вместе с именами клиентов, которые их разместили.

Описание задачи: У вас есть две таблицы: Employees и Departments. Вам нужно найти список всех сотрудников вместе с их названиями отделов.

Таблицы:

Таблица Employees:

employee_id	name	department_id
1	Алексей Петров	10
2	Ольга Иванова	20
3	Дмитрий Смирнов	10
4	Мария Сергеева	30

Таблица Departments:

department_id	department_name
10	IT
20	Бухгалтерия
30	Маркетинг

SELECT Employees.name, Departments.department_name
FROM Employees
INNER JOIN Departments ON Employees.department_id = Departments.department_id;

Результат:

name	department_name
Алексей Петров	IT
Ольга Иванова	Бухгалтерия
Дмитрий Смирнов	IT
Мария Сергеева	Маркетинг

Объяснение: INNER JOIN соединяет таблицы Employees и Departments по полю department_id, что позволяет получить информацию о сотрудниках и их отделах.

Описание задачи: У вас есть две таблицы: Books и Authors. Вам нужно найти список всех книг с именами их авторов.

Таблицы:

Таблица Books:

book_id	book_title	author_id
1	Война и мир	1001
2	Преступление и наказание	1002
3	Идиот	1002
4	Анна Каренина	1001

Таблица Authors:

author_id	author_name
1001	Лев Толстой
1002	Федор Достоевский

SELECT Books.book_title, Authors.author_name
FROM Books
INNER JOIN Authors ON Books.author_id = Authors.author_id;

Результат:

book_title	author_name
Война и мир	Лев Толстой
Преступление и наказание	Федор Достоевский
Идиот	Федор Достоевский
Анна Каренина	Лев Толстой

Объяснение: INNER JOIN объединяет таблицы Books и Authors по полю author_id, что позволяет получить информацию о книгах и их авторах.

Описание задачи: У вас есть две таблицы: Employees и Departments. Вам нужно получить список всех сотрудников вместе с их отделами, включая сотрудников, которые еще не назначены в отдел.

Таблицы:

Таблица Employees:

employee_id	name	department_id
1	Алексей Петров	10
2	Ольга Иванова	NULL
3	Дмитрий Смирнов	20
4	Мария Сергеева	30

Таблица Departments:

department_id	department_name
10	IT
20	Бухгалтерия
30	Маркетинг

SELECT Employees.name, Departments.department_name
FROM Employees
LEFT JOIN Departments ON Employees.department_id = Departments.department_id;

Результат:

employee_name	department_name
Алексей Петров	IT
Ольга Иванова	NULL
Дмитрий Смирнов	Бухгалтерия
Мария Сергеева	Маркетинг

Объяснение: LEFT JOIN возвращает все строки из таблицы Employees, даже если у них нет соответствующих записей в таблице Departments. Для сотрудников без отдела (где department_id равно NULL), поле department_name также будет NULL.

Описание задачи: У вас есть две таблицы: Orders и Customers. Вам нужно найти список всех заказов, включая те, которые были сделаны анонимными клиентами (без регистрации в базе данных).

Таблицы:

Таблица Orders:

order_id	customer_id	order_date
1	101	2024-01-01
2	NULL	2024-01-02
3	102	2024-01-03
4	103	2024-01-04

Таблица Customers:

customer_id	customer_name
101	Иван Иванов
102	Мария Смирнова
103	Алексей Кузнецов

SELECT Orders.order_id, Customers.customer_name, Orders.order_date
FROM Orders
LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id;

Результат:

order_id	customer_name	order_date
1	Иван Иванов	2024-01-01
2	NULL	2024-01-02
3	Мария Смирнова	2024-01-03
4	Алексей Кузнецов	2024-01-04

Объяснение: LEFT JOIN позволяет вернуть все заказы, даже если для них нет информации о клиенте (где customer_id равно NULL). В таких случаях поле customer_name будет NULL.

Описание задачи: У вас есть две таблицы: Students и Courses. Вам нужно получить список всех студентов и курсов, на которые они записаны, включая тех студентов, которые еще не зарегистрировались ни на один курс.

Таблицы:

Таблица Students:

student_id	student_name
1	Анна Кузнецова
2	Борис Иванов
3	Вера Смирнова

Таблица Courses:

course_id	course_name	student_id
1	Математика	1
2	История	1
3	Литература	NULL

SELECT Students.student_name, Courses.course_name
FROM Students
LEFT JOIN Courses ON Students.student_id = Courses.student_id;

Результат:

student_name	course_name
Анна Кузнецова	Математика
Анна Кузнецова	История
Борис Иванов	NULL
Вера Смирнова	NULL

Объяснение: LEFT JOIN возвращает всех студентов, даже если они не зарегистрированы ни на один курс (где student_id равно NULL в таблице Courses). В таких случаях поле course_name будет NULL.

Описание задачи: У вас есть две таблицы: Books и Authors. Вам нужно найти список всех книг, включая те, у которых автор не указан в базе данных.

Таблицы:

Таблица Books:

book_id	book_title	author_id
1	Война и мир	1001
2	Преступление и наказание	1002
3	Идиот	NULL
4	Анна Каренина	1001

Таблица Authors:

author_id	author_name
1001	Лев Толстой
1002	Федор Достоевский

SELECT Books.book_title, Authors.author_name
FROM Books
LEFT JOIN Authors ON Books.author_id = Authors.author_id;

Результат:

book_title	author_name
Война и мир	Лев Толстой
Преступление и наказание	Федор Достоевский
Идиот	NULL
Анна Каренина	Лев Толстой

Объяснение: LEFT JOIN возвращает все книги, даже если для них нет информации о авторе (где author_id равно NULL). В таких случаях поле author_name будет NULL.

Задача 1: Список всех отделов и сотрудников, работающих в них

Описание задачи: У вас есть две таблицы: Employees и Departments. Вам нужно получить список всех отделов и сотрудников, работающих в них, включая отделы, в которых пока нет сотрудников.

Таблицы:

Таблица Employees:

employee_id	employee_name	department_id
1	Алексей Петров	10
2	Ольга Иванова	20
3	Дмитрий Смирнов	10

Таблица Departments:

department_id	department_name
10	IT
20	Бухгалтерия
30	Маркетинг

SELECT Employees.employee_name, Departments.department_name
FROM Employees
RIGHT JOIN Departments ON Employees.department_id = Departments.department_id;

Результат:

employee_name	department_name
Алексей Петров	IT
Дмитрий Смирнов	IT
Ольга Иванова	Бухгалтерия
NULL	Маркетинг

Объяснение: RIGHT JOIN соединяет таблицы Employees и Departments по полю department_id, при этом выбираются все записи из таблицы Departments, даже если в соответствующем отделе нет сотрудников. В случае отсутствия сотрудников для конкретного отдела, в столбце employee_name будет NULL.

Описание задачи: У вас есть две таблицы: Orders и Customers. Вам нужно получить список всех клиентов и их заказов, включая клиентов, которые пока не сделали заказ.

Таблицы:

Таблица Orders:

order_id	customer_id	order_date
1	101	2024-01-01
2	102	2024-01-02
3	101	2024-01-03

Таблица Customers:

customer_id	customer_name
101	Иван Иванов
102	Мария Смирнова
103	Алексей Кузнецов

SELECT Customers.customer_name, Orders.order_id, Orders.order_date
FROM Orders
RIGHT JOIN Customers ON Orders.customer_id = Customers.customer_id;

Результат:

customer_name	order_id	order_date
Иван Иванов	1	2024-01-01
Иван Иванов	3	2024-01-03
Мария Смирнова	2	2024-01-02
Алексей Кузнецов	NULL	NULL

Объяснение: RIGHT JOIN соединяет таблицы Orders и Customers по полю customer_id, при этом выбираются все записи из таблицы Customers, даже если у клиента еще нет заказов. Если клиент не сделал заказ, столбцы order_id и order_date будут содержать NULL.

Описание задачи: У вас есть две таблицы: Students и Courses. Вам нужно получить список всех курсов и студентов, записанных на них, включая курсы, на которые никто еще не записался.

Таблицы:

Таблица Students:

student_id	student_name
1	Анна Сергеева
2	Иван Петров

Таблица Courses:

course_id	course_name	student_id
101	Математика	1
102	Литература	2
103	Физика	NULL

SELECT Courses.course_name, Students.student_name
FROM Students
RIGHT JOIN Courses ON Students.student_id = Courses.student_id;

Результат:

course_name	student_name
Математика	Анна Сергеева
Литература	Иван Петров
Физика	NULL

Объяснение: RIGHT JOIN соединяет таблицы Students и Courses по полю student_id, при этом выбираются все записи из таблицы Courses, даже если на курс никто еще не записался. В этом случае, если на курсе нет студентов, столбец student_name будет содержать NULL.

Описание задачи: У вас есть таблица Employees, которая содержит информацию о сотрудниках и их руководителях. Вам нужно найти всех сотрудников, которые подчиняются одному и тому же руководителю.

Таблица Employees:

employee_id	employee_name	manager_id
1	Иван Иванов	3
2	Мария Смирнова	3
3	Алексей Петров	NULL
4	Дмитрий Кузнецов	3
5	Ольга Сергеева	4

SELECT e1.employee_name AS employee, e2.employee_name AS manager
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.employee_id;

Результат:

employee	manager
Иван Иванов	Алексей Петров
Мария Смирнова	Алексей Петров
Дмитрий Кузнецов	Алексей Петров
Ольга Сергеева	Дмитрий Кузнецов

Объяснение: SELF JOIN используется для того, чтобы соединить таблицу Employees саму с собой. Мы сопоставляем сотрудников (e1) с их руководителями (e2) через поле manager_id. В результате получается таблица с именами сотрудников и их менеджеров.

Описание задачи: У вас есть таблица Employees, которая содержит информацию о сотрудниках и их отделах. Вам нужно найти все пары сотрудников, работающих в одном и том же отделе.

Таблица Employees:

employee_id	name	department_id
1	Иван Иванов	10
2	Мария Смирнова	20
3	Алексей Петров	10
4	Дмитрий Кузнецов	30
5	Ольга Сергеева	20

SELECT e1.name AS employee1, e2.name AS employee2
FROM Employees e1
JOIN Employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id < e2.employee_id

Результат:

employee1	employee2
Иван Иванов	Алексей Петров
Мария Смирнова	Ольга Сергеева

Объяснение: SELF JOIN используется для нахождения пар сотрудников, работающих в одном отделе. Условие WHERE e1.employee_id < e2.employee_id предотвращает дублирование и исключает пары сотрудник-сам-собой. В результате вы получаете список пар сотрудников, работающих в одном и том же отделе.

Описание задачи: У вас есть таблица Employees, которая содержит информацию о сотрудниках, их отделах и зарплатах. Вам нужно найти сотрудников, которые зарабатывают больше всех других сотрудников в их отделе.

Таблица Employees:

employee_id	name	department_id	salary
1	Иван Иванов	10	50000
2	Мария Смирнова	20	60000
3	Алексей Петров	10	55000
4	Дмитрий Кузнецов	30	70000
5	Ольга Сергеева	20	60000

SELECT e1.name, e1.salary
FROM Employees e1
LEFT JOIN Employees e2 ON e1.department_id = e2.department_id AND e1.salary < e2.salary
WHERE e2.employee_id IS NULL

Результат:

employee_name	salary
Алексей Петров	55000
Дмитрий Кузнецов	70000

Объяснение: Здесь используется SELF JOIN для сравнения зарплат сотрудников в одном отделе. Если у сотрудника нет коллег в его отделе, которые зарабатывают больше (проверка WHERE e2.employee_id IS NULL), то этот сотрудник считается самым высокооплачиваемым в своем отделе.

Найти количество сотрудников в каждом департаменте

Таблица employees:

employee_id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	55000
3	Charlie	HR	60000
4	David	HR	62000
5	Eve	IT	70000

WITH department_counts AS (
    SELECT department, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department
)
SELECT department, employee_count
FROM department_counts;

Результат:

department	employee_count
Sales	2
HR	2
IT	1

Объяснение: CTE department_counts считает количество сотрудников в каждом департаменте, группируя данные по колонке department. Основной запрос просто выбирает данные из CTE.

Найти сотрудников с зарплатой выше среднего

Таблица employees:

employee_id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	55000
3	Charlie	HR	60000
4	David	HR	62000
5	Eve	IT	70000

WITH avg_salary AS (
    SELECT AVG(salary) AS overall_avg_salary
    FROM employees
)
SELECT name, department, salary
FROM employees
WHERE salary > (SELECT overall_avg_salary FROM avg_salary);

Объяснение: CTE avg_salary вычисляет среднюю зарплату всех сотрудников. Основной запрос выбирает сотрудников, чья зарплата выше этой средней.

Результат:

name	department	salary
Charlie	HR	60000
David	HR	62000
Eve	IT	70000

Найти самую низкую зарплату в каждом департаменте

Таблица employees:

employee_id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	55000
3	Charlie	HR	60000
4	David	HR	62000
5	Eve	IT	70000

WITH min_salaries AS (
    SELECT department, MIN(salary) AS min_salary
    FROM employees
    GROUP BY department
)
SELECT department, min_salary
FROM min_salaries;

Объяснение: CTE min_salaries определяет минимальную зарплату в каждом департаменте, группируя данные по колонке department.

Результат:

department	min_salary
Sales	50000
HR	60000
IT	70000

Найти сотрудников, которые зарабатывают больше среднего по своему департаменту

Таблица employees:

employee_id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	55000
3	Charlie	HR	60000
4	David	HR	62000
5	Eve	IT	70000

WITH department_avg_salary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary
FROM employees e
JOIN department_avg_salary das
ON e.department = das.department
WHERE e.salary > das.avg_salary;

Объяснение: CTE department_avg_salary вычисляет среднюю зарплату по каждому департаменту. Основной запрос выбирает сотрудников, чья зарплата выше средней по их департаменту.

Результат:

name	department	salary
Bob	Sales	55000
David	HR	62000

Подсчитать общее количество сотрудников и общую сумму зарплат

Таблица employees:

employee_id	name	department	salary
1	Alice	Sales	50000
2	Bob	Sales	55000
3	Charlie	HR	60000
4	David	HR	62000
5	Eve	IT	70000

WITH totals AS (
    SELECT COUNT(*) AS total_employees, SUM(salary) AS total_salary
    FROM employees
)
SELECT total_employees, total_salary
FROM totals;

Объяснение: CTE totals вычисляет общее количество сотрудников и сумму их зарплат. Основной запрос просто выбирает эти значения из CTE.

Результат:

total_employees	total_salary
5	297000

Оптимизируйте SQL запрос и объясните своё решение

SELECT e.employee_id, e.name
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

WITH dept_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.name
FROM employees e
JOIN dept_avg_salary das ON e.department_id = das.department_id
WHERE e.salary > das.avg_salary;

Обоснование:

• Использование CTE: CTE позволяет один раз вычислить среднюю зарплату по департаменту, а затем использовать результат для фильтрации сотрудников. Это устраняет необходимость повторного выполнения подзапроса для каждой строки в таблице employees.

• JOIN вместо подзапроса: В большинстве случаев JOIN более эффективен, чем подзапрос, особенно если используется CTE или предварительно агрегированные данные.

Потенциальное ускорение: Ожидаемое ускорение в 2-4 раза.

Оптимизируйте SQL запрос и объясните своё решение

SELECT c.customer_id, c.customer_name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY c.customer_id, c.customer_name;

SELECT c.customer_id, c.customer_name, total_spent
FROM customers c
JOIN (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE order_date >= '2023-01-01'
    GROUP BY customer_id
) o_sum ON c.customer_id = o_sum.customer_id;

Обоснование:

• Предварительная агрегация: Агрегация данных по заказам выполняется до выполнения JOIN. Это позволяет уменьшить объем данных, с которыми работает основной запрос, что снижает нагрузку и ускоряет выполнение.

Потенциальное ускорение: Оптимизация может ускорить запрос в 1.5-2 раза, особенно если таблица orders содержит большое количество данных.

delete

Оптимизируйте SQL запрос и объясните своё решение

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 100 AND order_date > '2023-01-01' AND status = 'completed';

SELECT order_id, customer_id, total_amount
FROM orders
WHERE status = 'completed' AND order_date > '2023-01-01' AND total_amount > 100;

Обоснование:

• Расположение условий: В PostgreSQL порядок условий в WHERE может влиять на производительность, особенно если одни условия более селективны, чем другие. Помещение наиболее селективных условий в начало может уменьшить количество строк, которые проходят через последующие фильтры.

Потенциальное ускорение: В зависимости от данных, улучшение может быть незначительным, но иногда может ускорить запрос в 1.5-2 раза.

SELECT order_id, product_id
FROM orders
WHERE product_id = ANY (ARRAY[1, 2, 3]);

Оптимизируйте SQL запрос и объясните своё решение

SELECT order_id, product_id
FROM orders
WHERE product_id IN (1, 2, 3);

Обоснование:

• Замена ANY на IN: В PostgreSQL использование IN может быть более эффективным, чем ANY при работе с небольшими наборами данных. Это связано с тем, что IN легче оптимизируется и исполняется быстрее.

Потенциальное ускорение: В зависимости от количества данных, улучшение может быть в 1.5-2 раза.

Ситуация: У вас есть таблица users, которая содержит миллионы записей с информацией о пользователях. Часто требуется быстро находить пользователя по его уникальному идентификатору (user_id), который является нечисловым полем, например, UUID или строка.

Задание: Создайте HASH индекс на поле user_id, чтобы ускорить выполнение запроса, и объясните, почему в данном случае использование HASH индекса предпочтительно.

CREATE INDEX idx_users_user_id_hash ON users USING HASH (user_id);

Обоснование:

• Когда использовать HASH индекс: HASH индексы идеально подходят для точных соответствий (=) по значению, особенно если это частая операция. Они обеспечивают быстрый доступ к данным при сравнении на равенство.

• Преимущество HASH индекса: В отличие от индексов типа BTREE, которые эффективны для диапазонных запросов, HASH индексы могут быть быстрее при работе с точными значениями, так как они используют хэш-функцию для быстрого поиска соответствия.

Ситуация: У вас есть таблица locations, которая содержит географические координаты (долгота и широта) местоположений, используя тип данных point. Вам нужно часто находить все точки, находящиеся в определенном радиусе от заданной точки.

Задание: Создайте GiST индекс на поле с координатами и объясните, почему GiST индекс подходит для этой задачи.

CREATE INDEX idx_locations_point_gist ON locations USING GiST (point(longitude, latitude));

Обоснование:

• Когда использовать GiST индекс: GiST индексы отлично подходят для географических данных, особенно для операций, связанных с поиском в пределах определенной области (например, точки в круге).

• Эффективность: GiST индекс может существенно ускорить выполнение пространственных запросов, таких как поиск точек внутри окружности, прямоугольника или другой формы.