это базовый минимум, чтобы с вами разговаривать начали. для проверки реальных знаний, задачи совсем другие. плюс, после таких решений можно ожидать массу вопросов касающихся оптимизации. приведенные решения плюют на производительность, соответственно в реальных условиях лид за такое отрубит руку. джойн таблиц до группировки, которая возвращает буквально одну строку? а если заказов 100 миллионов или сто миллиардов, а таких разработчиков пара тысяч? это же никаких кластеров не хватит. ну можно надеяться на оптимизатор, он скорее всего сделает все как надо и сэкономит где можно, но опять же, без проверки плана запроса такой код отдавать нельзя.
В третьем примере для большей корректности (еслим мы не хотим полагаться на уникальность имени) нужно оставлять группировку по id покупателя и джойнить снаружи с таблицей покупателей.
Всё-таки Limit не всегда самое лучшее решение, лучше наверное показать через вложенный запрос, а то начнут везде его использовать когда можно или нельзя) Особенно если данных много и тебе нужно найти допустим строки с MAX значением, а их может быть 100. Но тут соглашусь что лимит идеально заходит )
@@vaccino3668 Зачем? В данном случае пример тривиальный. Если задача решается простыми запросами - то нужно ее решать простыми запросами, а не пардон из**аться показывая что ты "умеешь оконные функции", так как самое простое решение - обычно и самое быстрое. По поводу подзапросов - это удобный инструмент, но в данном случае он тоже избыточен, а в практических задачах - их(поздапросы, особенно вложенные) нужно использовать крайне аккуратно, так как неоптимально сконструированный сложный запрос по большим таблицам может иметь очень высокую стоимость(в смысле ресурсов на его выполнение) и порой существенно замедлить работу вашего приложения. Если есть сомнения в эффективности решения - можно использовать EXPLAIN и изучить что на самом деле делает сервер БД при выполнении ваших конструкций(к каким таблицам и каким образом обращается, какие индексы при этом использует, сколько строк извлекается из каждой таблицы и сколько суммарно строк было извлечено чтобы обслужить ваш запрос). Так же, некоторые реализации СУБД дают рекомендации в случае обнаружения неоптимальных конструкций(например можноу видеть рекомендацию добавить в таблицу индекс на определенную колонку).
Интересное видео! Кстати, ты используешь в своей работе питон? Пандас, нп, и так далее. Если да, то можешь рассказать про этот язык программирования в аналитике? Спасибо 👍
По первой задаче. Все эти решения как бы не совсем верны. Точнее они верны только на определенном наборе данных. Например ваше решение, насколько я понял, не учитывает что посылать можно несколько раз. Т.е. 1 числа послал - не принял, 2 - не принял, 3его - принял. Но по вашему запросу все 3 числа будут 100% принятие. (т.е. при таблице: send, send, accepted результат будет сомнителен)
Спасибо за видео. Познавательно. Только лучше сразу уточнять, что Вы выполняете в PostgreSQL и кстати в MySQL если указать только один столбец для группировки ошибки не будет (вот такой вот забавный MySQL)
В последнем случае поддерживаю про Left Join - всяко может быть, и по скорости лучше, т.к. связывание будет после получения результатов. Но во 2й задаче - сомнительно использовать union all из таблиц, в которых будет миллионы строк 😁 Давайте больше головоломок - типа найти не более 5 заказов клиентов которые заказывали такой-то товар ...
можно на джойнах сделать :) чего там, три поля сложить. но результат будет сильно похуже по производительности наверно. на самом деле оптимизация решения будет сильно зависеть от конфигурации сервера и наличия индексов на конкретных полях. вообще, UNION ALL работает очень быстро, в отличие от UNION, он же почти ничего не делает, кроме прямого чтения. другой вопрос, что тут экономить, время или ресурсы. сейчас уже не помню, да и разные движки наверно по разному отрабатывают, но возможно в каких то случаях данные будут читаться параллельно, а в каких то последовательно. насчет иннер джойн тоже можно подискутировать. иннер работает как фильтр, и если он отбрасывает много данных, то может оказаться быстрее лефт, опять же, при определенном наборе условий, типа наличия индексов на связываемых полях, длине записей и т.д. здесь я бы скорее отталкивался от бизнес-требований. нужна ли на выходе вся левая таблица или только та часть, по которой есть все данные в правой. но это мой личный закидон, я люблю фильтрацию по возможности запихивать в джойны. дальше оптимизатор сам решит, как выгоднее ее провести на самом деле
В году 365 дней, поэтому даже за 10 лет существования проекта - там в каждой таблице будет максимум по ~36 тыс. строк. Даже если есть 10 таблиц - это максимум треть миллиона строк.
В 3 заданий наверное точнее было бы написать order_date>='2020.02.01'. Не уверен, но вроде в задании эти дни указаны включительно. А если это так то есть минимальны шанс вывести не правильный результат
В задаче написано between, поэтому если подразумевается between в смысле синтаксиса SQL - то да, именно так, так как он(оператор) включает границы диапазона значений в выборку. Если же значение подразумевается в "человеческом" смысле - то тогда не факт, что значения границы выборки входят в искомый диапазон. По хорошему - в нормальном ТЗ такие вещи следует точно конкретизировать, а если есть сомнения - то лучше уточнить у заказчика такие моменты перед тем как собственно писать код и сдавать работу, так как именно из за вот таких "маленьких неоднозначностей в ТЗ" порой случаются серьезные и дорогостоящие ошибки и факапы.
для дата девов это заявка на профнепригодность. если от этого не отучили на этапе обучения, с человеком явно что то не то и девом ему лучше не быть. пусть идет в аналитики например, может быть там найдет себя.
Добрый день. Задача №1. А почему нельзя сделать просто группировку по полям отправитель и получатель, а по дате и действию расчет минимального значения? Мы ж в рамках этой задачи должны решить, не думая на перспективу? Будет работать однозначно быстрее, чем использовать соединения таблиц. А потом уже дело техники посчитать количество записей принятых через CASE и количество общее. Или нет?
Андрей, спасибо за разбор. Как раз сейчас прохожу sql. А поясни, плиз, если не сложно, почему для отбора самого большого заказа, ты сортируешь не просто по total_order_cost, а ставишь аггр функцию SUM(total_order_cost)? Там же просто по значениям в колонке это делается - без суммирования.
Ответил в одном из соседних комментариев. В ORDER BY можно использовать или alias или выражение (например CASE WHEN). Использовать алиас с выражением не получится. Поэтому я не использую алиасы с ORDER BY чтобы не запутаться.
вообще то в group by и order by можно вписывать номер колонки. Это религиозно неправильно но mysql и pgsql поддерживают эту фичу уже лет 20 как. что то задачи какие то слишком простые.
@@ВикторРябов-ц3з У меня есть видео про задачки из SQL еще одно - и там я как раз в Group by использую 1,2. А задачки да - это для новичков - используются на этапе скрининга первичного чтобы понять он вообще туда пришел или нет. Есть более сложные задачи, которые также буду разбирать
Привет. Конечно. Нужны примеры задач не только sql, но и питон. Я как начинающий аналитик... Пока не понимаю тот, минимальный уровень навыков, с которым могу ворваться в эту сферу.
Спасибо за видео, было интересно, хоть SQL использую ограниченно, но имеется опыт преподавания практических занятий в университете. Финальное решение последней задачи кажется неправильным, ведь выполняется группировка по first_name, который не уникален (такого условия вроде бы не было).
Здравствуйте, а можете пояснить -а почему в первой задаче у нас в результирующей таблице всего две строчки? Там же дат гораздо больше (5-6). Или я что-то упустил. Заранее спасибо.
А теперь реши задачу 2 и 3, только вместо максимального значения нужно выбрать, скажем, второе по величине. Ну это так, чтобы на Limit 1 не подсаживались.
Андрей, подскажите, а если в 3 задаче не делать джоин, а просто брать данные из двух табл . Выводить имя из одной, остальное - из другой, при условии, что их каст_айди - равны. Чем отличаются варианты? И почему лучше использовать одно, а не другое?
а как вы будете брать данные из второй таблицы? отдельным запросом? все равно надо будет сопоставлять по ИД. но логика будет разорвана на два блока и это уже не СКЛ
Задача №1: Разве datetime не подразумевает что там могут быть и данные с временной меткой? Если так, то решение не работает. Задача №3: 1) Зачем учить людей плохому и группировать по имени? Это более ресурсоемко + может привести к багам. 2) Поидее движок должен такое нормально отсеять, но я в этом не уверен на 100%(нужно смотреть explain и т.д.): Есть ли смысл джойнить таблицу юзеров со всеми ордерами, если нам нужен только один ? На больших данных такой джойн может сказаться на производительности(а может и не сказаться, нужно вникать в оптимизацию конкретного sql движка)
Это все конечно круто, но в реальности count, sum и тд будут дико нагружать бд, если там большие объемы данных. Мне кажется, стоит это упомянуть и возможно рассказать про хорошую практику (создание отдельных таблиц под подобную статистику)
@@user-ch76tcye4vvuu8 ваш ответ не отменяет начальный запрос. как раз по собранным данным чаще всего и нужна агрегация. я для сбора скорее надо обсуждать insert, update, delete.
угу, и поддерживать их актуальность. но перед этим неплохо бы оценить, что можно выкрутить на индексах. они хорошо помогают в запросах с агрегатами, если грамотно наложены. ну и если двигло позволяет, то можно посмотреть в сторону column store хранения данных, тогда агрегаты просто взлетают как ракета.
ну как бы это решается индексированием и партицированием таблиц, а также использованием фильтров в запросе. Никто отдельные таблицы под статистику не заводит, это бред.
в первой задаче совсем опускается случай, когда первый запрос улетел, но его не приняли, а, например, при второй попытке запроса - приняли. В текущем решении первый запрос засчитается, как принятый, но это неверно. во второй задаче - максимальное потребление может выпадать на несколько дат, лимит выведет только одну из них. в третьей задаче использовал limit - во-первых, не во всех СУБД такой оператор есть, во-вторых, может быть больше 1 пользователя с такой же суммой заказа, в-третьих, в конце группировал по first_name, а надо по айдишнику пользователя, ибо опять же, их может быть больше одного. Короче я бы ни одно из решений не зачел на собесе. Кстати и про платформу с задачами это многое говорит - они не проверяют крайние случаи, так что лучше такой платформой не пользоваться. Приведенные решения в целом неправильные!
В ORDER BY можно использовать или alias или выражение (например CASE WHEN). Использовать алиас с выражением не получится. Поэтому я не использую алиасы с ORDER BY чтобы не запутаться.
Во второй задаче: опять же зависимость от данных. Т.е. если будет 2 даты с одним максимальным количеством, то какая из них нужно вывести (по хорошему обе, ну или уточнить например где дата больше/меньше)?
Заметка: 3 задаче, у 2 или больших покупателей одинаковые суммы заказа (к примеру макс 275, не только у Jhon но и у Jill) то мы теряем остальных покупателей, кроме первого
16:50. Никогда не делайте так, как сделал этот "ментор". За подобное решение - дисквалифицировал бы соискателя нафиг. Причем сначала правильно же начал делать, а потом сделал фигню: Представьте на минутку, что есть несколько покупателей с одинаковыми первыми именами. Представили? Правильно, будет ошибка, так как разные покупатели с одинаковыми ферстнеймами будут сгруппированы в одну группу. Поэтому в данном случае - правильно как раз таки группировать по кастомер ID так как скорее всего, это внешний ключ охваченный ссылочной целостностью, ну и как минимум - даже если в таблице кастмоеров нету такого ИД - мы понимаем что это точно заказы одного и того же кастомера. А джоинт в этом случае лучше должен быть левый, а то мало ли чего, это как раз таки мысль безусловно здравая(хотя в нормальной БД - такие вещи связывают внешними ключами).
было бы, но без конкретного сервера это скорее на "поговорить". как прочитать план запроса, на что обратить внимание, где есть потенциальные узкие места и т.п.
Прикольно. В tsql завершающая 2-й пример инструкция "limit 1" пишется как "top 1" сразу после select. Неужели такие простые задачи. Нее. Скорее это какой то предварительный этап отсева.
Это на trainee аналитика что ли задачи? Примитивные вещи, единственный ньюанс (актуальный для меня), придется поломать голову как это делается на диалекте использованных БД (все же ClickHouse попривычнее).
Плохое решение для первой задачи, так как идёт два прохода по таблице. Лучше бы было: select date, sum(action)/count(*) from (select user_id_sender, user_id_reciver, min(date) date, min(case when action = 'accepted' then 1 else 0 end) as action from fb_friend_request group by user_id_sender, user_id_reciver) group by date order by date Я ораклист, поэтому приведение типов додумайте сами. :) Плохое решение для третьей задачи: 1. Имена тянутся для каждой записи, даже тех, которые нам не нужны. Лишняя нагрузка на вторую таблицу. Надо считать топа, а потом по id подтягивать его имя 2. Если есть тёзки, но разные покупатели, то они будут просуммированы.
У тебя не оптимальное решение с точки зрения перформанса. Зачем ты джойниш таблицу, для все строк а потом эти строки пропускаешь. Проще вытащить одну запись в подзапрос, и уже потом заджойнить. Так будет работать быстрей
А если еще и дальше углубиться. То в последнем примере. Зря ты на дату забил. А что если там данных будет за 20 лет? Ты и для них будешь проводить группировки, джойны и все вычисления? Запрос получится тяжёлым. Вот что нужно сделать. 1. Отфильтровать ордера по дате. 2. Найти максимальное значение с ценой. И сделать это подзапросом. 3. Заджойнить таблицу юзеров 4. И вывести результат
Согласен с комментариями! Я вообще не оптимизировал запросы с точки зрения перформанса и не учитывал крайние случаи/ошибки в данных. Для меня было важно показать основной принцип вычислений. Но все комментарии 100% по делу
@@rubinroterstern зачем пересылать, посмотри внимательно на решение других пользователей в этом же видео, там есть решение без двойнов. Джойны вообще в 90% случаях не нужны
так, попробую писать решения до твоего варианта)) 1) SELECT t1.date, COUNT(t2.action) / COUNT(t1.action) percentage_acceptance FROM fb_friend_requests t1 LEFT JOIN fb_friend_requests t2 ON t1.user_id_sender = t2.user_id_sender AND t1.user_id_receiver = t2.user_id_receiver AND t1.action != t2.action WHERE t1.action = 'sent' GROUP BY t1.date upd: Спасибо за лайфхак с 1.0. Раньше игрался с функциями 2) WITH cte AS ( SELECT date, SUM(consumption) sumConsumption FROM( SELECT * FROM fb_eu_energy UNION ALL SELECT * FROM fb_asia_energy UNION ALL SELECT * FROM fb_na_energy ) t1 GROUP BY date ) SELECT * FROM cte WHERE sumConsumption = ( SELECT MAX(sumConsumption) FROM cte ) upd: Важно не через лимит идти, мы же так можем данные потерять 3) WITH cte AS ( SELECT order_date, t1.id, first_name, COUNT(*) countOfOrders, SUM(total_order_cost) total_order_cost FROM customers t1 JOIN orders t2 ON t1.id = t2.cust_id WHERE order_date BETWEEN '2019-02-01' AND '2019-05-01' GROUP BY order_date, t1.id, first_name ) SELECT first_name, total_order_cost, order_date FROM cte t1 WHERE countOfOrders = ( SELECT MAX(countOfOrders) FROM cte ) upd: Тут тоже самое, можем данные потерять, опасно через лимит решать такие задачи
хотим больше SQL!
Будет больше обязательно!
Спасибо за примеры! Так легко и непринужденно получилось решить задачи, как 2+2 😁
Спасибо Андрей 😉Стало понятно, какого уровня бывают задачи. Очень было интересно и поучительно.
это базовый минимум, чтобы с вами разговаривать начали. для проверки реальных знаний, задачи совсем другие. плюс, после таких решений можно ожидать массу вопросов касающихся оптимизации. приведенные решения плюют на производительность, соответственно в реальных условиях лид за такое отрубит руку. джойн таблиц до группировки, которая возвращает буквально одну строку? а если заказов 100 миллионов или сто миллиардов, а таких разработчиков пара тысяч? это же никаких кластеров не хватит. ну можно надеяться на оптимизатор, он скорее всего сделает все как надо и сэкономит где можно, но опять же, без проверки плана запроса такой код отдавать нельзя.
Формат супер! Ждем больше таких видео!
Однозначно нужно больше таких видео. 👍🏻🙏🏻
Супер, спасибо, я немного выпала на пол годика с твоего канала, но очень рада что ты запустил такую рубрику по желанию подписчиков))
Добро пожаловать обратно на канал :)
В третьем примере для большей корректности (еслим мы не хотим полагаться на уникальность имени) нужно оставлять группировку по id покупателя и джойнить снаружи с таблицей покупателей.
Не то что не хотим, а не должны. Одинаковые имена - это не исключение какое то, а вполне себе реальная реальность.
Интересно рассказываешь, логику и последовательность решения наглядно, спасибо за материал!
Спасибо! Будут ещё такие видео с решением задач :)
Большое спасибо за труд!
Было бы здорово, если бы вы сняли ролик по SQL для новичков:)
У меня уже есть пару роликов на канале про SQL, но сниму ещё
Учитывая, что я trainee data analyst, ролик сложноватый) Но есть куда расти!) Спасибо за информацию!
Рад, что ролик полезный!
После первого месяца на работе покажется легковатым, так что желаю вам скорее к этому прийти!)
Всё-таки Limit не всегда самое лучшее решение, лучше наверное показать через вложенный запрос, а то начнут везде его использовать когда можно или нельзя)
Особенно если данных много и тебе нужно найти допустим строки с MAX значением, а их может быть 100.
Но тут соглашусь что лимит идеально заходит )
Полностью согласен! Бывает много случаев, когда он не заходит - например, когда множество строк с одинаковым значением :)
Подобные фокусы делаются через оконные функции...
@@vaccino3668 Зачем? В данном случае пример тривиальный. Если задача решается простыми запросами - то нужно ее решать простыми запросами, а не пардон из**аться показывая что ты "умеешь оконные функции", так как самое простое решение - обычно и самое быстрое. По поводу подзапросов - это удобный инструмент, но в данном случае он тоже избыточен, а в практических задачах - их(поздапросы, особенно вложенные) нужно использовать крайне аккуратно, так как неоптимально сконструированный сложный запрос по большим таблицам может иметь очень высокую стоимость(в смысле ресурсов на его выполнение) и порой существенно замедлить работу вашего приложения. Если есть сомнения в эффективности решения - можно использовать EXPLAIN и изучить что на самом деле делает сервер БД при выполнении ваших конструкций(к каким таблицам и каким образом обращается, какие индексы при этом использует, сколько строк извлекается из каждой таблицы и сколько суммарно строк было извлечено чтобы обслужить ваш запрос). Так же, некоторые реализации СУБД дают рекомендации в случае обнаружения неоптимальных конструкций(например можноу видеть рекомендацию добавить в таблицу индекс на определенную колонку).
спасибо за разборы!
Спасибо за видео
как по мне, задачка фейсбука сложнее, спасибо, очень интересно и хорошо обьясняете, хотелось бы еще таких задачек
Ура. Частота видео радует
Да - стабильно 2 в неделю
Спасибо за видео. Супер контент, побольше практики
Не сильно сложные задачки, это радует)
Хоть я ещё и не начал изучать SQL но после этого видео будто пришло прозрение как понимать логику кода
Ура! Этого я и хотел достичь :)
Интересное видео! Кстати, ты используешь в своей работе питон? Пандас, нп, и так далее. Если да, то можешь рассказать про этот язык программирования в аналитике? Спасибо 👍
Обязательно расскажу больше про практическую сторону питона
Спасибо за отличный контент!
Спасибо за комментарий!
Привет, Андрей! Вопрос: сайт, на котором вы решаете задачи в видео, это некий официальный портал от Мета или что-то другое?
Спасибо большое! А есть ли уроки на когортный анализ?
это просто сюр! назовите видео как положено: "разбор SQL задач экстремистских организаций"
По первой задаче. Все эти решения как бы не совсем верны. Точнее они верны только на определенном наборе данных. Например ваше решение, насколько я понял, не учитывает что посылать можно несколько раз. Т.е. 1 числа послал - не принял, 2 - не принял, 3его - принял. Но по вашему запросу все 3 числа будут 100% принятие. (т.е. при таблице: send, send, accepted результат будет сомнителен)
Спасибо за видео. Познавательно. Только лучше сразу уточнять, что Вы выполняете в PostgreSQL и кстати в MySQL если указать только один столбец для группировки ошибки не будет (вот такой вот забавный MySQL)
Да! Хорошее замечание. Буду уточнять :)
В последнем случае поддерживаю про Left Join - всяко может быть, и по скорости лучше, т.к. связывание будет после получения результатов.
Но во 2й задаче - сомнительно использовать union all из таблиц, в которых будет миллионы строк 😁
Давайте больше головоломок - типа найти не более 5 заказов клиентов которые заказывали такой-то товар ...
можно на джойнах сделать :) чего там, три поля сложить. но результат будет сильно похуже по производительности наверно. на самом деле оптимизация решения будет сильно зависеть от конфигурации сервера и наличия индексов на конкретных полях. вообще, UNION ALL работает очень быстро, в отличие от UNION, он же почти ничего не делает, кроме прямого чтения. другой вопрос, что тут экономить, время или ресурсы. сейчас уже не помню, да и разные движки наверно по разному отрабатывают, но возможно в каких то случаях данные будут читаться параллельно, а в каких то последовательно.
насчет иннер джойн тоже можно подискутировать. иннер работает как фильтр, и если он отбрасывает много данных, то может оказаться быстрее лефт, опять же, при определенном наборе условий, типа наличия индексов на связываемых полях, длине записей и т.д. здесь я бы скорее отталкивался от бизнес-требований. нужна ли на выходе вся левая таблица или только та часть, по которой есть все данные в правой. но это мой личный закидон, я люблю фильтрацию по возможности запихивать в джойны. дальше оптимизатор сам решит, как выгоднее ее провести на самом деле
В году 365 дней, поэтому даже за 10 лет существования проекта - там в каждой таблице будет максимум по ~36 тыс. строк. Даже если есть 10 таблиц - это максимум треть миллиона строк.
очень круто! хочу ещё больше sql
Будет!
В 3 заданий наверное точнее было бы написать order_date>='2020.02.01'. Не уверен, но вроде в задании эти дни указаны включительно. А если это так то есть минимальны шанс вывести не правильный результат
В задаче написано between, поэтому если подразумевается between в смысле синтаксиса SQL - то да, именно так, так как он(оператор) включает границы диапазона значений в выборку. Если же значение подразумевается в "человеческом" смысле - то тогда не факт, что значения границы выборки входят в искомый диапазон. По хорошему - в нормальном ТЗ такие вещи следует точно конкретизировать, а если есть сомнения - то лучше уточнить у заказчика такие моменты перед тем как собственно писать код и сдавать работу, так как именно из за вот таких "маленьких неоднозначностей в ТЗ" порой случаются серьезные и дорогостоящие ошибки и факапы.
обожаю твои разборы задач
Ура! Это очень радует :) буду снимать ещё
@@Noukash мне ответил сам Андрей😨😱
@@erboljunusmamatov32 Я много кому отвечаю)) Стараюсь по возможности ответить на большую часть, но не всегда успеваю ответить на все :)
для аналитиков еще может и пойдет (им можно и по именам группировать) , но для data девелоперов это легкий уровень
для дата девов это заявка на профнепригодность. если от этого не отучили на этапе обучения, с человеком явно что то не то и девом ему лучше не быть. пусть идет в аналитики например, может быть там найдет себя.
Спасибо. А где можно разместить такие задачи для своих кандидатов, так, чтобы они могли писать SQL и видеть результаты?
А разве в третьей задаче в условии WHERE не должна стоять функция between? в отличии от > и = и
Да, конечно between топ
Круто. Можно ссылки на задачи?
Ты ТОП
Спасибо!
Здравствуйте, Андрей, спасибо за видео. Подскажите, пожалуйста, хороший курс по базам данных. С основами я знаком, хотелось бы углубить знания
spasibo, ti skazal chto samiy posledniy eto samiy trudniy. No dlya menya perviy bil samim trudnom. ))))
Ого! Интересно) не ожидал, что он будет труднее 3! Принял
@@Noukash a mojna sprosit voprosi? da u menya zadachi kotorie nu moqu reshat ))) Tolka 2 budet
В третьем примере >= и
Спасибо
Добрый день. Задача №1. А почему нельзя сделать просто группировку по полям отправитель и получатель, а по дате и действию расчет минимального значения? Мы ж в рамках этой задачи должны решить, не думая на перспективу? Будет работать однозначно быстрее, чем использовать соединения таблиц. А потом уже дело техники посчитать количество записей принятых через CASE и количество общее. Или нет?
Андрей, спасибо за разбор. Как раз сейчас прохожу sql. А поясни, плиз, если не сложно, почему для отбора самого большого заказа, ты сортируешь не просто по total_order_cost, а ставишь аггр функцию SUM(total_order_cost)? Там же просто по значениям в колонке это делается - без суммирования.
Ответил в одном из соседних комментариев. В ORDER BY можно использовать или alias или выражение (например CASE WHEN). Использовать алиас с выражением не получится. Поэтому я не использую алиасы с ORDER BY чтобы не запутаться.
вообще то в group by и order by можно вписывать номер колонки.
Это религиозно неправильно но mysql и pgsql поддерживают эту фичу уже лет 20 как.
что то задачи какие то слишком простые.
@@ВикторРябов-ц3з У меня есть видео про задачки из SQL еще одно - и там я как раз в Group by использую 1,2. А задачки да - это для новичков - используются на этапе скрининга первичного чтобы понять он вообще туда пришел или нет. Есть более сложные задачи, которые также буду разбирать
Хотелось бы уточнить на какую вакансию сии "задачи"? Судя по уровню "сложности" на уборщицу протирающую столы разрабов.
В последнем примере группировка по first_name в действительность дичь… я понимаю что они написали что имена уникальны, но в реальных условиях…
ага, бага. Даже для "упрощения" руку сбивать нельзя.
Возможно уникальность имен это этакая ловушка-маркер для неправильных решений
Даты должны быть включительно или нет? Т.е. 2019-02-01 должна попадать в диапазон в запросе или нет?
Привет. Конечно. Нужны примеры задач не только sql, но и питон.
Я как начинающий аналитик... Пока не понимаю тот, минимальный уровень навыков, с которым могу ворваться в эту сферу.
Первый лойсик загрузил 😁❤️
Ура! Спасибо :)
Спасибо за видео, было интересно, хоть SQL использую ограниченно, но имеется опыт преподавания практических занятий в университете.
Финальное решение последней задачи кажется неправильным, ведь выполняется группировка по first_name, который не уникален (такого условия вроде бы не было).
Такое условие было прописано в задаче и я его проговорил в видео. Но в боевых условиях конечно так делать не стоит
а на каком сайте вы пишете запрос в этом видео?
Во втором примере наверное outer apply отработал качественнее
Здравствуйте, а можете пояснить -а почему в первой задаче у нас в результирующей таблице всего две строчки? Там же дат гораздо больше (5-6). Или я что-то упустил. Заранее спасибо.
Только 2 даты в которые были посланы запросы - атрибуцируем к ним
Подскажите пожалуйста, как называется сайт на котором вы данные задачи решаете?
Stratascratch
А теперь реши задачу 2 и 3, только вместо максимального значения нужно выбрать, скажем, второе по величине. Ну это так, чтобы на Limit 1 не подсаживались.
Так можно ведь оставить Limit 1, но дописать offset 2 в вашем случае?
Извините, если предложение тупое. Только учусь
А я бы посмотрел курс по SQL. Хочу наконец нормально разобраться в оконных функциях и вложенных запросах
О! Я бы с удовольствием такой сделал бы ближе к концу года
читайте Ицика Бен-Ганна и будет вам счастье
Андрей, подскажите, а если в 3 задаче не делать джоин, а просто брать данные из двух табл . Выводить имя из одной, остальное - из другой, при условии, что их каст_айди - равны. Чем отличаются варианты? И почему лучше использовать одно, а не другое?
а как вы будете брать данные из второй таблицы? отдельным запросом? все равно надо будет сопоставлять по ИД. но логика будет разорвана на два блока и это уже не СКЛ
@@mkalalaka select a.name , b.adress from tableNames a, tableAdress b where a.id=b.id (К примеру, я уже не помню какие там данные)
Задача №1: Разве datetime не подразумевает что там могут быть и данные с временной меткой? Если так, то решение не работает.
Задача №3:
1) Зачем учить людей плохому и группировать по имени? Это более ресурсоемко + может привести к багам.
2) Поидее движок должен такое нормально отсеять, но я в этом не уверен на 100%(нужно смотреть explain и т.д.): Есть ли смысл джойнить таблицу юзеров со всеми ордерами, если нам нужен только один ? На больших данных такой джойн может сказаться на производительности(а может и не сказаться, нужно вникать в оптимизацию конкретного sql движка)
Особенно доставляет left join вместо inner join.
Это все конечно круто, но в реальности count, sum и тд будут дико нагружать бд, если там большие объемы данных. Мне кажется, стоит это упомянуть и возможно рассказать про хорошую практику (создание отдельных таблиц под подобную статистику)
Тут же задачи про то, как получить данные из уже собранных, а ни как их собирать.
@@user-ch76tcye4vvuu8 ваш ответ не отменяет начальный запрос. как раз по собранным данным чаще всего и нужна агрегация. я для сбора скорее надо обсуждать insert, update, delete.
угу, и поддерживать их актуальность. но перед этим неплохо бы оценить, что можно выкрутить на индексах. они хорошо помогают в запросах с агрегатами, если грамотно наложены. ну и если двигло позволяет, то можно посмотреть в сторону column store хранения данных, тогда агрегаты просто взлетают как ракета.
@@mkalalaka Тема видео решение задач на SQL. В задачах нужно писать запрос на получение данных, а ни проектировать сбор данных.
ну как бы это решается индексированием и партицированием таблиц, а также использованием фильтров в запросе. Никто отдельные таблицы под статистику не заводит, это бред.
Я бы сказал ничего не поня но, но тк я не программист, но было интересно
в первой задаче совсем опускается случай, когда первый запрос улетел, но его не приняли, а, например, при второй попытке запроса - приняли. В текущем решении первый запрос засчитается, как принятый, но это неверно.
во второй задаче - максимальное потребление может выпадать на несколько дат, лимит выведет только одну из них.
в третьей задаче использовал limit - во-первых, не во всех СУБД такой оператор есть, во-вторых, может быть больше 1 пользователя с такой же суммой заказа, в-третьих, в конце группировал по first_name, а надо по айдишнику пользователя, ибо опять же, их может быть больше одного.
Короче я бы ни одно из решений не зачел на собесе. Кстати и про платформу с задачами это многое говорит - они не проверяют крайние случаи, так что лучше такой платформой не пользоваться. Приведенные решения в целом неправильные!
как называется сервис в котором ты эти квери пишешь?
Stratascratch
@@Noukash спасибо
А почему в order by нельзя было поставить алиас?
В ORDER BY можно использовать или alias или выражение (например CASE WHEN). Использовать алиас с выражением не получится. Поэтому я не использую алиасы с ORDER BY чтобы не запутаться.
Во второй задаче: опять же зависимость от данных. Т.е. если будет 2 даты с одним максимальным количеством, то какая из них нужно вывести (по хорошему обе, ну или уточнить например где дата больше/меньше)?
Если ты не джун, от тебя ожидаются вопросы подобного рода ;)
А сколько у тебя уходит времени на подготовку одного видео?)
Заметка: 3 задаче, у 2 или больших покупателей одинаковые суммы заказа (к примеру макс 275, не только у Jhon но и у Jill) то мы теряем остальных покупателей, кроме первого
Для этого как раз нужно более универсальное решение через подзапрос с оконкой DENSE_RANK()
👍
безолаберное решение второй задачи. В случае двух дней с одинаково большим потреблением енергии просто теряется еще один день. отлично учите)))
Запрос должен быть с планом выполнения.
На собеседовании спросят зачем ты это сделал, а не "чтобы просто было веселее"
в первом задании не понимаю зачем джойнить одну и ту же таблицу?
Закидывай пандас на сложном уровне
Обязательно будет!
Ещё вопрос: почему BETWEEN не подходит в последней задачи?
Подходит конечно! Лучше бы его использовал
@@Noukash так в чем дело? Я очень удивился, тем более в условии не сказано о граничных значениях!
@@MsAlexandr76 Просто использовал не его) Можно сказать ошибся. Можно было бы использовать >= и и
А я ещё удивляюсь: что за люди вместо одного запроса делают кучу лишних ненужных джойнов)
16:50. Никогда не делайте так, как сделал этот "ментор". За подобное решение - дисквалифицировал бы соискателя нафиг. Причем сначала правильно же начал делать, а потом сделал фигню: Представьте на минутку, что есть несколько покупателей с одинаковыми первыми именами. Представили? Правильно, будет ошибка, так как разные покупатели с одинаковыми ферстнеймами будут сгруппированы в одну группу. Поэтому в данном случае - правильно как раз таки группировать по кастомер ID так как скорее всего, это внешний ключ охваченный ссылочной целостностью, ну и как минимум - даже если в таблице кастмоеров нету такого ИД - мы понимаем что это точно заказы одного и того же кастомера. А джоинт в этом случае лучше должен быть левый, а то мало ли чего, это как раз таки мысль безусловно здравая(хотя в нормальной БД - такие вещи связывают внешними ключами).
Каждый раз, когда человек, говорящий об ИТ называет "Кремниевую долину" "Силиконовой", сразу как-то пропадает доверие... Только у меня так?
почему не between с датой в where?
Between действительно лучше. Можно или >=
Было бы не плохо оптимальность каждого решения показывать.😉
было бы, но без конкретного сервера это скорее на "поговорить". как прочитать план запроса, на что обратить внимание, где есть потенциальные узкие места и т.п.
Прикольно. В tsql завершающая 2-й пример инструкция "limit 1" пишется как "top 1" сразу после select.
Неужели такие простые задачи.
Нее. Скорее это какой то предварительный этап отсева.
Да - это на первичном скрининге. На последующих этапах они сложнее
Программисты 1С смеются над сложностью задач amazon и facebook )
LIMIT vs TOP в чём разница?
Одно и тоже в разных диалектах SQL. Насколько я помню, TOP используется в SQL Server.
Хм, как по мне задачи и на 1/3 не близки по сложности к тому что встречаешь обычно в работе
Это наверно на джуна?
В последней задаче если имена повторяются у тебя сложатся суммы.
Да - но в задаче указано и я это проговорил, что для простоты имена уникальны. Так можно было бы оставить группировку по айди и приджойнить имя
Школьникам будет интересно
Там в 1й задаче иногда 0/0 будет деление
что это за сайт?
Stratascratch
Чувак ты путаешь соединение join и обьединение union
Ниче себе, Пол Дано выучил русский и ведёт канал про айти?
Бетмен мало денег собрал, переквалифицировался
Это на trainee аналитика что ли задачи? Примитивные вещи, единственный ньюанс (актуальный для меня), придется поломать голову как это делается на диалекте использованных БД (все же ClickHouse попривычнее).
Плохое решение для первой задачи, так как идёт два прохода по таблице. Лучше бы было:
select date, sum(action)/count(*) from (select user_id_sender, user_id_reciver, min(date) date, min(case when action = 'accepted' then 1 else 0 end) as action from fb_friend_request group by user_id_sender, user_id_reciver) group by date order by date
Я ораклист, поэтому приведение типов додумайте сами. :)
Плохое решение для третьей задачи:
1. Имена тянутся для каждой записи, даже тех, которые нам не нужны. Лишняя нагрузка на вторую таблицу. Надо считать топа, а потом по id подтягивать его имя
2. Если есть тёзки, но разные покупатели, то они будут просуммированы.
это задачи для кого? мне кажется это легко даже для джунов (:
Согласен они простоватые, но в лайв режиме на собесах спрашивают похожее - не берут сильно сложные. В следующий раз возьму посложнее)
@@Noukash было бы реально интересно посмотреть что-нибудь посложней с партишинами например
@@DostoevskyFedor1821 Следующее видео про SQL возьму задачи от туда, но хард тогда)
@@Noukash вот это по нашему! ждемс тогда (:
У тебя не оптимальное решение с точки зрения перформанса.
Зачем ты джойниш таблицу, для все строк а потом эти строки пропускаешь. Проще вытащить одну запись в подзапрос, и уже потом заджойнить. Так будет работать быстрей
А если еще и дальше углубиться. То в последнем примере. Зря ты на дату забил.
А что если там данных будет за 20 лет? Ты и для них будешь проводить группировки, джойны и все вычисления?
Запрос получится тяжёлым.
Вот что нужно сделать.
1. Отфильтровать ордера по дате.
2. Найти максимальное значение с ценой. И сделать это подзапросом.
3. Заджойнить таблицу юзеров
4. И вывести результат
Согласен с комментариями! Я вообще не оптимизировал запросы с точки зрения перформанса и не учитывал крайние случаи/ошибки в данных. Для меня было важно показать основной принцип вычислений. Но все комментарии 100% по делу
Какие-то дико простые задачи
Зачем писать, что компания в какой-то дыре запрещена? Всем насрать на это
как зарплату из заграницы получать то?)))
Почему так изево...
Какие-то слишком простые задачи
Легчайшие задачи, причём решить их можно без ваших "ср@ных" Джойнов
Пришлите сюда решение первой задачи без left join
@@rubinroterstern зачем пересылать, посмотри внимательно на решение других пользователей в этом же видео, там есть решение без двойнов. Джойны вообще в 90% случаях не нужны
Что не так с джойнами?)
@@Noukash это, походу дела, религиозный вопрос.
@@rubinroterstern Да - наверное его просто джойн покусал)
Какие-то совсем уж каличные задачки)) такое даже не интересно решать
Чтож ты зажатый сидишь, постоянно крест руками делаешь, аж неприятно смотреть
Так не смотри!
@@Noukash Посмотрел уже, поздно!
Работа на за границу при ведении боевых действий расценивается как предательство
так, попробую писать решения до твоего варианта))
1)
SELECT t1.date, COUNT(t2.action) / COUNT(t1.action) percentage_acceptance
FROM fb_friend_requests t1
LEFT JOIN fb_friend_requests t2
ON t1.user_id_sender = t2.user_id_sender
AND t1.user_id_receiver = t2.user_id_receiver
AND t1.action != t2.action
WHERE t1.action = 'sent'
GROUP BY t1.date
upd: Спасибо за лайфхак с 1.0. Раньше игрался с функциями
2)
WITH cte AS (
SELECT date, SUM(consumption) sumConsumption
FROM(
SELECT * FROM fb_eu_energy
UNION ALL
SELECT * FROM fb_asia_energy
UNION ALL
SELECT * FROM fb_na_energy
) t1
GROUP BY date
)
SELECT *
FROM cte
WHERE sumConsumption = ( SELECT MAX(sumConsumption) FROM cte )
upd: Важно не через лимит идти, мы же так можем данные потерять
3)
WITH cte AS (
SELECT order_date, t1.id, first_name, COUNT(*) countOfOrders, SUM(total_order_cost) total_order_cost
FROM customers t1
JOIN orders t2
ON t1.id = t2.cust_id
WHERE order_date BETWEEN '2019-02-01' AND '2019-05-01'
GROUP BY order_date, t1.id, first_name
)
SELECT first_name, total_order_cost, order_date
FROM cte t1
WHERE countOfOrders = ( SELECT MAX(countOfOrders) FROM cte )
upd: Тут тоже самое, можем данные потерять, опасно через лимит решать такие задачи