SQL. Решаем ТЕСТОВЫЕ ЗАДАНИЯ из AMAZON и FACEBOOK (подробный разбор)

Сегодня вместе будем решать задачи по SQL с собеседований топовых технических компаний - Amazon и Facebook. Видео будет интересно как тем, кто учит SQL с нуля, так и тем, кто уже работает в IT и планирует проходить собеседования.
Напишите, пожалуйста, если видео было полезным и стоит ли снимать ещё такие разборы?
Полезные ссылки:
www.stratascratch.com/
www.sql-ex.ru/
Таймкоды:
00:00 - Где искать задачи с собеседований IT-компаний?
00:53 - Решаем задачу из Amazon
09:36 - Решаем задачу из Facebook
20:44 - Задача со звездочкой из моей работы. Пишите ваши решения в комментарии!
Меня зовут Андрей - я работаю продуктовым аналитиком в IT стартапе и на этом канале (Noukash) я рассказываю про IT и стартапы. Будут разборы профессий, советы, истории. Подписывайтесь и оставляйте комментарии)
Instagram: / noukash
ТГ Чат для общения: t.me/noukash_it

[@traktin4590]

О, решение задач! То, что нужно. Спасибо за видео!

[@piktogor]

Блиииин… как круто! Всегда думал, что пока не познаешь SQL, Python, статистику, алгоритмы …. нечего даже соваться на должность продуктового аналитика. А тут человек с опытом говорит, что что-то не знать или забывать это нормально. Я прям заряжен после этого видео. Большое спасибо 🙏🏻

[@freedomtv2295]

Так забыть можно, но совсем не знать нельзя, а знать надо очень хорошо

[@piktogor]

@@freedomtv2295 согласен

[@SaltLakeCity_tog]

Не знаю у кого как, мне хватает select * чтобы потом зафильтровать данные в query и запихать их в pivot Но изучить питон хочется, да

[@user-ur2id1ut9k]

Спасибо огромное. Очень интересно . Можешь продолжить такие разборы делать 🙏👋🏿👍👏

[@user-li7hi9em2o]

Андрей, спасибо, смотрю твои видосы с удовольствием. Так доходчиво и по-доброму)

[@lelich7]

Очень крутой формат! Спасибо!

[@user-rq7oi6le9b]

Спасибо за видео, хотелось бы побольше подобных с решениями задач!

[@user-vh1yn1gq5t]

Отличные реккомендации, сайты классные! Спасибо!

[@elmira363]

Только начинаю Спасибо за труд! Лайк и подписка прибыли! )) успеха!

[@georgyozbetelashvili3930]

Крутой канал. Сейчас увидел в ТГ ссылку, сразу подписался! Тоже хочется стать продуктовым аналитиком

[@Noukash]

Спасибо за комментарий!) И удачи в освоении профессии :)

[@ExcelStore]

Спасибо за видео! Доступно и понятно.

[@Noukash]

Спасибо вам за комментарий!)

[@RROxanaSS]

Отличное видео! Спасибо

[@_GreenSnake_]

Спасибо за Ваш труд

[@eb6006]

Даже очень круто, где-то бы поднатаскаться по SQL, и поиску в массиве и сортировках

[@yuripryadko3111]

самое полезное видео на всем канале, ладно, одно из)

[@InvisibleEssence]

Спасибо за выпуск, очень интересно! SQL начала изучать недавно, с удовольствием решаю всякие задачки) А вашу задачку попробовала решить так: если у нас только ежемесячная подписка (то есть отмена возможна только в течение месяца после покупки), то в новой таблице (t1) даты отмены привести к датам как из таблицы (t2) с датами appstore. А потом посмотреть какие данные из таблиц не пересекаются. Примерно так: with sub_1 as (select user_id, to_char(app_store_date, 'YYYY-MM-DD')::date as app_store_date from( SELECT event, created_at, user_id, lag(created_at) over (partition by user_id order by created_at asc)+interval '1 month' as app_store_date FROM public.t1) as sub where event='canceled') select t2.user_id, t2.date, sub_1.app_store_date, sub_1.user_id from public.t2 t2 full outer join sub_1 on t2.user_id=sub_1.user_id and t2.date=sub_1.app_store_date where countevent='canceled' and ((t2.user_id is NULL and t2.date is NULL) or (sub_1.user_id is NULL and sub_1.app_store_date is NULL))

[@VitaliyZlobin]

Это реально крутой совет, помню как устраивался аналитиком после sql-ex и реально я знал только синтаксис SQL, а не его практическое применение. На 9:30 решение не только извращённое, оно ещё и по производительности плохое - 2 раз в таблицу нужно ходить. Но это скорее разрабам полезно знать, аналитикам пофиг)) Благодарю за видео! Новичкам буду рекомендовать этот сайт, после 20-30 задач на sql-ex

[@Noukash]

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

[@user-vb7ms8yx8g]

Даешь, больше SQL!!!

[@amiran_g]

Подсяду на Ваши видосы, полезно

[@vladnn8322]

Очень полезно!

[@yusifibragimov4311]

Спасибо!

[@jimivlob]

системно, подробно, интересно расписаны выпуски лайк+коммент+подписка желаю тебе как можно скорее стать продактом!

[@Noukash]

Спасибо огромное за комментарий и пожелание :)

[@user-lc1ps9rd3m]

Было бы здорово, если бы разбирались задачи с собеседований по статистике

[@Noukash]

Обязательно будет! И по терверу тоже разбор будет

[@user-jn1mz3uh2t]

Рубашка огонь, сам на ex тренируюсь

[@cor4win]

Не знал про stratascratch, спасибо) Во втором задании все-таки решение через case выглядит элегантнее: меньше кода, проще читается, но это субъективно) Сразу о нем подумал, еще до открытия решений) И без форматирования кода сложновато читается в моментах как на 17:18, например. Без отступов всё превращается в кашу, особенно новичкам может быть сложно понять такой код.

[@Noukash]

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

[@TimofeySuyargulov]

Я человек простой. Вижу новое видео от Андрея - ставлю лайк

[@Noukash]

Спасибо Timo

[@johnconnor7338]

Большое спасибо за выпуск! как раз то что я искал последнее время. Я учусь на Системного аналитика на курсах sk**********(знаю знаю это никому не нужно итд. мне так удобно) сейчас пытаюсь проглотить книгу по SQL от Алексанрова вы вроде еще в каком то видео советовали одну книгу , но я забыл в каком. Пытался решать задачки сам, но мне там так на сували, что я обратился к литературе, надеюсь поможет, смотрю ваши видео по нескольку раз очень интересно. По аналитике их к сожалению не много интересных от других тоарищей. У меня фарм. образование , хочу стать аналитиком в фарм индустрии надеюсь потяну, честно, для гуманитария это непросто но профессия жуть как интересная очень расширяет рамки познаваемого. Ваши видео очень помогли с мотивацией, не бросайте! Анализирую что 100к подписчиков у вас будет

[@Noukash]

Спасибо большое за такое комментарий! И удачи с обучением. В будущем обязательно будет больше видео про аналитику и разбор основных понятий/инструментов и задач. Рад, что видео полезны :)

[@johnconnor7338]

@@Noukash было бы круто, я буду очень ждать

[@user-ok8ju6zb6r]

Самая простая функция по датам DATEDIFF, почему вы ее не используете, вычитание это ненадежно Спасибо за разбор!

[@khukhua]

Автор сказал что она не работает в этом диалект где тренажёр

[@rotvx]

не учитываешь версии MS SQL... приходиться поддерживать старые верси в которых нет WITH, OVER etc.... поэтому есть только JOIN-s

[@yulia23714]

Кстати, не во всех диалектах можно писать Group by 1, 2. В кликхаусе, например, нельзя, в вертике можно

[@Noukash]

Спасибо за комментарий! Не знал такой особенности

[@user-jn1mz3uh2t]

Diff, sub_date, date_sub?

[@user-mp3hq9xk3u]

Как я понимаю на StrataScratch уже нельзя проверять ответы без подписки?

[@alexanderpustota4206]

Последняя задача непонятна: "Хорошо ли, для всех ли пользователей приходят отмены". Что значит хорошо, а что значит плохо? Как они могут не приходить и каким образом это можно отловить?

[@ArtemKuznetsovTV]

На какие позиции фб/Амазон спрашивает sql? Ни разу такого не встречал

[@Noukash]

На аналитические позиции. Все что связано с data science, product analytics

[@ArtemKuznetsovTV]

@@Noukash понятно, спасибо. Не встречал людей на этих позициях, их кажется мало очень. В соседних командах таких нет.

[@ivani3237]

ну на дата инженера 100% надо знать хорошо sql ..

[@ArtemKuznetsovTV]

@@ivani3237 да, я уже глянул гайдлайн по собеседованимя, действительно спрашивают. Когда проводишь интервью у обычных SDE не особо знаешь, что там у других направлений :-)

[@Krakatoa_27]

Спасибо за видео и новый ресурс! Но за group by 1,2 дизреспект. Крайне неудобная штука для будущего легаси.

[@maximgesler]

Такой сайт для Python есть ?

[@Noukash]

Да, там есть задачи и для python :)

[@maximgesler]

@@Noukash можно показать, задачки для каждого уровня, интересно даже посмотреть какую задачу на junior позицию в собеседованиях

[@user-rl4nw2rt1y]

Канкел😂

[@user-nk1bs5ks6u]

Некоторые пользователи пишут замудреные запросы, которые быстрее отрабатываются. Аналитиков просят писать оптимизированные запросы?)))

[@michaelketov]

Автор конечно странный, оценивать запросы с точки зрения "красивости". Как по мне единственный правильный критерий - скорость выполнения. Толку от красивости , если он выполняется в разы дольше "некрасивого"

[@ivani3237]

ну второй критерий - читабельность, при прочих равных надо писать так чтобы было понятно

[@sergey-chigarev]

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

[@user-nc9sl2wv5r]

На первый взгляд именно так и кажется, но... как всегда есть нюансы. Во первых, синтаксис оконных функций только на первый взгляд кажется непонятным. После того как вы нагуглите где нибудь картинку схематично показывающую смысл определения окна поверх вашей выборки-все резко прояснится. Во вторых, использование концепции окна позволяет избежать избыточной выборки данных. Когда вы делаете JOINT-ы даже той самой таблицы на себя же саму - сервер по факту извлекает и повторно обрабатывает данные, шарит по ключам, индексам, выполняет сравнения и сопоставления условий в ON секции. Попробуйте выполнить EXPLAIN для вашего сложносоставного запроса и посмотреть что по факту делает сервер чтобы его обработать. При использовании оконных функций - данные извлекаются только один раз, при первом считывании таблицы, а оконные функции манипулируют все тем же набором данных, просто "тасуют" его так, как вы определите в условиях разбивки на фреймы и правилах упорядочивания внутри окна. Т.е. вы избавляете сервер от лишних операций выборки и соответственно экономите ресурсы. Ну и еще, эти функции позволяют например легко вычислить разницу между какими то значениями, переместив логику предварительной обработки данных на сторону сервера, это может быть критично для систем где высока стоимость пересылки данных, или их последующей обработки на стороне приложения(например написанного на "тяжелом" скриптовом языке который работает медленно, и неэффективно расходует память для хранения переменных). В этих случаях обычно становится выгодно максимально предварительно обработать данные уже на этапе SQL запроса, чтобы получить компактную "выжимку" именно тех данных которые нужны вашему приложению, а не извлекать вагон и тележку значений, гонять их по сети, а потом их обрабатывать в приложении порождая кучу объектов и тратя под это память.

[@SuperPrimus]

Кстати, join саму с собой - это чуть ли не единственный вариант решить такую задачу запросом 1С, где в принципе нет оконных функций.

[@user-ec5ut2fi4n]

Ресурс интересный и полезный, но есть замечание по первой задаче от амазона, запрос провалился, но решение не верное. В концовке должно быть select distinct user_id from preptable where next_purchase_at-created_at