skrald/showcase-vitech

showcase-vitech

SQL-1

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

Данные были подготовлены в следующем виде:

CREATE TABLE #measure_history (
	month_code		INT,
	measure_name	VARCHAR(20),
	measure_value	Decimal(18,4)
);

INSERT INTO #measure_history (month_code, measure_name, measure_value) VALUES
	(202305,'quantity', 100),
	(202305,'sale',   12312440.00 ),
	(202305,'margin', 2462488.00),
	(202305,'client', 10),
	(202306,'quantity', 110),
	(202306,'sale', 13814557.68),
	(202306,'margin', 2762911.536),
	(202306,'client', 15),
	(202307,'quantity', 135),
	(202307,'sale', 17293314.4776),
	(202307,'margin', 3458662.8955),
	(202307,'client', 20),
	(202308,'quantity', 150),
	(202308,'sale', 19599089.74),
	(202308,'margin', 3919817.95),
	(202308,'client',25);

Итоговая форма должна была быть:

Описание:

• quantity – кол-во реализованного товара,
• sale – сумма реализованного товара,
• margin – маржа реализованного товара,
• client – кол-во клиентов купивших товар

Требования:

a) Предоставить решение как с использованием «group by», так и через pivot
b) Дополнительно вывести столбец с месячным приростом продаж

Решение в файле sql-1

Дополнительно ответить на вопросы:
a) Какова динамика изменения маржинальности?

Маржинальность остаётся неизменной (20% выручки) - этот показатель отвязан от масштаба как при сравнении удельных показателей выручки / прибыли, так и тоталов по ним

b) Имеет ли место быть инфляция? Если да, то переложил ли ее продавец на конечного покупателя?

Однозначно можно делать вывод о постоянном влиянии внешнего ценового фактора (про инфляцию можно говорить при доп.вводных, например, динамике закупок / прямой себестоимости).
Бремя этого фактора продавец переложил на конечного покупателя, так как его доля прибыли не изменилась.

SQL-2

Компания продает два товара:

• станок ЧПУ (id_sku = 1) и
• перфоратор (id_sku = 2).

Медианные продажи в день по станку – 2 шт., перфоратор – 7 шт.
Остаток на сегодня: станки – 5 шт., перфоратор – 0 шт.

Есть график поставок:

id_sku	supply_id	date_supply	quantity
1	2	01.09.2023	10
1	4	03.09.2023	2
1	6	25.09.2023	3
1	8	25.10.2023	8
1	10	10.11.2023	11
2	12	07.09.2023	4
2	14	10.09.2023	33
2	16	11.10.2023	55
2	18	11.11.2023	12

CREATE TABLE #plan_supply (
	id_sku		INT,
	supply_id	INT,
	date_supply	date,
	quantity INT
);

INSERT INTO #plan_supply (id_sku, supply_id, date_supply,quantity) VALUES
	(1,2,'2023.09.01',10),
	(1,4,'2023.09.03',2),
	(1,6,'2023.09.25',3),
	(1,8,'2023.10.25',8),
	(1,10,'2023.11.10',11),
	(2,12,'2023.09.07',4),
	(2,14,'2023.09.10',33),
	(2,16,'2023.10.11',55),
	(2,18,'2023.11.11',12)
;

Требуется:

1. Посредством SQL скриптов предоставить таблицу с расчетом остатков каждого из товаров на каждый день с 1 сентября по 30 ноября 2023

Решение в файле sql-2

SQL-3

Написать скрипт по созданию таблицы в БД «DWH», схеме «Reporting»
Название полей и таблицы придумать самостоятельно.
Тип данных и атрибуты\ограничения выбрать самостоятельно исходя из описания.
Таблица будет содержать информацию по контрагенту.
Одному контрагенту строго соответствует одна запись.

Поля:

1. ID клиента (пример: 1,2,3,4)
2. ФИО клиента
3. Фамилия
4. Имя
5. Отчество (если отчества нет, то должно быть значение «Нет отчества»)
6. Широта адреса
7. Долгота адреса
8. Общее кол-во заказов
9. Сумма покупок (два знака после запятой)
10. Дата и время регистрации клиента на сайте
11. Логин
12. Пароль
13. Дата и время создания строки (с текущим временем по умолчанию)
14. Дата и время обновления строки (с текущим временем по умолчанию)

Решение в файле sql-3

Проверка навыков POWER BI

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

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

«Рейтинг менеджера» = «ранг по sale» * 0.75 + «ранг по бонусу» * 0.25
«ранг по sale» и «ранг по бонусу» — это ранжирование % прироста sale и бонуса последнего закрытого месяца к предыдущему месяцу (если сейчас август, то ранжируем прирост июля к июню).
Менеджер участвует в ранжировании только после того, как с момента начала его работа прошло 3 календарных месяца не считая месяца выхода.
То есть, если менеджер вышел 15 августа, то в ранжировании он начнет участвовать с декабря (будет браться прирост ноября к октябрю)

Исходные таблицы:

1. Dim_manager (справочник менеджеров)
   • manager_id – id менеджера продаж
   • name – фамилия менеджера
   • start_work – дата начала работы

2. Mes_fact (таблица фактов продаж по месяцам):
   • month_code – месяц продаж в формате YYYYMM
   • manager_id – id менеджера продаж
   • sale – сумма продаж в руб.
   • bonus – сумма бонусов от производителей в руб.

Требуется отразить в дашборде следующую информацию:
• ТОП 3 лучших менажеров по рейтингу
• ТОП 3 худших менеджера по рейтингу
• Динамику изменения рейтинга менеджеров с начала года
• Реализовать таблицу вида:

Менеджер / Суммарный показатель за год	Продажи, руб.	Бонус, руб.	Ср. рейтинг менеджера
Иванов	1000000	2000000	1,3
…	…	…	…

Ср. рейтинг менеджера – среднемесячный рейтинг менеджера по тем месяцами, где он участвовал в ранжировании.

(WIP)