Коллеги, официально сообщаю, что сотрудник В** Л**, начиная с 20.09.2022 не планируется к привлечению на проект 585 * Золотой в связи с завершением сотрудником всех поставленных задач.
Благодарим Л** за ответственное выполнение задач и оперативное вовлечение в работу в ходе ОПЭ. Сотрудник готов к привлечению на другие проекты.

Имеются следующие таблицы:

Справочник акций (содержит несколько сотен записей)
Integer
Идентификатор акции (Primary key)
Varchar(12)
Код акции
Varchar(30)
Название акции

Котировки акций (содержит котировки за несколько лет)
Integer
Идентификатор акции
Date
Дата котировки (даты для одной акции могут быть с пропусками, если на заданную дату котировки нет, то считается, что котировка с предыдущей даты не изменилась)
Float
Рыночная котировка на указанную дату

Сделки покупки и продажи акций (содержит около миллиона записей)
Integer
Идентификатор сделки
Integer
Идентификатор акции
Date
Дата сделки
Char(1)
Направление сделки (B – покупка, S – продажа)
Integer
Количество бумаг
Float
Цена за штуку

Позиция по акциям
Integer
Идентификатор акции
Integer
Количество бумаг в позиции
Float
Средняя цена приобретения позиции за штуку – определяется по методу LIFO
Float
Реализованный финансовый результат
Float
Нереализованный финансовый результат (рассчитывается как разница между рыночной ценой бумаги и ценой приобретения умноженная на количество бумаг в позиции)

Задание
1. Написать скрипты для создания указанных таблиц (со всеми необходимыми constraints и индексами), а также тех таблиц, которые еще могут потребоваться для решения задачи.
2. Разработать процедуру, которая получает на вход один параметр – дату позиции. Процедура должна рассчитать по сделкам позицию по методу LIFO для каждой акции на указанную дату и сложить ее в таблицу «Позиция по акциям».
3. Допущения:
a. Считаем, что набор сделок и котировки во время работы процедуры не меняются
b. Таблицу «Позиция по акциям» можно чистить каждый раз перед использованием
4. Позиция может быть как короткой (продали бумаг больше, чем купили до этого), так и длинной.
5. Внутри дня сделки сортируются по идентификатору

Тестовое задание можно выполнять на любом из трех языков: Sybase Transact SQL, MS-SQL Transact SQL, Oracle PL/SQL.

В результате выполнения задания должен быть подготовлен один sql-файл со всеми скриптами для создания таблиц, индексов, хранимых процедур; дампы базы данных прикладывать не надо.

Метод LIFO (Last In First Out)
При продаже ценных бумаг в первую очередь продаются те из оставшихся, что были куплены самыми последними, например:

(1) Купили 1 марта 10 штук по 5 рублей за штуку
(2) Купили 2 марта 15 штук по 6 рублей за штуку
(3) Купили 3 марта 5 штук по 4 рубля за штуку
(4) Продали 4 марта 17 штук по 7 рублей за штуку

В результате продажи в позиции на вечер 4 марта останутся сделки:
(1) 10 штук по 5 рублей
(2) 3 штуки по 6 рублей

Итого 13 штук по средней цене (10*5 + 3*6) / 13 = 5.23076923
Реализованный финансовый результат 5 * (7 - 4) + 12 * (7 – 6) = 27

После этого
(5) Продали 5 марта 20 штук по 6 рублей

В результате продажи в позиции останутся сделки:
(5) -7 штук по 6 рублей

Реализованный финансовый результат от этой продажи 13 * (6 – 68/13) = 10
Итоговый фин.рез. 37 рублей

(6) Продали 6 марта 10 штук по 7 рублей
(7) Купили 10 марта 15 штук по 8 рублей за штуку

Реализованный финансовый результат от этой покупки 10 * (7 - 8) + 5 * (6 - 8) = -20
Итоговый фин.рез. 17 рублей

В позиции остались -2 штуки по 6 рублей

Пусть рыночная цена по этой акции на 11 марта составит 9 рублей за штуку.

Общая позиция на 11 марта такова:
Количество бумаг в позиции
-2
Средняя цена приобретения
6
Реализованный финансовый результат
17
Нереализованный финансовый результат
-6