Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [закрыт для гостей] / тестовое задание на собеседовании / 12 сообщений из 12, страница 1 из 1
21.06.2022, 19:02
    #69356
Артефакт
АртефактПривилегированный пользователь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
old_card,new_card,dt
111 555 2020-01-09 00:00:00.000
222 223 2020-02-10 00:00:00.000
333 334 2020-03-11 00:00:00.000
444 222 2020-04-12 00:00:00.000
555 666 2020-05-12 00:00:00.000
666 777 2020-06-13 00:00:00.000
777 888 2020-07-14 00:00:00.000
888 000 2020-08-15 00:00:00.000
999 333 2020-09-16 00:00:00.000
223 111 2020-10-16 00:00:00.000
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
declare  @T table (id int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, old_card varchar(10),new_card varchar(10),dt datetime)
declare  @C table (Card varchar(10),limit int,dtLast datetime,dtFirst datetime)
declare @card varchar(10),@id int,@limit int,@id2 int,@dtLast datetime,@dtFirst datetime
set @id2=1

insert into @T
select old_card,new_card,dt from ChangeCard order by dt

WHILE @id2<=(select top 1 id from @T order by id desc)
Begin
  set @limit=1
  select @card=new_card,@id=id,@dtFirst=dt from @T where id=@id2
  WHILE @@ROWCOUNT<>0
    select top 1 @id=id,@limit=@limit+1,@card=new_card,@dtLast=dt from @T where id>@id and old_card=@card order by dt

  insert into @C
  Values(@card,@limit,@dtLast,@dtFirst)

  set @id2=@id2+1
End

select card,limit,DATEADD(year,1,dtFirst) ДатаЗамены from @C
where limit>=5
...
Изменено: 21.06.2022, 19:13 - Артефакт
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.06.2022, 19:53
    #69405
Артефакт
АртефактПривилегированный пользователь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
у покупателя есть скидочная карта
он может ее потерять или обменять на новую
старая карта поступает в оборот и может быть выдана другому покупателю
поменять можно 5 карт в год
вывести карты по которым превышен лимит и дату с которой можно получить новую карту
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать | Ответы  
21.06.2022, 22:26
    #69585
PaNik
PaNikПривилегированный пользователь
Участник
[игнорирует гостей]
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
Артефакт  21.06.2022, 19:53
[игнорируется]
69405
у покупателя есть скидочная карта
он может ее потерять или обменять на новую
старая карта поступает в оборот и может быть выдана другому покупателю
поменять можно 5 карт в год
вывести карты по которым превышен лимит и дату с которой можно получить новую карту
1. "поменять можно 5 карт в год" - карта с одним номером может выдаваться не более 5 раз в год (календарный?) ?
2. "вывести карты по которым превышен лимит ..." - т.е. которые выдавались более 5 раз в течение года?
3. "... и дату с которой можно получить новую карту" - типа 01.01 следующего года?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать | Ответы  
22.06.2022, 00:36
    #69733
PaNik
PaNikПривилегированный пользователь
Участник
[игнорирует гостей]
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
PaNik  21.06.2022, 22:26
[игнорируется]
69585
Артефакт  21.06.2022, 19:53
[игнорируется]
69405
у покупателя есть скидочная карта
он может ее потерять или обменять на новую
старая карта поступает в оборот и может быть выдана другому покупателю
поменять можно 5 карт в год
вывести карты по которым превышен лимит и дату с которой можно получить новую карту
1. "поменять можно 5 карт в год" - карта с одним номером может выдаваться не более 5 раз в год (календарный?) ?
2. "вывести карты по которым превышен лимит ..." - т.е. которые выдавались более 5 раз в течение года?
3. "... и дату с которой можно получить новую карту" - типа 01.01 следующего года?
Артефакт
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.06.2022, 14:25
    #70956
Артефакт
АртефактПривилегированный пользователь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
календарный год
5 карт (больше 5 не может быть)
дата год с момента выдачи первой карты
точно не помню уже
код сохранился, а условия нет.
...
Изменено: 23.06.2022, 14:27 - Артефакт
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.06.2022, 07:20
    #71479
Артефакт
АртефактПривилегированный пользователь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
Задача 1: Необходимо написать запрос, который позволит понять, идентичны ли данные в двух таблицах. Порядок хранения данных в таблицах значения не имеет.
create table t1(a number, b number);

create table t2(a number, b number);
Пример данных:
T1:
a b
1 1
2 2
2 2
3 3
4 4
T2:
a b
1 1
2 2
3 3
3 3
4 4
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
IF NOT EXISTS(
select a,b from (
  select a,b,row_number() over(partition by a,b order by a,b) rn from t1
  EXCEPT
  select a,b,row_number() over(partition by a,b order by a,b) rn from t2) t
UNION ALL
select a,b from (
  select a,b,row_number() over(partition by a,b order by a,b) rn from t2
  EXCEPT
  select a,b,row_number() over(partition by a,b order by a,b) rn from t1) t
)
PRINT 'Данные идентичны'
ELSE
PRINT 'Данные не идентичны'
Задача 2: Имеется таблица без первичного ключа. Известно, что в таблице имеется задвоение данных. Необходимо удалить дубликаты из таблицы.
create table t (a number, b number);

Пример данных:
a b
1 1
2 2
2 2
3 3
3 3
3 3
Требуемый результат:
a b
1 1
2 2
3 3
Код: SQL
1.
2.
with t0 as (select a,b,row_number() over(partition by a,b order by a,b) rn from t)
delete from t0 where rn>1
Задача 3: Есть таблица с данными в виде дерева. Необходимо написать запрос для получения дерева от корневого узла, узел 5 и все его потомки не должны попасть в результат, нужно вывести для каждого узла имя его родителя, данные отсортировать в порядке возрастания ID с учетом иерархии
create table t (id number, pid number, nam varchar2(255));

Пример данных:
ID PID NAM
1 Корень
2 1 Узел2
3 1 Узел3
4 2 Узел4
5 4 Узел5
6 5 Узел6
7 4 Узел7

Требуемый результат:
ID PID NAM PARENT_NAM
1 Корень
2 1 Узел2 Корень
4 2 Узел4 Узел2
7 4 Узел7 Узел4
3 1 Узел3 Корень
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with t1 (id,pid) as (
  select id,id pid from t
  union all
  select t.id, t1.pid from t inner join t1 on t.pid=t1.id)
select id,pid,nam,(select nam from t t2 where t2.id=t.pid) PARENT_NAM from t
where id not in (select id from t1 where pid=5)
order by (select count(*) from t1 t2 where t2.pid = t.id) desc
Задача 4: Имеется таблица курсов валют следующей структуры:
create table rates(curr_id number, -- ид валюты
                   date_rate DATE, -- дата курса
                   rate NUMBER -- значение курса
                   )
Курс валюты устанавливается не на каждую календарную дату.
Уникальный ключ: curr_id + date_rate.

Напишите запрос, который покажет действующее значение курса заданной валюты на любую заданную календарную дату.

Пример данных:
CURR_ID DATE_RATE RATE
1 01.01.2010 30
2 01.01.2010 40
1 02.01.2010 32
1 05.01.2010 33
2 10.01.2010 41
2 15.01.2010 42
Требуемый результат:
Для валюты 1 на 03.01.2010 получить курс 32
Для валюты 2 на 10.01.2010 получить курс 41
Код: SQL
1.
2.
3.
4.
5.
declare @curr_id int=2,@date_rate date='20100110'

select top 1 rate from rates
where curr_id=@curr_id and date_rate<=@date_rate
order by date_rate desc
Задача 5: Имеется таблица в данными по платежным документам. Необходимо написать запрос, который выведет все документы того типа, которого за все время было по сумме больше всего. Если таких типов несколько, то вывести все такие типы. Для каждой строки результата вывести промежуточную сумму платежей данного типа от самого раннего до текущеиго платежа включительно.
create table payments (id number, pay_type NUMBER, pay_date date, pay_sum number);
ID PAY_TYPE PAY_DATE PAY_SUM
1 1 01.01.2012 100
2 1 02.01.2012 200
3 1 03.01.2012 300
4 1 01.02.2012 400
5 1 01.02.2012 500
6 2 01.01.2012 600
7 2 01.02.2012 700
8 2 01.04.2012 800
9 2 01.05.2012 900
10 2 01.06.2012 1000
11 3 10.01.2012 1100
12 3 01.03.2012 1200
13 3 01.05.2012 1300
14 3 05.05.2012 1400
15 3 01.06.2012 1500
Требуемый результат:
ID PAY_TYPE PAY_DATE PAY_SUM SM
11 3 10.01.2012 1100 1100
12 3 01.03.2012 1200 2300
13 3 01.05.2012 1300 3600
14 3 05.05.2012 1400 5000
15 3 01.06.2012 1500 6500
Код: SQL
1.
2.
3.
4.
5.
6.
with p as (select  pay_type,sum(pay_sum) pay_type_pay_sum from payments group by pay_type),
pt as (select pay_type from p where pay_type_pay_sum=(select top 1 pay_type_pay_sum from p order by 1 desc))
select *,SUM(pay_sum) OVER(PARTITION BY pay_type
                ORDER BY pay_date,id
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) SM from payments where pay_type in (select pay_type from pt)
С оконной функцией проверить не смог т.к. у меня SQL2008 сделал нарастающий итог подзапросом
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
with p as (select  pay_type,sum(pay_sum) pay_type_pay_sum from payments
group by pay_type),
pt as (select pay_type from p
where pay_type_pay_sum=(select top 1 pay_type_pay_sum from p order by 1 desc))
select *,(select SUM(pay_sum) from payments p2
where p2.pay_type=p1.pay_type and p2.pay_date <= p1.pay_date and p2.id <=p1.id ) SM
 from payments p1 where pay_type in (select pay_type from pt)
 order by pay_type,id
Задача 6: По таблице из предыдущего примера написать запрос, который выведет данные общей суммой за каждый месяц по типам документов с итогами по каждому типу и общим итогом.
Требуемый результат:
PAY_TYPE MON SM
1 01.2012 600
1 02.2012 900
1 1500
2 01.2012 600
2 02.2012 700
2 04.2012 800
2 05.2012 900
2 06.2012 1000
2 4000
3 01.2012 1100
3 03.2012 1200
3 05.2012 2700
3 06.2012 1500
3 6500
12000
Код: SQL
1.
2.
3.
select  pay_type,convert(varchar(7),pay_date,120) mon,sum(pay_sum) pay_type_pay_sum
from payments
group by ROLLUP(pay_type,convert(varchar(7),pay_date,120))
Задача 7: Напишите генератор непрерывного интервала дат с 10.01.2013 по 10.02.2013 в виде запроса.
Требуемый результат:
DT
10.01.2013
11.01.2013
12.01.2013
13.01.2013
14.01.2013
15.01.2013
16.01.2013
17.01.2013
18.01.2013
19.01.2013
20.01.2013
21.01.2013
22.01.2013
23.01.2013
24.01.2013
25.01.2013
26.01.2013
27.01.2013
28.01.2013
29.01.2013
30.01.2013
31.01.2013
01.02.2013
02.02.2013
03.02.2013
04.02.2013
05.02.2013
06.02.2013
07.02.2013
08.02.2013
09.02.2013
10.02.2013
Код: SQL
1.
2.
3.
4.
5.
6.
with T(DT) as (
  select CAST('20130110' as date) DT
  union all
  select DATEADD(day,1,DT) DT from T where DT<'20130210')
select * from T
order by 1
...
Изменено: 24.06.2022, 07:22 - Артефакт
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать | Ответы  
24.06.2022, 15:40
    #71699
PaNik
PaNikПривилегированный пользователь
Участник
[игнорирует гостей]
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
Артефакт  24.06.2022, 07:20
[игнорируется]
71479
Задача 1: Необходимо написать запрос, который позволит понять, идентичны ли данные в двух таблицах. Порядок хранения данных в таблицах значения не имеет.
create table t1(a number, b number);

create table t2(a number, b number);
Пример данных:
T1:
a b
1 1
2 2
2 2
3 3
4 4
T2:
a b
1 1
2 2
3 3
3 3
4 4
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
IF NOT EXISTS(
select a,b from (
  select a,b,row_number() over(partition by a,b order by a,b) rn from t1
  EXCEPT
  select a,b,row_number() over(partition by a,b order by a,b) rn from t2) t
UNION ALL
select a,b from (
  select a,b,row_number() over(partition by a,b order by a,b) rn from t2
  EXCEPT
  select a,b,row_number() over(partition by a,b order by a,b) rn from t1) t
)
PRINT 'Данные идентичны'
ELSE
PRINT 'Данные не идентичны'
Задача 2: Имеется таблица без первичного ключа. Известно, что в таблице имеется задвоение данных. Необходимо удалить дубликаты из таблицы.
create table t (a number, b number);

Пример данных:
a b
1 1
2 2
2 2
3 3
3 3
3 3
Требуемый результат:
a b
1 1
2 2
3 3
Код: SQL
1.
2.
with t0 as (select a,b,row_number() over(partition by a,b order by a,b) rn from t)
delete from t0 where rn>1
Задача 3: Есть таблица с данными в виде дерева. Необходимо написать запрос для получения дерева от корневого узла, узел 5 и все его потомки не должны попасть в результат, нужно вывести для каждого узла имя его родителя, данные отсортировать в порядке возрастания ID с учетом иерархии
create table t (id number, pid number, nam varchar2(255));

Пример данных:
ID PID NAM
1 Корень
2 1 Узел2
3 1 Узел3
4 2 Узел4
5 4 Узел5
6 5 Узел6
7 4 Узел7

Требуемый результат:
ID PID NAM PARENT_NAM
1 Корень
2 1 Узел2 Корень
4 2 Узел4 Узел2
7 4 Узел7 Узел4
3 1 Узел3 Корень
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with t1 (id,pid) as (
  select id,id pid from t
  union all
  select t.id, t1.pid from t inner join t1 on t.pid=t1.id)
select id,pid,nam,(select nam from t t2 where t2.id=t.pid) PARENT_NAM from t
where id not in (select id from t1 where pid=5)
order by (select count(*) from t1 t2 where t2.pid = t.id) desc
Задача 4: Имеется таблица курсов валют следующей структуры:
create table rates(curr_id number, -- ид валюты
                   date_rate DATE, -- дата курса
                   rate NUMBER -- значение курса
                   )
Курс валюты устанавливается не на каждую календарную дату.
Уникальный ключ: curr_id + date_rate.

Напишите запрос, который покажет действующее значение курса заданной валюты на любую заданную календарную дату.

Пример данных:
CURR_ID DATE_RATE RATE
1 01.01.2010 30
2 01.01.2010 40
1 02.01.2010 32
1 05.01.2010 33
2 10.01.2010 41
2 15.01.2010 42
Требуемый результат:
Для валюты 1 на 03.01.2010 получить курс 32
Для валюты 2 на 10.01.2010 получить курс 41
Код: SQL
1.
2.
3.
4.
5.
declare @curr_id int=2,@date_rate date='20100110'

select top 1 rate from rates
where curr_id=@curr_id and date_rate<=@date_rate
order by date_rate desc
Задача 5: Имеется таблица в данными по платежным документам. Необходимо написать запрос, который выведет все документы того типа, которого за все время было по сумме больше всего. Если таких типов несколько, то вывести все такие типы. Для каждой строки результата вывести промежуточную сумму платежей данного типа от самого раннего до текущеиго платежа включительно.
create table payments (id number, pay_type NUMBER, pay_date date, pay_sum number);
ID PAY_TYPE PAY_DATE PAY_SUM
1 1 01.01.2012 100
2 1 02.01.2012 200
3 1 03.01.2012 300
4 1 01.02.2012 400
5 1 01.02.2012 500
6 2 01.01.2012 600
7 2 01.02.2012 700
8 2 01.04.2012 800
9 2 01.05.2012 900
10 2 01.06.2012 1000
11 3 10.01.2012 1100
12 3 01.03.2012 1200
13 3 01.05.2012 1300
14 3 05.05.2012 1400
15 3 01.06.2012 1500
Требуемый результат:
ID PAY_TYPE PAY_DATE PAY_SUM SM
11 3 10.01.2012 1100 1100
12 3 01.03.2012 1200 2300
13 3 01.05.2012 1300 3600
14 3 05.05.2012 1400 5000
15 3 01.06.2012 1500 6500
Код: SQL
1.
2.
3.
4.
5.
6.
with p as (select  pay_type,sum(pay_sum) pay_type_pay_sum from payments group by pay_type),
pt as (select pay_type from p where pay_type_pay_sum=(select top 1 pay_type_pay_sum from p order by 1 desc))
select *,SUM(pay_sum) OVER(PARTITION BY pay_type
                ORDER BY pay_date,id
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) SM from payments where pay_type in (select pay_type from pt)
С оконной функцией проверить не смог т.к. у меня SQL2008 сделал нарастающий итог подзапросом
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
with p as (select  pay_type,sum(pay_sum) pay_type_pay_sum from payments
group by pay_type),
pt as (select pay_type from p
where pay_type_pay_sum=(select top 1 pay_type_pay_sum from p order by 1 desc))
select *,(select SUM(pay_sum) from payments p2
where p2.pay_type=p1.pay_type and p2.pay_date <= p1.pay_date and p2.id <=p1.id ) SM
 from payments p1 where pay_type in (select pay_type from pt)
 order by pay_type,id
Задача 6: По таблице из предыдущего примера написать запрос, который выведет данные общей суммой за каждый месяц по типам документов с итогами по каждому типу и общим итогом.
Требуемый результат:
PAY_TYPE MON SM
1 01.2012 600
1 02.2012 900
1 1500
2 01.2012 600
2 02.2012 700
2 04.2012 800
2 05.2012 900
2 06.2012 1000
2 4000
3 01.2012 1100
3 03.2012 1200
3 05.2012 2700
3 06.2012 1500
3 6500
12000
Код: SQL
1.
2.
3.
select  pay_type,convert(varchar(7),pay_date,120) mon,sum(pay_sum) pay_type_pay_sum
from payments
group by ROLLUP(pay_type,convert(varchar(7),pay_date,120))
Задача 7: Напишите генератор непрерывного интервала дат с 10.01.2013 по 10.02.2013 в виде запроса.
Требуемый результат:
DT
10.01.2013
11.01.2013
12.01.2013
13.01.2013
14.01.2013
15.01.2013
16.01.2013
17.01.2013
18.01.2013
19.01.2013
20.01.2013
21.01.2013
22.01.2013
23.01.2013
24.01.2013
25.01.2013
26.01.2013
27.01.2013
28.01.2013
29.01.2013
30.01.2013
31.01.2013
01.02.2013
02.02.2013
03.02.2013
04.02.2013
05.02.2013
06.02.2013
07.02.2013
08.02.2013
09.02.2013
10.02.2013
Код: SQL
1.
2.
3.
4.
5.
6.
with T(DT) as (
  select CAST('20130110' as date) DT
  union all
  select DATEADD(day,1,DT) DT from T where DT<'20130210')
select * from T
order by 1
Спасибо!

1. долго тупил над формулировкой " ... данные отсортировать в порядке возрастания ID с учетом иерархии ..."
2. про ROLLUP вообще забыл, надо бы повторить
3. в 5-ой задаче с оконной функций order by бы добавить новсякей (ставь 2019-ый developer edition)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2022, 09:00
    #185874
Артефакт
АртефактПривилегированный пользователь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
Цитата 
[игнорируется]
Имеются следующие таблицы:

Справочник акций (содержит несколько сотен записей)
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать | Ответы  
16.10.2022, 15:13
    #186292
Дед-Папыхтет
Дед-ПапыхтетПривилегированный пользователь
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
Артефакт  16.10.2022, 09:00
[игнорируется]
185874
Цитата 
[игнорируется]
Имеются следующие таблицы:

Справочник акций (содержит несколько сотен записей)
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
Еббадд здесь на пару часов погрязнуть
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.10.2022, 20:50
    #189324
cat2
cat2Привилегированный пользователь
Участник
[игнорирует гостей]
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гесты и игнорируемые идут по CSS
тестовое задание на собеседовании
Артефакт  16.10.2022, 09:00
[игнорируется]
185874
Цитата 
[игнорируется]
Сделки покупки и продажи акций (содержит около миллиона записей)
Integer
Идентификатор сделки
Integer
Идентификатор акции
Date
Дата сделки
Char(1)
Направление сделки (B – покупка, S – продажа)
Integer
Количество бумаг
Float
Цена за штуку
Не оптимальная структура

"Направление сделки (B – покупка, S – продажа)" - лишнее
Достаточно что бы "Количество бумаг" было положительное или отрицательное
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.10.2022, 20:55
    #189326
cat2
cat2Привилегированный пользователь
Участник
[игнорирует гостей]
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гесты и игнорируемые идут по CSS
тестовое задание на собеседовании
Артефакт  16.10.2022, 09:00
[игнорируется]
185874
Цитата 
[игнорируется]
b. Таблицу «Позиция по акциям» можно чистить каждый раз перед использованием
Если можно "чистить", то она и не нужна
...
Рейтинг: 1 / 0
Нравится: Sparrow
| Ответить | Цитировать | Написать | Ответы  
18.10.2022, 21:43
    #189373
Sparrow
Sparrow ✓Привилегированный пользователь
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
тестовое задание на собеседовании
cat2  18.10.2022, 20:55
[игнорируется]
189326
Артефакт  16.10.2022, 09:00
[игнорируется]
185874
Цитата 
[игнорируется]
b. Таблицу «Позиция по акциям» можно чистить каждый раз перед использованием
Если можно "чистить", то она и не нужна
Вообще команда Delete самая дорогая в SQL. И не только там , в программировании освобождение памяти дороже выделения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [закрыт для гостей] / тестовое задание на собеседовании / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 495ms
Закрыть
start [/forum/topic.php?fid=17&mobile=1&tid=2485]:
 0ms
get settings:
 12ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 357ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 69ms
get tp. blocked users:
 2ms
others: 20ms
total:  495ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]