SQL Advent. Days 4-7

✨ Решила объединить, потому что в уровне easy (4 и 7 день) нужно написать всего по 3-4 строчки

✨ День 5 потребовал от меня несколько попыток, потому что условие было неполным. Пыталась ориентироваться на подсказки, но они как будто уводили меня еще дальше))

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

coalesce(max(return_date), 'Still resting')

✨ День 6 потребовал разложить горнолыжки по 4 бакетам в зависимости от уровня снега. По-моему, впервые применила функцию ntile:

ntile(4) over (order by total_snowfall_inches)

📍 Полезные ссылки

Адвент календарь

Мои решения

Мысли с матемаркетинга

Посмотрела несколько интересных докладов с матемаркетинга. Больше всего меня заинтересовали про работу с командой, что-то поближе к данным и практические кейсы. Пойдемте смотреть и обсуждать) 🤔

🤩"Эволюция аналитических платформ в XXI веке"

"Если ваша команда дата инженеров ковыряет лейкхаус, то ваш стек отстал года на 4. А если нет - то, возможно, и на все 10"

"Строить лейкхаус-платформу - это все равно, что построить заново СУБД"

В своих заметках еще нашла такую подходящую:

"Когда новая технология накатывает на вас как асфальтоукладчик и вы не у него за рулем, вы становитесь частью дороги"

Еще спикер упомянул про агентскую платформу от Databricks и The Data Engineering Agent от Google

Меня немножко задемотивировало, а вы что думаете? Мы тут все отстающие или стабильные? 😁

🤩"Как найти и как себя вести с сотрудниками, которые не перформят"

Социальная лень - когда один человек понимает, что ответственность размазана по коллективу, и снижает планку => все снижают планку

Токсик продуктивити - много шума, реального результата мало

Когда человек не перформит, есть несколько вопросов к себе на подумать:

🤩Если бы человек ушел - вы бы обрадовались или расстроились?
🤩Сколько денег дают на новую вакансию?
🤩Ок, если 3-4 месяца будем искать?
🤩А если уже на деньги мидла можно найти только джуна?
🤩Что думают доверенные сеньоры про онбординг нового человека?

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

А когда мы отбирали кандидатов на собес или решали по офферу, то руководствовались принципом: сомнение = нет

🤩"Сложные кейсы: как тимлиду грамотно расстаться с неэффективным сотрудником"

Если вы руководитель, то будьте готовы, что вас постоянно записывают. Сюда относятся записи из онлайн встреч, экспорт чата, личные чаты, диктофонные записи. Получается, работа лида - это как разведка, ничего лишнего болтать не надо 🤭

Если вдруг у сотрудника отрубили доступы или перестали ставить задачи - то это необеспечение работой и принуждение к увольнению. А это уже нарушение закона 🤓

🤩"Новый потребитель: как появился сегмент, которого не должно было быть"

Тут рассказывали про покупателей с "неадекватным" поведением в 2022-2024 в отдельных регионах. Они внезапно получили много денег, которых у них никогда не было, и начали покупать то, что никогда не покупали

Еще рассказывали про другой подход к ритейлу - если раньше в магазинах брали много, то сейчас люди делают несколько заказов по 100 рублей (я сама так делаю, хотя чувствую, что неразумно)

🤩"Виртуальная очередь в Яндекс Go: как мы сделали поиск такси предсказуемым в пике"

Этот доклад про оптимизацию, когда много людей уезжает одновременно со стадиона/из театра

Раньше было как:
- первый человек делает заказ
- остальные 100 человек делают заказ на сантиметр ближе к водителю
- первый человек ждет до талого, пока ближайшие не разъедутся

А сейчас так:
- первый человек делает заказ, за него максимальный бонус
- у второго уже бонус меньше
- водитель может взять второй заказ, но с меньшим бонусом
- при перезаказе - в конец очереди (я так тоже люблю делать)

Лайк за доклад, очень все понятно и применимо 👍 А так большинство докладов уже совсем не метчится с моими интересами🥲

Стартанул новый Advent of SQL!!!

⏳ В прошлом году его делал паренек на волонтерских началах. А в этом эту идею продолжают ребята из онлайн-школы по бд на своей платформе

Мне оочень понравился формат - они записывают видосы с заданием! И какой же харизматичный спикер, какой у него красивый акцент 🥰🥰

К видосу прилагается файл с данными. Данных прям много, около 500к строк для одной таблицы. Я как раз на днях перешла на DataGrip и теперь полноценно работаю с ним. Там можно просто тыкнуть SQL Scripts -> Run SQL Script... и выбрать скачанный файлик

У них также есть своя песочница, поднимается за 30 сек со всеми данными

Задание прикольное и приближенное к жизни: надо почистить и нормализовать столбец с новогодними желаниями. Правда, сабмиттить пока никуда не надо, потому что решения - это вторая часть видоса

🤩всего 15 дней
🤩задания открываются в 21:00 по мск

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

Advent of SQL. Days 2-3

📺 Видосы с каждым днем становятся длиннее, вчера было уже почти 12 минут! Задача была на использование ilike - это регистронезависимый like, in, case when. Не думала, что update/delete можно применять, но в этих задачках можно... Главный совет - обязательно перепроверить себя селектом перед изменением данных

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Решила вести словарик с новыми фразами. Так что фразы дня:

🤩rowdy children - шумные дети
🤩interstitial break - промежуточный перерыв
🤩repentant kids - раскаивающиеся дети
🤩yoink - восклицание, когда человек что-то быстро берет

И мне особенно понравились такие комментарии автора:

Я мог бы написать where tag = '' or tag = '', но я не пещерный человек

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

Про конструкцию count(*) FILTER (WHERE ...) в Postgres:

Вы можете услышать, что люди вам не рекомендуют использовать этот вариант, потому если вдруг сменится база данных... Но если вы когда-нибудь поменяете продовую бд, у вас будет много проблем. и CASE vs FILTER - это самая простейшая из них

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Advent of SQL. Day 4

Задачка на приведение данных к единому формату и применение like, ilike, case when, union

В одном из case мне нужно было сделать assigned_task ilike '%snow%' or assigned_task ilike '%shovel%', но это так скучно)) Поэтому я пошла гуглить другие варианты, нашла такие:

where assigned_task ~* 'snow|shovel'

where lower(assigned_task) similar to '%(snow|shovel)%'

where assigned_task ilike any (array['%snow%', '%shovel%'])

where assigned_task ilike any('{%snow%,%shovel%}')

Раньше я пользовалась только ~~ вместо like. Но оказалось, для остальных тоже есть свои символы. not ilike - это вообще что за демон?

like        ~~
ilike       ~~*
not like    !~~
not ilike   !~~*

А также я узнала нагуглила функцию initcap. В одной из таблиц данные уже были в едином формате, их нужно было привести к другому: stage_setup -> Stage Setup. initcap как раз делает то, что нужно: она каждое слово в строке пишет с заглавной буквы:

select initcap(replace(role, '_', ' ')) as role

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Фразы дня

wonky list/space - кривой список/пробел
volunteer roster - список/расписание волонтеров
handwarmer handout - раздача грелок для рук
studly case = pascal case = StudlyCaps - название в формате MyClassName
strong suit - сильная сторона

В конце спикер попросил рассказать о челлендже своим друзьям. И своим врагам тоже 😁

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Advent of SQL. Days 5-6

На пятой задаче было топ-3 через row_number и cte - мы с подобной штукой уже встречались ранее:

row_number() over(partition by user_name order by count(1) desc) as rn

😇Но на 6й день я немного потерялась... Долго не могла понять суть задачи) Вот есть таблица с датами отправки подарков и надо найти даты, на которые забыли записать доставку подарков. Но откуда я должна взять даты, которых нет??

Перечитала summary дважды и посмотрела дважды видос. Решила послушать подсказку - спикер посоветовал использовать "эзотерический" джойн. И почему моя первая мысль была про asof join, я даже пошла гуглить примеры 😁

Потом решила, что не может быть такого задания, когда в предыдущем мы всего лишь применяли оконку🥺 В итоге формула решения такая: generate_series + cross join + left join

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

Advent of SQL. Days 7-8

☕️ На 7й день мы распределяли посетителей по разным какао-станциям на основе их вкусовых предпочтений. Для этого нужно было вспомнить про пересечения и агрегацию массивов

Получилось такое условие джойна, оно возвращает true, если массивы пересекаются:

left join available_cocoa_cars c
  on p.favorite_mixins && c.available_mixins

А так можно красиво собрать все доступные какао-станции по каждому посетителю. Про сортировку даже не вспомнила, что ее можно туда засунуть:

select
  passenger_name,
  array_agg(c.car_id order by c.car_id)
from ...
group by passenger_name

🌄 На 8й день произошло целое открытие для меня. Все это время существовала концепция named window, а я ни разу ее не видела??😱

Мы определяем окно в самом конце запроса типо как cte и можем переиспользовать название окна в расчетах столбцов:

select
  product_id,
  price as product_price,
  lead(price) over w as previous_price,
  row_number() over w as rn
from price_changes
window w as (
  partition by product_id order by effective_timestamp desc
)

Причем этот синтаксис работает просто везде: PostgreSQL, MySQL, ClickHouse, Spark SQL, Oracle, Vertica... Что? 😍 Почему это так удобно, но нигде не используется?

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

Хотите побыть Дедом Морозом?

Новый год - это пора чудес✨ И сегодня вы можете помочь исполнению новогоднего чуда!

У нас в ламоде проводится конкурс мерча. И если вы хотите, чтобы его получила я - то буду благодарна за вашу подписку 🙂🙂

А вообще в канале рассказывается много чего интересного:

🤩статьи на хабре
🤩записи с выступлений
🤩внутренние новости
🤩карточки с полезностями
🤩мемасики
🤩а также проводятся новогодние розыгрыши - сейчас есть активный до 22го декабря🎄

Advent of SQL. Day 9

🗓️ День 9: нужно вытащить из вложенного json нужные поля. Я так и не могу запомнить, как правильно это делается в постгре, поэтому делала по интуиции) Заодно познакомилась с новыми функциями

Как получилось у меня:

select
  order_data['gift']['wrapped']::boolean as gift_wrapped,
  trim(both '"' from order_data['risk']['flag']::text) as risk_flag
from orders

Я сделала в питонячем виде, и это сработало👍 Сначало было просто order_data['risk']['flag'], но мне не понравились лишние кавычки:

risk_flag
----------
"high"
"medium"

Перепроверила типы данных через pg_typeof. В первом случае тип столбца jsonb, а во втором - text:

select
  pg_typeof(order_data['risk']['flag']) as risk_flag,
  pg_typeof(order_data['risk']['flag']::text) as risk_flag2
from orders

Я кастанула, но кавычки не ушли. Пошла гуглить, наверняка есть хитрый trim без substring/replace. И такой есть! Вот эта прикольная конструкция позволяет нам удалять символы с разных сторон, общий синтаксис такой:

TRIM([LEADING | TRAILING | BOTH] trim_character
FROM source_string)

У спикера же получилось более канонично 🙂

select
  (order_data -> 'gift' ->> 'wrapped')::boolean as gift_wrapped,
  order_data -> 'risk' ->> 'flag' as risk_flag
from orders

-> достает по ключу json
->> достает по ключу строку

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Advent of SQL. Day 10

🏰 Вот такую конструкцию я 200% ни разу не встречала и не подозревала о ней! В задаче нужно было перенести данные из таблицы1 в таблицу2 и вывести только перенесенные строчки. Но обязательно в одном запросе - эммм... а как?

Дали подсказку - использовать по максимуму возможности постгри. Что ж, по первой ссылке я нашла тред на stackoverflow

С помощью CTE и RETURNING можно возвращать вставленные/удаленные/обновленные строки и передавать их в следующий запрос. Наверное, можно навешивать несколько cte, строить цепочки таких перемещений и даже ETL из raw в витрину, но звучит как мазохизм 😁

В финальном запросе получилось так:

with moved_rows as (
  delete from deliveries
  where ...
  returning *
)
insert into misdelivered_presents
select
  *,
  date_trunc('second', LOCALTIMESTAMP) as flagged_at,
  'Invalid delivery location' as reason
from moved_rows
returning *

Мы из delete передаем строки в insert, а из insert выводим на экран - это мэджик какой-то 😍

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Фразы дня

I'm feeling stingy - ощущаю себя скупым
inexplicably - необъяснимо
issue an edict - издать указ
meddle in - вмешиваться
muck it up - все испортить
plop - плюхнуться
sleigh is airborne - сани находятся в воздухе
volcano rim - край вулкана

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Собрались как-то DeepSeek, Grok и Gemini...

Вам попадался видос, как несколько иишек играют в мафию? Я наконец посмотрела, и тут прям полноценный разбор, как каждая модель себя ведет. Кто-то более официозно, кто-то льет много воды, кто-то умеет строить хитрые планы, а кто-то не понял правила игры 😁

А на этом канале автор выкладывает игры полностью. Обязательно хочу посмотреть игры целиком и проследить, как у них проявляются персональные характеристики 😮

Advent of SQL. Days 11-14

🏂 Сначала вспомнили про скользящее среднее, его нужно было найти за 7 предыдущих дней. Для этого есть конструкция rows between:

avg(score) over (
  partition by child_id order by behavior_date
  rows between 6 preceding and current row
) as avg_rolling_7_days

Я про нее писала в начале года, но к этому моменту все равно забыла, что можно циферками задавать промежуток)

Дальше задачка на полнотекстовый поиск, такого я не ожидала😁 По итогу познакомилась с несколькими функциями и операторами:

to_tsvector
to_tsquery
ts_rank
setweight
@@

Потом пошли парсить xml и считать количество зарегавшихся пассажиров. Автор использовал cross join lateral, ну а я нашла другой способ, он мне нравится больше:

cardinality(xpath('/manifest/passengers/passenger', manifest_xml)) as passengers_count

В предпоследний день искали пути от одной точки до другой и задействовали recursive cte

Ну вот и почти все! Сегодня будет последнее задание 🥲

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Фразы дня

barrel straight down the hill - нестись вниз по склону
circuitous path - окольный, окружной путь
mind-bending - сложный
schlep - волочиться
winding route - извилистый маршрут

📍 Advent of SQL (с впн)
📍 SQL Advent Calendar (с впн)
📍 Мои решения

@data_engineerette

Подводим итоги!

➡️ Летом этого года нашла такую штуку, как "YearCompass". Это док для подведения итогов года. Там есть вопросы про предыдущий 2025 год - что произошло, какие достижения и уроки. И про наступающий 2026 - про мечты, привычки и планы

Я хочу попробовать и посидеть перед нг. Хотя не очень люблю рефлексировать, многое вообще забываю, а некоторые желания у меня повторяются из года в год с 14 лет🙂

📌 На сайте можно скачать в цифровом или бумажном формате на любом языке. Я же прикрепила цифровую версию, которую можно заполнить на любом устройстве

❄️❄️❄️❄️❄️

Адвенты тоже закончились!🥳 В последний день нужно было объединить 2 таблички, где одна из табличек имела приоритет. Рассказали про конструкцию insert into on conflict do nothing. Я же сделала через union all, добавила новый столбец 1 as ordering и засунула в оконку:

row_number() over(partition by system_id order by dispatched_at desc, ordering)

🏴󠁧󠁢󠁥󠁮󠁧󠁿 Фразы дня

dispatch ledger - журнал отправлений
edifying - поучительный
eerie calm - жуткое спокойствие
it's a wrap - снято/все закончили
network hiccup - сетевой сбой
perfect liftoff - идеальный запуск
pumped - воодушевлен
with any luck - если повезет

✨Advent of SQL (с впн)
✨SQL Advent Calendar (с впн)
✨Мои решения

Ну, а я с этого момента уже в предновогоднем отпуске! Еще подготовлю отдельный пост с итогами года

@data_engineerette