Задачи SQL с решениями. Десять практических упражнений SQL

Эти вопросы и примеры решений помогут Вам развить свои навыки в SQL.

Введение

Cтруктурированный Язык Запросов (SQL) используется для извлечения и обработки данных, хранящихся в реляционных базах данных. Получение практических навыков работы с SQL является важной предпосылкой для многих технологических профессий и требует постоянной практики. Разбор задач SQL с решениями важный аспект прокачки навыков.

В дополнение к ресурсам для обучения SQL (PGExercises. LeetCode. HackerRank. Mode),доступным в Интернете, я составил список моих любимых вопросов, которые Вы можете изучать сами .

@sqlhub – разбор реальных задач с SQL собеседований в нашем телеграм канале.

Эти вопросы охватывают следующие важнейшие концепции:

• Базовый поиск (Basic retrieval) (SELECT,FROM)
• (Creating and aliasing) (WITH, AS, GENERATE_SERIES)
• Фильтрация (Filtering) (DISTINCT, WHERE, HAVING, AND, OR, IN, NOT IN)
• Агрегирование (Aggregation) (GROUP BY with COUNT, SUM, AVERAGE)
• Джоины (Joins) (INNER JOIN, LEFT JOIN, FULL OUTER JOIN on one or multiple (in)equalities, CROSS JOIN, UNION and UNION ALL)
• условные операторы (Conditioinal) (CASE – WHEN – THEN – ELSE – END)
• Оконные функции (Window functions) (RANK, DENSE_RANK, ROW_NUMBER,SUM with PARTITION BY – ORDER BY)
• Форматирование (Formatting) (LIMIT, ORDER BY, casting as an integer, float, or date, CONCAT COALESCE)
• Арифметика операций и сравнений (Arithmetic) (+, -, *, /, //, ^, <, >, =, !=)
• Операции с датами и временем (Datetime operations) (EXTRACT (month / day / year))

Вы можете изучть их самостоятельно, загрузив PostgreSQL и PSequel (пошаговое руководство по установке см. В Этом руководстве), а затем выполнив запросы, показанные в серых полях в тексте ниже. PSequel доступен только для Mac — если вы используете ПК, вы можете попробовать один из этих вариантов Windows.

Сначала рассмотрим исходную таблицу, которая соответствует формату:

WITH input_table (column_1, column_2)
AS (VALUES
(1, 'A'), (2, 'B'))

Вы можете выполнять запросы к таблице с помощью PSequel (показано выше) и легко создавать новые таблицы для своих собственных задач, используя этот шаблон.

Веб-ресурсы для обучения SQL не соответствуют нескольким параметрам. Например, LeetCode не поддерживает использование оконных функций и скрывает свои самые интересные вопросы за платным доступом. Кроме того, выполнение SQL—запросов в вашем браузере может быть чрезвычайно медленным – наборы данных большие, а скорость поиска часто ограничена для пользователей, не являющихся премиум-пользователями. С другой стороны, локальное выполнение запроса выполняется мгновенно и позволяет выполнять быструю итерацию через промежуточные таблицы.

Вопросы, изложенные ниже, включают примеры решени для PostgreSQL. Имейте в виду, что обычно существует более одного способа получить правильный ответ на задачу SQL. Я предпочитаю использовать общие табличные выражения (CTE), а не вложенные подзапросы — CTE. Однако оба подхода могут привести к идентичным решениям. Мне также нравится использовать  стиль SQL со всеми заглавными буквами (SELECT, FROM, WHERE и т. Д.), имена столбцов в нижнем регистре (user_id, дата и т. Д.) И простое псевдонимирование таблиц (t1, t2 и т. Д.),там Где это возможно.

Фрагменты кода, показанные ниже, могут быть запущены в PSequel , без изменений. Обратите внимание на одну особенность Postgres: дроби должны быть умножены на 1.0 для преобразования из целочисленного формата в формат с плавающей запятой. Это не нужно делать в других реализациях SQL.

Не стесняйтесь оставлять свои альтернативные ответы в комментариях!

Вопросы

1. Показатели отказов

Из следующей таблицы идентификаторов пользователей, действий и дат напишите запрос, чтобы вернуть количество отказов от услуг для каждого пользователя.

WITH users (user_id, action, date) 
AS (VALUES 
(1,'start', CAST('01-01-20' AS date)), 
(1,'cancel', CAST('01-02-20' AS date)), 
(2,'start', CAST('01-03-20' AS date)), 
(2,'publish', CAST('01-04-20' AS date)), 
(3,'start', CAST('01-05-20' AS date)), 
(3,'cancel', CAST('01-06-20' AS date)), 
(1,'start', CAST('01-07-20' AS date)), 
(1,'publish', CAST('01-08-20' AS date))),
-- retrieve count of starts, cancels, and publishes for each usert1 AS (
SELECT 
   user_id,
   SUM(CASE WHEN action = 'start' THEN 1 ELSE 0 END) AS starts, 
   SUM(CASE WHEN action = 'cancel' THEN 1 ELSE 0 END) AS cancels, 
   SUM(CASE WHEN action = 'publish' THEN 1 ELSE 0 END) AS publishes
FROM users
GROUP BY 1
ORDER BY 1)-- calculate publication, cancelation rate for each user by dividing by number of starts, casting as float by multiplying by 1.0 (default floor division is a quirk of some SQL tools, not always needed)SELECT 
   user_id, 
   1.0*publishes/starts AS publish_rate, 
   1.0*cancels/starts AS cancel_rate
FROM t1

2. Изменения в капитале

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

WITH transactions (sender, receiver, amount, transaction_date) 
AS (VALUES 
(5, 2, 10, CAST('2-12-20' AS date)),
(1, 3, 15, CAST('2-13-20' AS date)), 
(2, 1, 20, CAST('2-13-20' AS date)), 
(2, 3, 25, CAST('2-14-20' AS date)), 
(3, 1, 20, CAST('2-15-20' AS date)), 
(3, 2, 15, CAST('2-15-20' AS date)), 
(1, 4, 5, CAST('2-16-20' AS date))),
-- sum amounts for each sender (debits) and receiver (credits)
debits AS (
SELECT 
   sender, 
   SUM(amount) AS debited
FROM transactions
GROUP BY 1 ),
credits AS (
SELECT 
   receiver, 
   SUM(amount) AS credited
FROM transactions
GROUP BY 1 )
-- full (outer) join debits and credits tables on user id, taking net change as difference between credits and debits, coercing nulls to zeros with coalesce()
SELECT 
   COALESCE(sender, receiver) AS user, 
   COALESCE(credited, 0) - COALESCE(debited, 0) AS net_change 
FROM debits d
FULL JOIN credits c
ON d.sender = c.receiver
ORDER BY 2 DESC

3. Наиболее часто встречающиеся элементы

Из следующей таблицы, содержащей список дат и товаров нтов, напишите запрос, чтобы вернуть наиболее часто заказываемый товар на каждую дату.

WITH items (date, item) 
AS (VALUES 
(CAST('01-01-20' AS date),'apple'), 
(CAST('01-01-20' AS date),'apple'), 
(CAST('01-01-20' AS date),'pear'), 
(CAST('01-01-20' AS date),'pear'), 
(CAST('01-02-20' AS date),'pear'), 
(CAST('01-02-20' AS date),'pear'), 
(CAST('01-02-20' AS date),'pear'), 
(CAST('01-02-20' AS date),'orange')),
-- add an item count column to existing table, grouping by date and item columns
t1 AS (
SELECT 
   date, 
   item, 
   COUNT(*) AS item_count
FROM items
GROUP BY 1, 2
ORDER BY 1),
-- add a rank column in descending order, partitioning by date
t2 AS (
SELECT 
   *, 
   RANK() OVER (PARTITION BY date ORDER BY item_count DESC) AS date_rank
FROM t1)
-- return all dates and items where rank = 1
SELECT 
   date, 
   item
FROM t2
WHERE date_rank = 1

4. Разница во времени между последними действиями

Из следующей таблицы действий пользователя напишите запрос, который вернет для каждого пользователя время, прошедшее между последним действием и предпоследним действием, в порядке возрастания по идентификатору пользователя.

5. Суперпользователи

Компания определяет своих суперпользователей как тех, кто совершил не менее двух транзакций. Из следующей таблицы напишите запрос, чтобы вернуть для каждого пользователя дату, когда он стал суперпользователем, сначала тех, кто стал раньше суперпользователем. Пользователи, которые не являются суперпользователями, также должны присутствовать в таблице.

WITH users (user_id, product_id, transaction_date) 
AS (VALUES 
(1, 101, CAST('2-12-20' AS date)), 
(2, 105, CAST('2-13-20' AS date)), 
(1, 111, CAST('2-14-20' AS date)), 
(3, 121, CAST('2-15-20' AS date)), 
(1, 101, CAST('2-16-20' AS date)), 
(2, 105, CAST('2-17-20' AS date)),
(4, 101, CAST('2-16-20' AS date)), 
(3, 105, CAST('2-15-20' AS date))),
-- create a transaction number column using ROW_NUMBER(), partitioning by user ID
t1 AS (
SELECT 
   *, 
   ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS transaction_number
FROM users),
-- filter resulting table on transaction_number = 2
t2 AS (
SELECT 
   user_id, 
   transaction_date
FROM t1
WHERE transaction_number = 2 ),
-- left join super users onto full user table, order by date 
t3 AS (
SELECT DISTINCT user_id
FROM users )
SELECT 
   t3.user_id, 
   transaction_date AS superuser_date
FROM t3
LEFT JOIN t2
ON t3.user_id = t2.user_id
ORDER BY 2

6. Система рекомендаций

Используя следующие две таблицы, напишите запрос для возврата рекомендаций страниц пользователю социальной сети на основе страниц, которые понравились его друзьям, но которые сам пользователь еще не отметили как понравившиеся. Упорядочите результат по возрастанию идентификатора пользователя.

WITH friends (user_id, friend) 
AS (VALUES 
(1, 2), (1, 3), (1, 4), (2, 1), (3, 1), (3, 4), (4, 1), (4, 3)),
likes (user_id, page_likes) 
AS (VALUES 
(1, 'A'), (1, 'B'), (1, 'C'), (2, 'A'), (3, 'B'), (3, 'C'), (4, 'B')),
-- inner join friends and page likes tables on user_id
t1 AS (
SELECT 
   l.user_id, 
   l.page_likes, 
   f.friend
FROM likes l
JOIN friends f
ON l.user_id = f.user_id ),
-- left join likes on this, requiring user = friend and user likes = friend likes 
t2 AS (
SELECT 
   t1.user_id,
   t1.page_likes, 
   t1.friend, 
   l.page_likes AS friend_likes
FROM t1
LEFT JOIN likes l
ON t1.friend = l.user_id
AND t1.page_likes = l.page_likes )
-- if a friend pair doesn’t share a common page like, friend likes column will be null - pull out these entries 
SELECT DISTINCT 
   friend AS user_id, 
   page_likes AS recommended_page
FROM t2
WHERE friend_likes IS NULL
ORDER BY 1

7. Посетители сайта.

С помощью следующих двух таблиц верните долю пользователей, которые посещали сайт только с мобильных устройств, только с веб версии и тех кто посещал со обоих устройств.

WITH mobile (user_id, page_url) 
AS (VALUES 
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'A'), (9, 'B'), (2, 'C'), (10, 'B')),
web (user_id, page_url) 
AS (VALUES 
(6, 'A'), (2, 'B'), (3, 'C'), (7, 'A'), (4, 'B'), (8, 'C'), (5, 'B')),

-- outer join mobile and web users on user ID
t1 AS (
SELECT DISTINCT 
   m.user_id AS mobile_user, 
   w.user_id AS web_user
FROM mobile m
FULL JOIN web w
ON m.user_id = w.user_id)
-- calculate fraction of mobile-only, web-only, and both as average of values (ones and zeros) specified in case statement condition
SELECT 
   AVG(CASE WHEN mobile_user IS NOT NULL AND web_user IS NULL THEN 1   ELSE 0 END) AS mobile_fraction,
   AVG(CASE WHEN web_user IS NOT NULL AND mobile_user IS NULL THEN 1 ELSE 0 END) AS web_fraction,
   AVG(CASE WHEN web_user IS NOT NULL AND mobile_user IS NOT NULL THEN 1 ELSE 0 END) AS both_fraction
FROM t1

8. Задача на уровни доступа

Учитывая следующие две таблицы, верните долю пользователей, округленную до двух знаков после запятой, которые получили доступ F2 и повысили свой уровень доступа до premium в течение первых 30 дней с момента регистрации.

WITH users (user_id, name, join_date) 
AS (VALUES 
(1, 'Jon', CAST('2-14-20' AS date)), 
(2, 'Jane', CAST('2-14-20' AS date)), 
(3, 'Jill', CAST('2-15-20' AS date)), 
(4, 'Josh', CAST('2-15-20' AS date)), 
(5, 'Jean', CAST('2-16-20' AS date)), 
(6, 'Justin', CAST('2-17-20' AS date)),
(7, 'Jeremy', CAST('2-18-20' AS date))),
events (user_id, type, access_date) 
AS (VALUES 
(1, 'F1', CAST('3-1-20' AS date)), 
(2, 'F2', CAST('3-2-20' AS date)), 
(2, 'P', CAST('3-12-20' AS date)),
(3, 'F2', CAST('3-15-20' AS date)), 
(4, 'F2', CAST('3-15-20' AS date)), 
(1, 'P', CAST('3-16-20' AS date)), 
(3, 'P', CAST('3-22-20' AS date))),

-- get feature 2 users and their date of feature 2 access
t1 AS (
SELECT 
   user_id, 
   type, 
   access_date AS f2_date
FROM events
WHERE type = 'F2' ),
-- get premium users and their date of premium upgrade
t2 AS (
SELECT 
   user_id, 
   type, 
   access_date AS premium_date
FROM events
WHERE type = 'P' ),
-- for each feature 2 user, get time between joining and premium upgrade (or null if no upgrade) by inner joining full users table with feature 2 users on user ID and left joining premium users on user ID, then subtracting premium upgrade date from join date
t3 AS (
SELECT t2.premium_date - u.join_date AS upgrade_time
FROM users u
JOIN t1
ON u.user_id = t1.user_id
LEFT JOIN t2
ON u.user_id = t2.user_id )
-- calculate fraction of users with upgrade time less than 30 days as average of values (ones and zeros) specified in case statement condition, rounding to two decimal places
 
SELECT 
   ROUND(AVG(CASE WHEN upgrade_time < 30 THEN 1 ELSE 0 END), 2) AS upgrade_rate
FROM t3

9. Самые близкие друзья

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

WITH friends (user1, user2) 
AS (VALUES (1, 2), (1, 3), (1, 4), (2, 3)),

-- compile all user appearances into one column, preserving duplicate entries with UNION ALL 
t1 AS (
SELECT user1 AS user_id
FROM friends
UNION ALL
SELECT user2 AS user_id
FROM friends)
-- grouping by user ID, count up all appearances of that user
SELECT 
   user_id, 
   COUNT(*) AS friend_count
FROM t1
GROUP BY 1
ORDER BY 2 DESC

10. Агрегация проектов (сложная задача)

Таблица projects содержит три столбца: task_id, start_date и end_date. Разница между end_date и start_date составляет 1 день для каждой строки в таблице. Если даты окончания задачи являются последовательными, они являются частью одного и того же проекта. Проекты не пересекаются.

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

WITH projects (task_id, start_date, end_date) 
AS (VALUES 
(1, CAST('10-01-20' AS date), CAST('10-02-20' AS date)), 
(2, CAST('10-02-20' AS date), CAST('10-03-20' AS date)), 
(3, CAST('10-03-20' AS date), CAST('10-04-20' AS date)), 
(4, CAST('10-13-20' AS date), CAST('10-14-20' AS date)), 
(5, CAST('10-14-20' AS date), CAST('10-15-20' AS date)), 
(6, CAST('10-28-20' AS date), CAST('10-29-20' AS date)), 
(7, CAST('10-30-20' AS date), CAST('10-31-20' AS date))),
-- get start dates not present in end date column (these are “true” project start dates) 
t1 AS (
SELECT start_date
FROM projects
WHERE start_date NOT IN (SELECT end_date FROM projects) ),
-- get end dates not present in start date column (these are “true” project end dates) 
t2 AS (
SELECT end_date
FROM projects
WHERE end_date NOT IN (SELECT start_date FROM projects) ),
-- filter to plausible start-end pairs (start < end), then find correct end date for each start date (the minimum end date, since there are no overlapping projects)
t3 AS (
SELECT 
   start_date, 
   MIN(end_date) AS end_date
FROM t1, t2
WHERE start_date < end_date
GROUP BY 1 )
SELECT 
   *, 
   end_date - start_date AS project_duration
FROM t3
ORDER BY 3, 1