Практика 8. Итоговая работа

Wiki-версия - требуется доработка

1. Настроить виртуальную машину Ubuntu Server 22.04.5 LTS в системе виртуализации операционных систем, а также настроить Docker контейнеры PostgreSQL, Arenadata Cluster Manager совместно с бандлами для кластера и мониторинга (Prometheus, Grafana). (1 балл)

В отчет прикрепить скриншот с интерфейса ADCM на странице Cluster->Overview где показано, что все сервисы подняты и работают в штатном режиме.

Рисунок 1 – скриншот интерфейса ADCM , демонстрация работы сервисов

2. Создать базу данных ecommerce и развернуть public схему таблиц, на основе приложенного скрипта, для хранения данных транзакций в PostgreSQL посредством postgresql-client (DBeaver или любой другой админ баз данных). Для каждой таблицы сделать импорт данных посредством импорта из CSV-файла в следующем порядке (1 балл):

В отчет прикрепить визуализацию схемы таблиц, полученную в консоли командной строки или в виде диаграммы в админе баз данных (например DBeaver).

Рисунок 2 – схема взаимодействия таблиц

3. Написать запросы на языке SQL для базы данных ecommerce для получения данных по следующим заданиям:

3.1. ТОП-10 категорий по выручке, с переводом (0,5 балла)

Нужно определить десять товарных категорий с наибольшим оборотом продаж. Для каждой категории требуется посчитать сумму цен всех проданных позиций (это и есть выручка/GMV категории), количество уникальных заказов, в которых встречались товары этой категории, и общее число товарных позиций. Дополнительно к исходному названию категории нужно вывести её англоязычный перевод из словаря переводов. Итоговый набор данных должен быть отсортирован по выручке по убыванию и ограничен первыми десятью строками.

3.2. Платежное поведение (0,5 балла)

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

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

Следует учитывать, что у одного заказа может быть несколько платежей. Доля считается на уровне «есть/нет» для типа оплаты в заказе.

3.3. Зависимость оценки от скорости доставки (0,5 балла)

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

Далее каждый заказ попадает в один из трёх интервалов длительности:

− до пяти дней включительно,

− от шести до десяти дней включительно,

− более десяти дней.

Для каждого интервала требуется посчитать количество заказов и среднюю оценку из отзывов. Итог должен содержать три строки — по одному агрегату на интервал — в логическом порядке от самого быстрого интервала к самому длительному.

3.4. Эффективность продавцов (0,5 балла)

Нужно получить рейтинг продавцов по активности и базовым бизнес-показателям. Для каждого продавца считается количество уникальных заказов, в которых он участвовал, число уникальных клиентов, которым он продал хотя бы одну позицию, общее число проданных товарных позиций, совокупный оборот по этим позициям (сумма цен) и средняя стоимость доставки на позицию (среднее от значений стоимости фрахта по позициям). Затем формируется топ-10 продавцов, отсортированный по числу заказов по убыванию. В отчёте по каждому продавцу должны присутствовать перечисленные метрики.

4. Построить дашборд в Grafana для визуализации данных из ecommerce на основе следующих задач и нескольких ваших созданных визуализаций:

4.1. Динамика числа заказов (0.5 балла)

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

4.2. Количество заказов по оценкам (0.5 балла)

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

4.3. Распределение способов оплаты (0.5 балла)

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

4.4. Топ товарных категорий по объему продаж (0.5 балла)

Задача заключается в том, чтобы вывести топ-10 товарных категорий по общему объёму продаж. Для каждой категории суммируется стоимость всех товарных позиций, после чего выбираются десять лидеров. Визуализация выполняется в виде горизонтальных столбцов (линеек), что позволяет удобно сравнивать категории между собой по длине.

4.5 Ваши дополнительные две визуализации по 1 баллу каждый + дашборд отдельно в виде скриншотов, в котором умещается ваш дашборд. (2 балла)

4.5.1 Средняя стоимость доставки (freight) по месяцам

4.5.2 Количество новых клиентов по месяцам