тестовое задание на собеседовании / Microsoft SQL Server

Дедофорум Z

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [закрыт для гостей] / тестовое задание на собеседовании

12 сообщений из 12, страница 1 из 1

тестовое задание на собеседовании

#69356

Артефакт

Участник

Сообщения: 10 663

Рейтинг: 1644 / 19

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:02

| Ответить | Цитировать | Написать

тестовое задание на собеседовании

#69405

Артефакт

Участник

Сообщения: 10 663

Рейтинг: 1644 / 19

у покупателя есть скидочная карта
он может ее потерять или обменять на новую
старая карта поступает в оборот и может быть выдана другому покупателю
поменять можно 5 карт в год
вывести карты по которым превышен лимит и дату с которой можно получить новую карту

...

Рейтинг:

0 / 0

21.06.2022, 19:53

| Ответить | Цитировать | Написать | Ответы

тестовое задание на собеседовании

#69585

PaNik

Участник
[игнорирует гостей]

Откуда: 50

Сообщения: 14 700

Рейтинг: 2907 / 54

Артефакт 21.06.2022, 19:53

[игнорируется]

69405

1. "поменять можно 5 карт в год" - карта с одним номером может выдаваться не более 5 раз в год (календарный?) ?
2. "вывести карты по которым превышен лимит ..." - т.е. которые выдавались более 5 раз в течение года?
3. "... и дату с которой можно получить новую карту" - типа 01.01 следующего года?

...

Рейтинг:

0 / 0

21.06.2022, 22:26

| Ответить | Цитировать | Написать | Ответы

тестовое задание на собеседовании

#69733

PaNik

Участник
[игнорирует гостей]

Откуда: 50

Сообщения: 14 700

Рейтинг: 2907 / 54

PaNik 21.06.2022, 22:26

[игнорируется]

69585

Артефакт 21.06.2022, 19:53

[игнорируется]

69405

Артефакт

...

Рейтинг:

0 / 0

22.06.2022, 00:36

| Ответить | Цитировать | Написать

тестовое задание на собеседовании

#70956

Артефакт

Участник

Сообщения: 10 663

Рейтинг: 1644 / 19

календарный год
5 карт (больше 5 не может быть)
дата год с момента выдачи первой карты
точно не помню уже
код сохранился, а условия нет.

...

Изменено: 23.06.2022, 14:27 - Артефакт

Рейтинг:

0 / 0

23.06.2022, 14:25

| Ответить | Цитировать | Написать

тестовое задание на собеседовании

#71479

Артефакт

Участник

Сообщения: 10 663

Рейтинг: 1644 / 19

Задача 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, 07:20

| Ответить | Цитировать | Написать | Ответы

тестовое задание на собеседовании

#71699

PaNik

Участник
[игнорирует гостей]

Откуда: 50

Сообщения: 14 700

Рейтинг: 2907 / 54

Артефакт 24.06.2022, 07:20

[игнорируется]

71479

Код: 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 'Данные не идентичны'

Код: 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

Код: 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 -- значение курса
                   )

Код: 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

Код: 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

Код: 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))

Код: 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

24.06.2022, 15:40

| Ответить | Цитировать | Написать

тестовое задание на собеседовании

#185874

Артефакт

Участник

Сообщения: 10 663

Рейтинг: 1644 / 19

Цитата

[игнорируется]

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

Справочник акций (содержит несколько сотен записей)
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, 09:00

| Ответить | Цитировать | Написать | Ответы

тестовое задание на собеседовании

#186292

Дед-Папыхтет Привилегированный пользователь

Участник

Сообщения: 4 631

Рейтинг: 1327 / 13

Артефакт 16.10.2022, 09:00

[игнорируется]

185874

Цитата

[игнорируется]

Еббадд здесь на пару часов погрязнуть

...

Рейтинг:

0 / 0

16.10.2022, 15:13

| Ответить | Цитировать | Написать

тестовое задание на собеседовании

#189324

cat2

Участник
[игнорирует гостей]

Сообщения: 10 725

Рейтинг: 2656 / 70

Гесты и игнорируемые идут по CSS

Артефакт 16.10.2022, 09:00

[игнорируется]

185874

Цитата

[игнорируется]

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

Не оптимальная структура

"Направление сделки (B – покупка, S – продажа)" - лишнее
Достаточно что бы "Количество бумаг" было положительное или отрицательное

...

Рейтинг:

0 / 0

18.10.2022, 20:50

| Ответить | Цитировать | Написать

тестовое задание на собеседовании

#189326

cat2

Участник
[игнорирует гостей]

Сообщения: 10 725

Рейтинг: 2656 / 70

Гесты и игнорируемые идут по CSS

Артефакт 16.10.2022, 09:00

[игнорируется]

185874

Цитата

[игнорируется]

b. Таблицу «Позиция по акциям» можно чистить каждый раз перед использованием

Если можно "чистить", то она и не нужна

...

Рейтинг:

1 / 0

Нравится: Sparrow

18.10.2022, 20:55

| Ответить | Цитировать | Написать | Ответы

тестовое задание на собеседовании

#189373

Sparrow

Модератор форума

Откуда: Красноярск

Сообщения: 4 850

Рейтинг: 649 / 13

cat2 18.10.2022, 20:55

[игнорируется]

189326

Артефакт 16.10.2022, 09:00

[игнорируется]

185874

Цитата

[игнорируется]

b. Таблицу «Позиция по акциям» можно чистить каждый раз перед использованием

Если можно "чистить", то она и не нужна

Вообще команда Delete самая дорогая в SQL. И не только там , в программировании освобождение памяти дороже выделения.

...

Рейтинг:

0 / 0

18.10.2022, 21:43

| Ответить | Цитировать | Написать

12 сообщений из 12, страница 1 из 1

Форумы / Microsoft SQL Server [закрыт для гостей] / тестовое задание на собеседовании

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=17&msg=70956&tid=2485]:	0ms
get settings:	26ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	41ms
get topic data:	12ms
get forum data:	3ms
get page messages:	858ms
get tp. blocked users:	2ms
others:	34ms

total:	997ms