🔥 Как тестировать SQL код при решении тестовых? Есть библиотека Pandasql. 🛠 Она позволяет выполнять SQL-запросы прямо в Юпитере, а вместо базы можно просто создавать DataFrame.
Например:

from pandasql import sqldf
import pandas as pd
# Создание DataFrame user_actions
user_actions = pd.DataFrame({'user_id': [1, 1, 2, 3, 3],
                             'order_id': [101, 102, 103, 104, 105],
                             'action': ['create_order', 'cancel_order', 'create_order', 'create_order', 'cancel_order']})

Предположим, у нас есть таблица user_actions с информацией о действиях пользователей на нашем сервисе, включая id пользователя, id заказа и тип действия. Мы хотим отобрать всех пользователей, которые ни разу не отменяли заказы.
Выполнение SQL-запроса с помощью pandasql:

query = """
SELECT DISTINCT user_id
FROM   user_actions
WHERE  user_id NOT IN (
    SELECT user_id
    FROM   user_actions
    WHERE  action = 'cancel_order'
)
"""
sqldf(query) 

Мы используем подзапрос, чтобы получить все user_id пользователей, которые отменили заказы, и затем исключаем этих пользователей из основного запроса с помощью оператора NOT IN.

Таким образом, мы получаем результат, который содержит только user_id пользователей, которые ни разу не отменяли заказы.

Учитывая, что осень - пора активных собеседований, то кому-то это точно пригодится. ✔️

P.S. Пост про то, какие онлайн сайты можно использовать для SQL у меня тут

📍 Пришло время упорядочить свои посты. Чтобы вам легче было в них ориентироваться и отыскать что-то нужное.

🐍 🔤🔤🔤🔤🔤🔤 ✨

- Живой кодинг на собеседовании. Примеры.
- Тестовое в Skyeng (ссылка на скрипт в комментариях)
- markdown и вставка картинок в Jupyter Notebook
- Метод format в Питоне
- Рабочий пример отчета для отдела продаж – формирование группы клиентов по разным условиям
- Особенности pivot_table на питоне
- Примеры сводной таблица pivot_table()
- Создание диапазона дат на питоне
- Рабочий скрипт по отвалу клиентов
- Отвал клиентов с доп.условиями
- Три топ клиента за год в каждой нише
- Кумулятивная функция сumsum()
- Примеры функции transform()
- Вопросы с собеседований по Python и Pandas
- Как определить к какой стране относится номер телефона? библиотека Phonenumbers
- Скрипт по когортному анализу (часть на SQL, часть на пандасе)
- Примеры np.where()
- Регулярные выражения на Питоне. модуль re

✨🔤🔤🔤✨ ✨

- Пример рабочего SQL скрипта в Jupyter Notebook
- Трафик, лиды, конверсия
- SQL Between
- HAVING в SQL
- Вопросы с собеседований по SQL
- Тестовое с решением
- Скрипт по когортному анализу (часть на SQL, часть на пандасе)
- Примеры использования CASE
- Group by на примере из курса Карпова и на рабочем примере.
- Регулярка для Clickhouse
- Библиотека Pandasql для написания SQL кода в Jupyter Notebook

✨ 🔤🔤🔤🔤🔤 ✨

- Как уменьшить вес Excel файла
- Excel vs Google таблицы
- Условное форматирование в Google таблицах.
- Выгрузка всех файлов с диска в Excel

✨☹️☹️😙🤑😓😓 ✨

- Как я сменила профессию
- Какие бывают аналитики
- Мотивация
- Теория вероятности на минималках
- Как заходить в LinkedIn без VPN
- YandexGPT 2 рассказал почему стоит выбрать карьеру в аналитике данных

p.s. Вам знакомо то чувство чистоты, когда у вас на рабочем столе был бардак, а вы взяли и навели порядок? Книжки расставили по местам, бумаги рассортировали по папкам и протерли пыль с клавы. Вот сейчас что-то похожее ощущаю 🍀

🔥 Установка PostgreSQL и создание таблицы в DBeaver из CSV ——————————————————————————————

Все-таки лучший способ для решения тестовых, для тренировки своих навыков в PostgreSQL – это установить базу к себе на компьютер и заливать туда csv файлы с нужными данными вместо создания таблиц. Да и оконки не потренируешь с pandasql, в которой синтаксис SQLite.
Я раньше думала, что это как-то сложно и потребуется много времени. Немного повозиться мне конечно пришлось, но зато теперь будет самая краткая инструкция!

✔️ Шаг 1 Грузим PostgreSQL для вашей операционной системы на странице официальной загрузки. Я ставила на Windows. Скачивается файл с расширением exe, который можно запустить и установить, как обычную программу! У меня этот файл соответственно назывался postgresql-16.1-1-windows-x64.exe Весил 361 МБ

Я выбрала 16 версию – самую новую. x64 - это 64-разрядная операционная система (какая у вас можно узнать в свойствах Мой компьютер). Там в характеристиках устройства указан тип вашей системы.
Запускаете инсталлятор – просто два раза щелкаете по файлу. В открывшемся окне выбираете Locale: «Russian, Russia» (русский язык в стране Россия) и далее выполняете то, что указано. Выбираете путь, где будет располагаться база. В конце установки снимите галочку в пункте Stack Builder. Там какие-то доп.утилиты предлагаются (не нужны короче, можно не заморачиваться)
На этом установка PostrgreSQL почти завершена! 🤪

✔️Шаг 2 Теперь заходите в Пуск и нажимайте pg и увидите слоника pgAdmin. При первом запуске pgAdmin просит задать Master Password. Он будет использоваться для безопасного сохранения паролей к базам данных PosgreSQL. Master Password можно выбрать любой, главное, запомнить его. И запомните номер Port (у меня 4 цифры). Это в свойствах (Properties)->Connection можно найти.
В целом только с помощью pgAdmin можно писать запросы, но я привыкла работать через DBeawer. Поэтому переходим к следующему шагу.

✔️Шаг 3 Если у вас не установлен DBeaver, то скачайте его тут.
У меня он уже установлен для работы. Идем в меню База данных. Там выбираем Новое соединение. Выбираем PostgreSQL. В настройках прописываем: Хост - localhost, база данных – postgres, Порт – тот номер Port из pgAdmin, пароль тоже вводим и жмем OK.

Теперь в списках баз DBeaver появился postgres с зеленой галочкой. Кликаете правой кнопкой мыши по postgres — Редактор SQL — Новый редактор SQL. Он Создадим схему. Пишем create schema test Теперь создадим таблицу. Сначала нужно обновить список объектов. Встаем на значок postgres внутри Базы данных и жмем F5. Появились наша схема test. А внутри название Таблицы. Правой клавишей мышки щелк и выбираем Импорт данных. В открывшимся окне, выбираем файл csv, который необходимо загрузить. Настраиваем параметры при необходимости. Здесь обращаю внимание на “Разделитель столбцов”, если в итоге вы получили некорректную таблицу, то необходимо проверить какой разделитель в исходном файле (иногда помогает сменить разделитель с запятой (,) на точку с запятой (;)). Нажимаем “Далее”, а потом “Продолжить”. Идет загрузка данных.
📌 И, ура, у нас табличка с нашими данными из файла! 🎯 Тип данных колонок можно менять прямо в свойствах.

🔥 Я побывала на интенсиве, где разбирали тестовое задание в OZON ⚡️ на аналитика данных! И принесла самое интересное сюда для тех, кто не смог присутствовать!
Задание
Для выполнения этого задания требуется сгенерировать DataFrame с синтетическими данными. DataFrame должен состоять из 10000 строк и 5 колонок. Каждую из колонок мы предлагаем тебе создать и наполнить следующим образом:

🔹 1-я колонка – user_id – идентификатор пользователя. Длина user_id должна равняться 15-ти символам. Идентификатор состоит из случайной комбинации следующих символов: "1234567890abcdefghijk". Для каждой строки в DataFrame значение user_id формируются случайным образом.

🔹 2-я колонка – order_number – номер заказа. Столбец необходимо заполнить случайными значениями в диапазоне от 1 до 10.

🔹 3-я колонка – click2delivery – время, прошедшее с момента оформления заказа до вручения клиенту. Столбец необходимо заполнить случайными значениями из нормального распределения со средним 1440 и стандартным отклонением 200.

🔹 4-я колонка – order_items_sum – общая стоимость заказа. Значения для этого столбца необходимо взять из экспоненциального распределения с параметром λ = 1, смещённого на +1.

🔹 5-я колонка – retention – день жизни покупателя, в который он совершил заказ. Необходимо сгенерировать значения 1, 2, 3, 4, 5 с вероятностями 0.35, 0.25, 0.2, 0.15 и 0.05 соответственно.
В случае, если в колонке user_id встречаются дублирующиеся значения, оставь только первое из них.

Итак, импортируем библиотеки и задаем seed:

import pandas as pd
import numpy as np
import random
np.random.seed(42)

Это для того, чтобы скрипт выполнялся идентичным образом при каждом запуске. Мы предоставляем начальное значение (42 - популярный вариант).

Функция random.choice() возвращает один элемент из последовательности. Запихнем ее в созданную нами функцию generate_user_id(), а возвращать она будет соединенные join-ом выбранные по одному элементы из characters в количестве 15 штук.

def generate_user_id():
    characters = "1234567890abcdefghijk"
    return ''.join(random.choice(characters) for _ in range(15))
#Проверим как работает функция
generate_user_id() 
'k4198854h3id213'

Теперь создадим функцию generate_df для генерации датафрейма.
И нашу функцию для user_id generate_user_id() запихнем в цикл for _ in range(n) чтобы нагенерировать 10 тыс строк.
Тут же создадим столбцы order_number, click2delivery, order_items_sum и retention.

def generate_df(n):
    data = {
      'user_id': [generate_user_id() for _ in range(n)],
      'order_number': np.random.randint(1, 11, size=n),
      'click2delivery': np.random.normal(1440, 200, size=n),
      'order_items_sum': np.random.exponential(1, size=n) + 1,
      'retention': np.random.choice([1, 2, 3, 4, 5], size=n, p=[0.35, 0.25, 0.2, 0.15, 0.05])
      }
    df = pd.DataFrame(data)
    return df

df = pd.DataFrame()

А вы знали, что в питоне можно разряды в числах вот так показывать? n = 10_000 Я – нет. 😜
Пока длина df не будет 10 тыс будет выполняться соединение concat два датафрейма.
Условие n = 10_000 - len(df) при длине df 10 тыс строк будет равно 0 и while перестанет выполняться.

n = 10_000
while n:
    new_df = generate_df(n)
    df = pd.concat([df, new_df])
    df = df.drop_duplicates(subset='user_id', keep='first')
    n = 10_000 - len(df)

df.head()

Если хотите разбор следующих заданий в ближайшие дни ставим 🔥

🔥 Итак, разберем следующие задачки из тестового на аналитика данных в OZON. Первую часть смотрите тут

⭐️ Задание 2
Для всех строк исходного датасета, сгруппированных по номеру заказа, посчитать среднее значение времени доставки по группе. Результат необходимо добавить в новый столбец датафрейма.
У нас с прошлого задания сформировался датасет df со следующими полями:
user_id, order_number, click2delivery, order_items_sum, retention
Делаем обычную группировку:

orders = (
    df
    .groupby('order_number')['click2delivery']
    .agg(mean_time='mean')
    .reset_index()
)

Если мы пишем код в скобочках (), то можно вот так прописывать, перенося точку и метод на отдельные строки.
Ну и присоединим это среднее к основному df :

df = df.merge(orders, how='inner', on='order_number')

⭐️ Задание 3
Отдельной колонкой добавить значения последовательности, начинающейся с 0 и 1, где каждый следующий элемент является суммой двух предыдущих, умноженных на 0.5.
Задаем лист arr, а потом в цикле пока длина листа не составит 10 тыс, добавляем сумму двух предыдущих элементов, умноженных на 0.5:

arr = [0, 1]
while len(arr) < 10_000:
    arr.append(sum(arr[-2:]) * 0.5)
df['seq'] = arr

Ну и еще было несколько заданий. 📎 Их найдете в файле юпитер ноутбука 👉 здесь

🍒🍒🍒🏆🍒😘 Теперь когда у меня есть Postgres 🐘 на компе (пост как установить тут) и она подключена к DBeaver,я могу показать вам как писать оконки на SQL. В одном из тестовых было следующее задание. Когда люди входят/выходят из здания, то это логгируется. Записывается время входа/выхода (pass_date), номер сотрудника (tab_id), адрес офиса (pass_name) и pass_direction (1 – вход, 0 - выход).
Нужно посчитать чистые рабочие часы сотрудников по дням без времени “на покурить” и обеды, когда они выходили из здания. Они могут ходить на улицу по несколько раз за день. И это тоже нужно учесть. Иногда сотрудники несколько раз прикладывают свой пропуск или пропускают по своему пропуску коллег, такое нужно исключать.

Тут нам поможет оконная функция LAG. Она используется для сравнения текущего значения колонки с предыдущим значением (в скобках смещение на 1 указала). Вот так мы узнаем время предыдущей записи:

lag (pass_date,1) over (partition by tab_id order by pass_date)

pass_date - это столбец, значение которого нужно сравнить с предыдущим значением.
partition by tab_id – берем партиции по номеру сотрудника и сортируем по дню order by pass_date

Когда мы из pass_date вычтем получившееся значение, то это будет разница между текущим временем (текущей строчкой) и временем предыдущего действия (вход либо выход) - time_diff

pass_date - lag (pass_date,1) over (partition by tab_id order by pass_date) as time_diff

Далее суммируем показатели текущего и предыдущего действия PASS_DIRECTION, тоже используя схему с lag

pass_direction +lag(pass_direction,1) over (partition by tab_id order by pass_date) as sum_enter

А теперь завернем все в подзапрос и прикрутим условие с фильтром. Мы суммируем время только когда выполняются два условия: sum_enter = 1 и pass_direction =0

sum(time_diff) filter (where (sum_enter = 1) and pass_direction =0)

В итоге:

select
  working_date,
  tab_id,
  sum(time_diff) filter (where (sum_enter = 1) and pass_direction = 0)
from
  (
  select
    date(pass_date) as working_date,
    pass_date,
    tab_id,
    pass_direction,
    -- разница между текущим временем и временем предыдущего действия 
    pass_date - lag (pass_date,  1) over (partition by tab_id order by pass_date) as time_diff,
    -- суммируем показатели текущего и предыдущего действия (0+1 - то, что нужно), а если 1+1=2 - такое нам не надо
    pass_direction + lag(pass_direction, 1) over (partition by tab_id order by pass_date) as sum_enter
  from
    test.newtable
  order by
    pass_date  
) t1
group by 1, 2

💡 Тренируйте оконки, это прямо уже must have на собесах! Файл с данными забирайте для тренировки! 👌

🔔 Друзья, я к вам с подарочком! 🎁🎁🎁 Скоро Новый Год 🎄и хотелось что-то вам подарить. Но что можно подарить кроме своих знаний, которые я тут и так выкладываю? Ладно, раскрою небольшой секрет. Я зарегистрирована как ментор в сообществе IT наставников на getmentor. У меня не слишком много свободного времени, поэтому я про это не рассказывала. Но кто меня там находил, тот получал помощь.

Так вот, я разыграю в пятницу три запроса на менторинг для Вас абсолютно бесплатно! 🚀. Сам созвон осуществим уже в Новом году, так как, понятное дело, сейчас в предновогодней суете всем некогда. Оставляйте комментарии тут под постом (например, хочу консультацию), а в пятницу с помощью random выберу трех счастливчиков, которые получат возможность индивидуальной консультации.
Темы для консультаций, которые я провожу:
- Расскажу о карьере в аналитике данных, путях профессионального развития и рынке вакансий. Покажу чем предстоит заниматься аналитику в IT. 📊
- Подскажу как организовать свое развитие, на что тратить время и что даст наибольший вклад в повышение квалификации
- Помогу с оформлением и корректировкой резюме. 🖊
- Просто поговорим о наболевшем в карьере, дам творческий пинок и вдохновение! 🏅

🍀 Удачи! 🍀

😀 ИТОГИ РОЗЫГРЫША 😀

Во-первых, спасибо огромное всем, кто принял участие! Мне было очень приятно осознавать, что людям понравился мой порыв. Этот канал я создавала в свое время именно с целью поделиться знаниями и опытом. Это уже потом мне стали предлагать рекламу (от которой глупо отказываться я считаю 😜)

Ну и раз уж у нас образовательно - просветительский контент, то я не стала использовать готовые генераторы случайных чисел в браузерах. Мы питон, в конце концов, что зря изучаем? 😂
Поэтому я написала генератор на питоне. Код вы видите в видео.

💎 Я скопировала всех участников, вставила в excel. Подтянула данные в датафрейм df_members.
💎 Потом сгенерировала порядковый номер каждому участнику range(1,кол-во участников+1)

 df_members['Номер участника'] = range(1,74)

💎 Ну и сама генерация: создаем DataFrame с 1 строкой и 3 столбцами. Числа генерируются от 1 до 73
На входе функция np.random.randint() принимает границы диапазона (верхняя граница в диапазон не входит, поэтому
ставим 74)

pd.DataFrame(np.random.randint(1,74,size=(1,3)), columns=['Победитель 1','Победитель 2','Победитель 3'])

💎 Чтобы увидеть кто победители, мы фильтруем df_members по выпавшим номерам

df_members[(df_members['Номер участника'] == df['Победитель 1'][0])
          |(df_members['Номер участника'] == df['Победитель 2'][0])
          |(df_members['Номер участника'] == df['Победитель 3'][0])]

🥳🥳🥳 Теперь итоги! 🎆🎆🎆

16 @s_g_olga Оля
52 @LidiyaRB LidiyR
67 @yulia1999 Julia

Победители, с Вами свяжусь в директе. 📲
🔗 Ну и всем: ловите этот юпитер ноутбук с кодом и моей картинкой It персонажа сгенерированной в app.leonardo.ai
А тут список участников. Можете поиграться с данными.

p.s. Ну и еще лайфхак - чтобы записать видео с экрана в Windows 10, я нажала клавиши Windows + Alt + R для записи.

🌲🌲🌲 С наступающим праздником, друзья!

🥰Желаю каждому из Вас, чтобы в следующем году самые смелые планы осуществились и самые оптимистичные прогнозы оказались скромнее реальности! 🍀

🥰Желаю каждому из Вас найти свое призвание (видов аналитиков много и это хорошо), которое будет приносить радость, достаток и удовлетворение в Новом году! Осуществляйте свои мечты, берегите здоровье, наполняйтесь энергией и верьте в себя! 🔔

🥰Тем, кто только делает первые шаги в IT, можно пожелать терпения и упорства. Не бойтесь ошибаться и учиться на своих ошибках, ведь это бесценный опыт. Стремитесь к саморазвитию и не останавливайтесь на достигнутом. Мир IT постоянно меняется и развивается, поэтому нужно быть в курсе всех новинок и тенденций. Удачи вам в освоении новых технологий и успехов в карьере! 🌟🌟🌟

P.S. А я убегаю резать салаты 😂🍨😋🎉

Смотреть на огонь можно бесконечно долго. Сидеть, греться, очищать разум.. .🔥

Всем хорошо отдохнуть, перезагрузиться в оставшиеся праздничные дни и подумать о том каким мы хотим видеть следующий год, что хотим сделать в нем, какими быть.. ✌️🎉🎄

🔥 1 февраля я запускаю тренинг “Старт в аналитике” длиной всего в один месяц!

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

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

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

Так как же быть? Вот тут я хочу предложить вам интересное решение.

✔️ Повторюсь, что 1 февраля я запускаю тренинг “Старт в аналитике” длиной всего в один месяц! Будем заниматься два раза в неделю – это десять групповых онлайн занятий со мной, где я показываю вам выжимку необходимых знаний по SQL и Питону на практике и без воды.
После этого вы спокойно вписываете в свое резюме эти навыки и уже пробуете откликаться на вакансии.

Весной, как правило, очень много вакансий. Усиленно позанимавшись в феврале, в марте вы будете с hard skills в резюме.
Как мы будем заниматься:
🎯Сначала установим по инструкции каждому PostgreSQL и Dbeaver для отработки SQL запросов. В каждом втором тестовом просят написать какой-то запрос на SQL. И у вас уже будет среда, в которой можно будет протестировать ваш код.

🎯А для питона установим Anaconda, там есть Jupyter Notebook, в котором работают большинство аналитиков. После занятий у вас останутся юпитерские блокноты со скриптами.

🎯 Ну и в финале я просмотрю ваши резюме и дам рекомендации по улучшению.

💸Цена тренинга 20 тыс.руб.💸 Сейчас она минимальна 🔽и действует до пятницы включительно. Позже будет на 5 тысяч дороже.🔼

В целом каждый решает сам – нужно ему это или нет. Можно и самому все изучить, но как показывает практика люди все-таки воспринимают информацию лучше, когда она исходит от человека. И очень многим требуется еще и простая человеческая поддержка в это время. Я буду на связи, создадим отдельный ТГ чат, где сможем обсуждать нюансы и просто общаться. Возможно такой формат подойдет именно Вам.

Оплатить можно тут. В комментарии укажите свой ник в телеграме
Если остались вопросы, то можно задать их в личке @Valeria_Shuvaeva

🎉 Старый Новый год - это ещё один шанс начать жизнь с чистого листа и воплотить свои мечты в реальность! 🌟
В этот праздничный период я хочу предложить вам классную возможность оплатить мой тренинг в рассрочку! 💫

Назовем эту акцию “Старый Новый Год: шанс на успех”.
Немного математики вас не испугает? 20 тыс за 10 занятий, то есть 2 тыс за одно занятие. За два занятия соответственно платите 4 тысячи. Если есть силы и деньги через неделю занятий, то опять оплачиваете 4 тысячи и занимаетесь еще 2 занятия и так далее.
Оплата через сайт самозанятых в размере 4️⃣🔤🔤🔤 рублей тут. 🎆
Тех, кто уже оплатил полностью ждет секретный подарок в качестве бонуса! 🎁
Пусть этот “Старый Новый Год” принесет вам знания, надежду и новые свершения! 🎉

Ну и немного тестовых от разных компаний, которые мне прислал @sds600 Спасибо ему за них!
✔️1.Проанализируйте выгрузку из appsflyer. Выделите в отдельный отчет трафик, который кажется вам подозрительным. Объясните свое решение. Что можно сделать для автоматизации вычета подозрительного трафика?
✔️2. Несколько заданий на SQL
✔️3. Проанализируйте данные отчета из Google Analytics.
Кратко опишите, какие особенности и аномалии вы видите и как их можно учесть, какие выводы можно сделать, какие можете дать рекомендации на основании данного отчета?
✔️4. Сделайте выводы на основе данных

P.S. На картинке отзывы выигравших консультацию. Все три проведены! 💪