| Студент | Варіант |
|---|---|
| Бовсуновська Марія Євгенівна | Варіант 3 |
| Бузюрова Анна Олександрівна | Варіант 4 |
| Гудименко Максим Леонідович | Варіант 1 |
| Калініченко Назар Володимирович | Варіант 1 |
| Кармелюк Тимофій Ігорович | Варіант 3 |
| Косован Іван Ігорович | Варіант 2 |
| Малишев Кирило Едуардович | Варіант 3 |
| Марков Артур Андрійович | Варіант 2 |
| Мельник Максим Володимирович | Варіант 1 |
| Морозов Ростислав Олегович | Варіант 4 |
| Набережний Артур Вячеславович | Варіант 4 |
| Пантьо Іванка Іванівна | Варіант 2 |
| Прийдун Марія Володимирівна | Варіант 1 |
| Титаренко Віктор Олександрович | Варіант 2 |
| Фельдман Михайло Георгійович | Варіант 1 |
| Чернецький Дмитро Максимович | Варіант 3 |
| Шабатін Павло Євгенійович | Варіант 4 |
Лабораторна робота №2
Хмарні технології обробки даних
Важливо
- Дедлайн здачі роботи: 28.03.2024
Розподіл по варіантах
Попередні вимоги
Для виконання лабораторної роботи необхідно виконати завдання з попередньої лабораторної роботи.
Завдання
Використовуючи дані з попередньої роботи, необхідно проаналізувати сервіс з доставки їжі та розрахуємо кілька показників. Вам потрібно написати кілька SQL-запитів в BigQuery та візуалізувати їх за допомогою Looker Studio.
Проаналізуємо наскільки швидко зростає аудиторія нашого сервісу та подивимося на динаміку числа користувачів та кур’єрів.
Завдання 1
Для кожного дня, представленого в таблицях user_actions та courier_actions, розрахуйте такі показники:
- Число нових користувачів.
- Число нових кур’єрів.
- Загальна кількість користувачів на сьогодні.
- Загальна кількість кур’єрів на цей день.
Колонки з показниками назвіть відповідно new_users, new_couriers, total_users, total_couriers. Колонку з датами назвіть date. Простежте, щоб показники були виражені цілими числами. Результат має бути відсортований за зростанням дати.
Поля в результуючій таблиці: date, new_users, new_couriers, total_users, total_couriers
Пояснення:
Новими вважатимемо тих користувачів та кур’єрів, які в цей день здійснили свою першу дію в нашому сервісі. Загальна кількість користувачів/кур’єрів на поточний день — це результат додавання числа нових користувачів/кур’єрів у поточний день зі значеннями аналогічного показника всіх попередніх днів.
Підказка на випадок, якщо зовсім не виходить
Щоб порахувати кількість нових користувачів/кур’єрів на кожну дату спочатку необхідно для кожного користувача/кур’єра визначити мінімальну дату, тобто дату першої дії в нашому сервісі. Потім потрібно провести групування і для кожної отриманої дати порахувати кількість користувачів/кур’єрів із цією датою. Далі до отриманої таблиці достатньо застосувати віконну функцію і кожної дати обчислити накопичувальну суму числа користувачів/кур’єрів.
Завдання 2
Аналізуючи динаміку показників із попереднього завдання, ви могли помітити, що порівнювати абсолютні значення не дуже зручно. Давайте порахуємо динаміку показників у відносних величинах.
Доповніть запит із попереднього завдання і тепер для кожного дня, поданого в таблицях user_actions та courier_actions, додатково розрахуйте такі показники:
- Приріст числа нових користувачів.
- Приріст числа нових кур’єрів.
- Приріст загальної кількості користувачів.
- Приріст загальної кількості кур’єрів.
Показники, розраховані на попередньому кроці, також включіть у результуючу таблицю.
Колонки з новими показниками назвіть відповідно new_users_change, new_couriers_change, total_users_growth, total_couriers_growth. Колонку з датами назвіть date.
Усі показники приросту рахуйте у відсотках відносно попереднього дня. Під час розрахунку показників округляйте значення до двох знаків після коми.
Результуюча таблиця має бути відсортована за зростанням дати.
Поля в результуючій таблиці: date, new_users, new_couriers, total_users, total_couriers, new_users_change, new_couriers_change, total_users_growth, total_couriers_growth
Пояснення:
Не забувайте при діленні заздалегідь наводити значення до потрібного типу даних. Пропущені значення приростів для першої дати не заповнюйте - просто залиште поля в цьому рядку порожніми.
Підказка на випадок, якщо зовсім не виходить
Для розрахунку приростів скористайтесь віконними функціями та функціями зсунення.
Завдання 3
- Збережіть отримані результати в окремі представлення в BigQuery.
- Підключіться до Looker Studio та побудуйте візуалізацію для отриманих даних.
- Збережіть створену візуалізацію в окремий дашборд.
- Поділіться посиланням на дашборд.
Підказка щодо очікуваної візуалізації
Очікується, що для Завдання 1 буде використані лінійні графіки, а для Завдання 2 - стовпчасті діаграми.
Але Ви можете запропонувати свої варіанти візуалізацій, якщо вони вважаєте, що вони краще підходять для відображення отриманих даних.
Проведемо аналіз користувачів, які оформлюють та оплачують замовлення у нашому сервісі.
Завдання 1
Для кожного дня, представленого в таблицях user_actions та courier_actions, розрахуйте такі показники:
- Число користувачів, які оплачують замовлення.
- Число активних кур’єрів.
- Частку користувачів, що платять, в загальній кількості користувачів на поточний день.
- Частку активних кур’єрів у кількості кур’єрів на поточний день.
Колонки з показниками назвіть відповідно paying_users, active_couriers, paying_users_share, active_couriers_share. Колонку з датами назвіть date. Простежте, щоб абсолютні показники були виражені цілими числами. Усі показники часток необхідно виразити у відсотках. Під час їх розрахунку округляйте значення до двох знаків після коми.
Результат має бути відсортований за зростанням дати.
Поля в результуючій таблиці: date, paying_users, active_couriers, paying_users_share, active_couriers_share
Пояснення:
Користувачів, які оплачують замовлення будемо вважати тих, які в даний день оформили хоча б одне замовлення, яке надалі не було скасовано.
Кур’єрів вважатимемо активними, якщо в даний день вони прийняли хоча б одне замовлення, яке було доставлено (можливо вже наступного дня), або доставили будь-яке замовлення.
Загальна кількість користувачів/кур’єрів на поточний день – це, як і раніше, результат складання числа нових користувачів/кур’єрів у поточний день зі значеннями аналогічного показника всіх попередніх днів. Ми рахували цей показник на попередніх кроках.
Не забувайте при діленні заздалегідь наводити значення до потрібного типу даних.
Підказка на випадок, якщо зовсім не виходить
Для вирішення завдання необхідно спочатку для кожного дня порахувати кількість користувачів, які оплачують замовлення, і активних кур’єрів, а потім об’єднати отримані таблиці з даними про загальну кількість користувачів і кур’єрів на поточну дату і далі розрахувати всі необхідні відносні показники. Загальну кількість користувачів та кур’єрів на поточну дату ми вже рахували раніше.
Завдання 2
Для кожного дня, поданого в таблиці user_actions, розрахуйте такі показники:
- Частку користувачів, які зробили в цей день всього одне замовлення, від загальної кількості користувачів, які оплачують замовлення.
- Частку користувачів, які зробили цього дня кілька замовлень, від загальної кількості користувачів, які оплачують замовлення.
Назвіть колонки з показниками відповідно single_order_users_share, several_orders_users_share. Колонку з датами назвіть date. Усі показники із частками необхідно виразити у відсотках. При розрахунку часток округляйте значення до двох знаків після коми.
Результат має бути відсортований за зростанням дати.
Поля в результуючій таблиці: date, single_order_users_share, several_orders_users_share
Пояснення:
Користувачами, що оплачують замовлення, як і раніше, вважаємо тих користувачів, які в даний день оформили (і не скасували) хоча б одне замовлення.
Не забувайте при діленні заздалегідь наводити значення до потрібного типу даних.
Підказка на випадок, якщо зовсім не виходить
Для вирішення завдання спочатку необхідно для кожного дня окремо розрахувати загальну кількість користувачів, що оплачують замовлення, кількість користувачів з одним замовленням і кількість користувачів з кількома замовленнями. Потім необхідно поєднати таблиці з цими даними за датою та порахувати всі необхідні показники.
Завдання 3
- Збережіть отримані результати в окремі представлення в BigQuery.
- Підключіться до Looker Studio та побудуйте візуалізацію для отриманих даних.
- Збережіть створену візуалізацію в окремий дашборд.
- Поділіться посиланням на дашборд.
Підказка щодо очікуваної візуалізації
Для Завдання 1 очікується використання лінійного графіку, а для Завдання 2 — нормована стовпчаста діаграма.
Розрахуємо декілька показників, пов’язаних із замовленнями.
Але Ви можете запропонувати свої варіанти візуалізацій, якщо вони вважаєте, що вони краще підходять для відображення отриманих даних.
Завдання 1
Для кожного дня, поданого в таблиці user_actions, розрахуйте такі показники:
- Загальна кількість замовлень.
- Число перших замовлень (замовлень, зроблених користувачами вперше).
- Число замовлень нових користувачів (замовлень, зроблених користувачами того ж дня, коли вони вперше скористалися сервісом).
- Частку перших замовлень у кількості замовлень (частку п.2 в п.1).
- Частку замовлень нових користувачів у кількості замовлень (частку п.3 в п.1).
Назвіть колонки з показниками відповідно orders, first_orders, new_users_orders, first_orders_share, new_users_orders_share. Колонку з датами назвіть date. Простежте, щоб у всіх випадках кількість замовлень була виражена цілим числом. Усі показники із частками необхідно виразити у відсотках. При розрахунку часток округляйте значення до двох знаків після коми.
Результат має бути відсортований за зростанням дати.
Поля в результуючій таблиці: date, orders, first_orders, new_users_orders, first_orders_share, new_users_orders_share
Пояснення:
При розрахунку числа перших замовлень враховуйте, що у кожного користувача може бути лише одне перше замовлення (що цілком логічно).
При розрахунку числа замовлень нових користувачів враховуйте, що у свій перший день кожен новий користувач міг як зробити відразу кілька замовлень, так і не здійснити жодного.
У всіх випадках при розрахунку числа замовлень враховуйте лише фактично здійснені замовлення, скасовані замовлення не враховуйте.
Не забувайте при діленні заздалегідь приводити значення до потрібного типу даних.
Підказка на випадок, якщо зовсім не виходить
Для вирішення завдання спочатку необхідно для кожного дня окремо розрахувати загальну кількість замовлень, кількість перших замовлень і кількість замовлень нових користувачів, а потім об’єднати отримані таблиці в одну і порахувати всі відносні показники. Для розрахунку числа перших замовлень для кожного користувача потрібно знайти дату оформлення першого нескасованого замовлення і потім здійснити групування за датою, порахувавши для кожного дня кількість користувачів, які зробили перше замовлення. Для розрахунку числа замовлень нових користувачів спочатку потрібно для кожного користувача знайти дату здійснення першої дії, а потім доповнити цю таблицю даними про кількість замовлень, зроблених користувачем свого першого дня. Це можна зробити, приєднавши до таблиці з датами перших дій таблицю із загальною кількістю замовлень на кожну дату для кожного користувача. Зверніть увагу, що в цій таблиці для деяких користувачів можуть бути відсутні дати здійснення першої дії оскільки користувач міг скасувати замовлення і фактично не здійснити жодної покупки свого першого дня. Після об’єднання таблиць для таких днів із пропущеними значеннями слід вказати кількість замовлень рівним 0. Це можна зробити, наприклад, за допомогою функції COALESCE.
Завдання 2
Оцінимо навантаження на наших кур’єрів і дізнаємося, скільки в середньому замовлень та користувачів припадає на кожного з них.
На основі даних у таблицях user_actions, courier_actions та orders для кожного дня розрахуйте такі показники:
- Кількість користувачів, що оплатили замовлення на одного активного кур’єра.
- Кількість замовлень на одного активного кур’єра.
Колонки з показниками назвіть відповідно users_per_courier та orders_per_courier. Колонку з датами назвіть date. Під час розрахунку показників округляйте значення до двох знаків після коми.
Результуюча таблиця має бути відсортована за зростанням дати.
Поля в результуючій таблиці: date, users_per_courier, orders_per_courier
Пояснення:
Користувачів, що оплатили замовлення, як і раніше, вважаємо тих користувачів, які в даний день оформили хоча б одне замовлення, яке надалі не було скасовано.
Кур’єрів вважаємо активними, якщо в даний день вони прийняли хоча б одне замовлення, яке було доставлено (можливо вже наступного дня), або доставили будь-яке замовлення.
У розрахунках враховуйте лише нескасовані замовлення.
Не забувайте при діленні заздалегідь наводити значення до потрібного типу даних.
Підказка на випадок, якщо зовсім не виходить
Для вирішення завдання спочатку необхідно за допомогою групування та агрегації розрахувати кількість користувачів, кур’єрів та замовлень на кожну дату, а потім об’єднати дані в одну таблицю та провести всі необхідні розрахунки.
Завдання 3
- Збережіть отримані результати в окремі представлення в BigQuery.
- Підключіться до Looker Studio та побудуйте візуалізацію для отриманих даних.
- Збережіть створену візуалізацію в окремий дашборд.
- Поділіться посиланням на дашборд.
Підказка щодо очікуваної візуалізації
Для Завдання 1 та Завдання 2 очікується використання лінійного графіку.
Але Ви можете запропонувати свої варіанти візуалізацій, якщо вони вважаєте, що вони краще підходять для відображення отриманих даних.
Завдання 1
На основі даних у таблиці courier_actions для кожного дня розрахуйте, за скільки хвилин у середньому кур’єри доставляли свої замовлення.
Назвіть колонку з показником minutes_to_deliver. Колонку з датами назвіть date. При розрахунку середнього часу доставки округляйте кількість хвилин до цілих значень. Враховуйте лише доставлені замовлення, скасовані замовлення не враховуйте.
Результуюча таблиця має бути відсортована за зростанням дати.
Поля в результуючій таблиці: date, minutes_to_deliver
Пояснення:
Для вирішення завдання вам, можливо, доведеться згадати, як визначити кількість хвилин, що містяться в інтервалі часу.
Деякі замовлення оформляють одного дня, а доставляють вже наступного. При розрахунку середнього часу доставки в якості днів, для яких рахується середнє, використовуйте дні фактичної доставки замовлень.
Підказка на випадок, якщо зовсім не виходить
Для вирішення завдання спочатку необхідно за допомогою групування та агрегації для кожного замовлення розрахувати час прийняття замовлення кур’єром (мінімальна позначка часу) та час доставки (максимальна позначка часу), обчислити різницю між цими значеннями та перевівши їх за хвилини. Потім можна провести групування за датою доставки та обчислити середній час доставки у кожний із днів.
Завдання 2
На основі даних у таблиці orders для кожної години на добу розрахуйте такі показники:
- Число успішних (доставлених) замовлень.
- Число скасованих замовлень.
- Частку скасованих замовлень у загальній кількості замовлень (cancel rate).
Колонки з показниками назвіть відповідно successful_orders, canceled_orders, cancel_rate. Колонку із годиною оформлення замовлення назвіть hour. При розрахунку частки скасованих замовлень округляйте значення до трьох знаків після коми.
Результуюча таблиця має бути відсортована за зростанням колонки з годиною оформлення замовлення.
Поля в результуючій таблиці: hour, successful_orders, canceled_orders, cancel_rate
Підказка на випадок, якщо зовсім не виходить
Для вирішення завдання можна спочатку за допомогою групування за датою та агрегацією окремо розрахувати кількість успішних та скасованих замовлень, потім об’єднати дві таблиці в одну за датою і потім розрахувати cancel_rate.
Завдання 3
- Збережіть отримані результати в окремі представлення в BigQuery.
- Підключіться до Looker Studio та побудуйте візуалізацію для отриманих даних.
- Збережіть створену візуалізацію в окремий дашборд.
- Поділіться посиланням на дашборд.
Підказка щодо очікуваної візуалізації
Для Завдання 1 очікується використання лінійного графіку, а для Завдання 2 - комбінованої діаграми.
Але Ви можете запропонувати свої варіанти візуалізацій, якщо вони вважаєте, що вони краще підходять для відображення отриманих даних.