Тестовое задание
на продуктового аналитика
Мегафон
Изменение параметров для всех абонентов тарифного плана "Без переплат. Минимум": абонентская плата увеличена с 350 руб. в месяц до 370 руб. в месяц, объем минут в рамках пакета увеличен с 300 до 450, объем смс в рамках пакета увеличен с 300 до 450, объем интернет трафика в рамках пакета увеличен с 2 ГБ до 4 ГБ.
Форма расчета может быть любой.
Какие методы прогнозирования вы еще знаете и в каких случаях их можно использовать?
ARPU - средний счет на одного абонента
T1
id | val |
1 | A |
2 | B |
2 | C |
3 | D |
5 | E |
T2
id | val |
1 | A |
2 | B |
2 | C |
2 | E |
4 | F |
Нужно расписать в формате таблицы, что вернет запрос select * from t1 left join t2 on t1.id=t2.id
sales_table
date | city | sales |
1/1/2018 | moscow | 10 |
1/1/2018 | spb | 20 |
2/1/2018 | moscow | 30 |
2/1/2018 | spb | 40 |
3/1/2018 | moscow | 50 |
3/1/2018 | spb | 60 |
… |
Результат должен выглядеть так:
date | city | sales | running_total |
1/1/2018 | moscow | 10 | 10 |
1/1/2018 | spb | 20 | 20 |
2/1/2018 | moscow | 30 | 40 |
2/1/2018 | spb | 40 | 60 |
3/1/2018 | moscow | 50 | 90 |
3/1/2018 | spb | 60 | 120 |
… |
transact_table
transact_table | ||
дата | сумма транзакции | валюта |
date | sum | currency |
12/31/2016 | 10 | usd |
1/1/2017 | 20 | usd |
1/2/2017 | 30 | usd |
1/3/2017 | 40 | usd |
1/4/2017 | 50 | usd |
1/5/2017 | 60 | usd |
1/6/2017 | 70 | usd |
… |
exchange_rub_table
дата | курс конвертации в рубли | валюта |
date | exchange_rate | currency |
12/30/2016 | 55,2 | usd |
12/31/2016 | 55,4 | usd |
1/10/2017 | 54,3 | usd |
1/11/2017 | 54,2 | usd |
… |
BILLS
название поля | month_start_date | subs_id | revenue |
описание | Дата начала месяца | ID абонента | выручка за месяц |
примеры значений | 1/1/2016 | 1234567 | 500 |
2/1/2016 | 2345678 | 675 | |
… | … | … |
SUBSCRIBERS
название поля | filial_id | subs_id | rate_plan_id | segment |
описание | ID филиала | ID абонента | ID тарифа | название сегмента |
примеры значений | 1 | 1234567 | 44 | B2B |
2 | 2345678 | 554 | B2C | |
… | … | … | … |
FILIALS
название поля | filial_id | filial_name |
описание | ID филиала | название филиала |
примеры значений | 1 | Столичный |
2 | Уральский | |
… | … |
1. Напишите SQL-запрос, возвращающий суммы выручки по филиалам и сегментам за январь 2017 года.
2. Напишите SQL-запрос, возвращающий суммы выручки по сегментам отдельными показателями по B2C и B2B в следующем виде:
Месяц | Филиал | Выручка B2C | Выручка B2B |
Название поля | report_month | subs_id | status | last_transact_date |
Описание | Месяц среза | ID абонента | Статус | Дата последней транзакции |
Пример значения | 4/1/2018 | 29833104 | закрыт | 4/24/2018 |
4/1/2018 | 2349842 | действующий | 1/21/2018 | |
… |
В таблице содержится информация по абоненту (и статус, и дата транзакции) на конец месяц (дата в поле "месяц среза" проставляется первым числом отчетного периода).
Дублей нет. Одна строка – один абонент.
Статус абонента в данной таблице может быть двух видов: "действующий" и "закрыт".
Если абонент расторгает контракт в месяце среза, то на конец месяца он будет со статусом "закрыт", в следующем месяце данного абонента не будет в таблице.
Абонент считается активным, если в текущем месяце у него была транзакция, и на конец месяца он действующий.
Оттоком активной базы называется количество абонентов, которые из активной базы перешли в неактивную в отчетном периоде.
Необходимо написать запрос, который вернет кол-во абонентов, ушедших в отток из активной базы за месяц.
- готовишься к СОБЕСАМ на продуктового аналитика?Мой онлайн-интенсив по подготовке к собеседованиям поможет тебе освоить навык прохождения всех этапов собеседования и получить оффер на 30% больше и в 2 раза быстрееУзнать подробнее
