Всем доброго дня!

Стал искать задачи по SQL, чтобы освежить свои знания, и к немалому удивлению обнаружил, что, несмотря на очевидную востребованность темы, интересные наборы задач на русскоязычных ресурсах можно пересчитать по пальцам. Хочу поделиться с сообществом своим мнением по поводу этих наборов, тем более что в отличие от самих задач далеко не все их авторские решения мне понравились.

Первый интересный набор задач я нашёл, конечно же, на Хабре ???? – этот пост. Автор безусловно – молодец, проделал большую работу! Но при всех благодарностях нужно отметить, что частенько его подход к решению задач был однобоким – автор везде где надо и где не надо применял join, забывая про другие возможности языка (оконные функции, union, except (или minus)), вследствие чего решения получались громоздкими и неестественными – такими, что через месяц будешь с трудом вспоминать, что же ты имел ввиду, когда писал код:

1.1. Для решения первой задачи (найти ежемесячное процентное изменение месячной аудитории активных пользователей) автор использовал join, а нужно было бы вспомнить про оконные функции lag или lead, которые идеально подходят для определения изменений какой-либо величины. Их использование делает запрос гораздо понятнее и компактнее:

with Per_month_users_count as (select count(distinct user_id) as users_count, date(date, 'start of month') as month_id from logins group by month_id)

select 100.*(1-(1./users_count)*Lag(users_count) over (order by month_id)), strftime('%m-%Y', month_id) from Per_month_users_count

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

Решение дано под SQLite, но сути не это не меняет).

1.2. Решая вторую задачу (маркировки древовидной структуры), автор тоже пошёл ненужно сложным путём (там и join, и group by с count, и case when). А стоило бы просто присмотреться к самой исходной таблице – она сформирована так, что узлы очень просто разбить на искомые классы (корень, листы, внутренние узлы) – в столбце parent могут находиться только внутренние узлы и корень, соответственно множество всех листов равно разности множества узлов, находящихся в столбце node, и множества узлов, находящихся в столбце parent.

Соответственно, вместо join, group by с count, и case when здесь гораздо логичнее и проще использовать union и except (или minus – в зависимости от СУБД):

with Root(id) as
(select node from tree
where parent is null),

Inner_nodes(id) as
(select distinct parent
from tree
where parent is not null
except
select * from Root),

Leafs(id) as
(select node from tree
except
select * from Inner_nodes
except
select * from Root)

select id, 'Root' from Root
union
select id, 'Inner' from Inner_nodes
union
select id, 'Leaf' from Leafs
order by id

(решение дано под sqlite).

1.3. 3-ю часть 3-й задачи (определение активных пользователей, которые были реактивированы в прошлом) автор позиционировал как особенно сложную («более сложную, чем вам предложат на реальном собеседовании»), и дал зубодробильное решение. Но на самом деле сложности то особой нет, просто опять надо вспомнить про окна ???? – для определения «дыры» в посещениях сайта пользователем идеально подходит функция lead:

with current_users(id, date) as (select l1.id, l1.date from logins as l1
inner join logins as l2
on l1.id = l2.id and date(l2.date, 'start of month') = date('now', 'start of month')

--выбираем из таблицы logins идентификаторы и даты визитов только «активных» пользователей (тех, кто был активен в текущий месяц)

select id,
date as user_timeout_start,
next_date as user_timeout_end
from
(select id,
date,
lead(date) over(partition by id order by date) as next_date
from current_users)
where date(date, 'start of month') = date(next_date, 'start of month',
'-1 days', 'start of month',
'-1 days', 'start of month')

--определяем тех «активных» пользователей, которые в какой-либо из месяцев не посещали сайт (становились «неактивными»).

1.4. Для решения задачи 6 автор использовал join и группировку, а вновь стоило бы вспомнить про оконные функции ????:

select id,
round((julianday(response_timestamp) - julianday(timestamp)) * 86400) as response_time
from
(select id,
to_,
timestamp,
min(case when from_ = 'zach@g.com' then timestamp
else null end) over(partition by subject order by timestamp rows between 1 following and unbounded following) as response_timestamp
from Emails)
where to_ = 'zach@g.com'
order by id

Используем min, как оконную функцию, подавая на её вход только те timestamp, которые соответствуют строкам с отправителем 'zach@g.com'. В качестве диапазона строк окна используем диапазон, начиная со строки, следующей за текущей.

1.5. 3-ю задачу из раздела «Другие задачи средней и высокой сложности» автор позиционирует, как ещё одну задачу повышенной сложности. Но слишком сложным здесь является только подход автора к решению. А самое печальное, что автор не обращает внимание, что его подход не является универсальным – если добавится ещё один класс, то всё, решение можно выкидывать. Странно, что автор не видит лежащее, прямо скажем, на поверхности кондовое универсальное решение – добавить таблицу, в которой привести величину приоритета каждого класса classes_priorities:

Теперь написать требуемый запрос совсем не сложно, плюс он будет работать при любом числе классов.
select fact_class, count(fact_class)
from
(select max(t2.priority), t2.class as fact_class
from users t1
inner join classes t2
on t1.class = t2.class
group by t1.user)
group by fact_class.
Но в любом случае автору вышеописанной подборки задач большое спасибо за её составление!

Вторая хорошая подборка задач есть на uproger.com: https://uproger.com/zadachi-sql-s-resheniyami-dvadczat-pyat-prakticheskih-uprazhnenij-sql/.

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

2.1. В решении 2-й задаче (изменение в капитале) автор использует full join и coalesce. Но с union запрос получается компактнее и естественнее:

select id, sum(cashflow) as sum_ from
(select sender as id, -amount as cashflow from transactions
union all
select receiver as id, amount as cashflow from transactions)
group by id
order by sum_ desc.

2.2. Решение 4-й задачи (разница во времени между последними действиями) почему-то не приведено. Привожу его здесь.

select user_id,
cast((julianday(action_date) - julianday(prev_action_date)) as integer) as days_elapsed
from
(select user_id,
action_date,
lead(action_date) over(partition by user_id order by action_date desc) as prev_action_date,
row_number() over(partition by user_id order by action_date desc) as row_number
from users)
where row_number = 1
order by user_id

--чтобы определить разницу во времени между последовательными действиями, логично использовать оконную функцию lead, а чтобы выбрать последнее действие, логично использовать оконную функцию row_number. Для обеих оконных функций сортировка выполняется в порядке убывания дат.

2.3. В решении 5-й задачи (суперпользователи) использованы три CTE (один с оконной функцией) и left join. Но если в CTE или подзапросе использовать не только row_number, но и count, как оконную функцию, то можно обойтись без left join, и сделать запрос компактнее:

select user_id,
case when user_transactions_count > 1 then transaction_date
else Null end as superuser_date
from
(select user_id,
transaction_date,
row_number() over(partition by user_id order by transaction_date) as row_number,
count(user_id) over(partition by user_id) as user_transactions_count
from users)
where (row_number = 2 and user_transactions_count > 1) or
(row_number = 1 and user_transactions_count = 1)
order by row_number desc, transaction_date

-- если для какого-то пользователя значение оконной функции count равно 1, то это маркер, что этот пользователь не является суперпользователем.

2.4.10-ю задачу 10 автор позиционировал, как самую сложную. Приведённое автором решение и впрямь довольно сложное, но если использовать оконные функции, его можно сильно упростить. Очевидно, что если взять непрерывно идущие друг за другом даты, то и juliandate, и row_number для них будут последовательно возрастать на единицу при возрастании дат, Т.е. для какой-либо группы непрерывно идущих друг за другом дат разница между juliandate и row_number будет одинаковой, и её можно взять как идентификатор такой группы. Теперь запрос становится делом техники ????:

with group_by_projects as
(select start_date,
(cast(julianday(start_date) as integer) -
row_number() over(order by start_date)) as project_id
from projects)

select start_date,
date(julianday(start_date) + count()), count() as project_duration
from group_by_projects
group by project_id
order by project_duration

Есть ещё подборка на https://tproger.ru/articles/5-zadanij-po-sql-s-realnyh-sobesedovanij/, но она совсем маленькая. Приведённое там решение 3-й задачи сработает только для PostgreeSQL. А в общем случае придётся повозиться посильнее:

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

alter table ClientBalance add duplicates_id integer

Затем создаём CTE, в котором с помощью оконной функции row_number() нумеруем дубликаты, а затем инсертим в исходную таблицу те строки из указанного CTE, для которых идентификатор дубликата равен 1:

with create_duplicates_id as
(select client_id,
client_name,
client_balance_date,
client_balance_value,
row_number() over(partition by client_id, client_name, client_balance_date, client_balance_value order by (select 0)) as duplicates_id
from ClientBalance)
insert into ClientBalance select * from create_duplicates_id where duplicates_id = 1

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

delete from ClientBalance where duplicates_id is null

Ну и в финале удаляем вспомогательный столбец, который был нужен для фиксации дубликатов, возвращая таблицу к её исходной структуре:

delete from ClientBalance where duplicates_id is null

В общем-то это и всё толковое, что удалось найти. Остальное можно обсуждать только в качестве курьёзов ????:

3.1. Вот например, на https://blog.sibirix.ru/colloquy-task/ автор написал про задачу, которую, по его словам, решает только один из десяти, и ещё один из десяти, если направлять и подсказывать. Если это действительно так, то становится очень грустно – запрос то совсем не сложный – один group by, и один right join:

select Contractor.name, Sums.contr_sum
from Contractor
right join
(select id_contr, sum(summa) as contr_sum
from Operation
group by id_contr) as Sums
on Contractor.id_contr = Sums.id_contr

3.2. На Хабре есть разбор тестового задания по SQL на собеседование в Тиньков. Оно бы и упоминания не заслуживало – задачи совсем простенькие, но даже здесь можно найти, к чему придраться ???? – в решении второй задачи автор переборщил с count и case wheh – их число можно сократить в два раза, если вспомнить про avg:

select start_dttm::date as "date", 
avg(case when dozv_flg=1 then 1 end) as sla
from calls
where start_dttm::date between '2020-10-01' and now()::date and dozv_flg in (1, 0)
group by start_dttm::date

Если у кого-то есть ссылки на интересные задачи по SQL, пишите, плиз, в комментариях.

Буду очень признателен!