Тестовое задание на должность "Разработчик баз данных MS SQL"

Около двух недель назад мне на почту прислали тестовое задание на должность "Разработчик баз данных MS SQL". И не было бы этого поста если бы дьявол не скрывался в мелочах.

Задание я сделал и отправил. Через пару дней вспомнил, погуглил и нашёл подобное задание на форуме (чесн-слово было именно в таком хронологическом порядке). Оно было 1-в-1 как то что прислали мне за исключением названий и данных. Для наглядности я приведу данные к виду что на форуме и сравню результаты.

Как пишет invm на форуме: Стандартная задача на нарастающий итог

Задание:
Даны 2 таблицы:

  • @T1 (Id – ID клиента, Credit – размер кредита)
  • @T2 (Id – ID клиента, Sum – сумма покупки, Date – дата покупки, Name - наименование покупки).

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

Данные:

declare @t1 table (id int, credit numeric(18,2));
insert into @t1
values
 (1, 24);
 
declare @t2 table (id int, s numeric(18,2), dt datetime, name varchar(50));
insert into @t2
values
 (1, 5, '10.24.2005', 'хлеб'),
 (1, 9, '10.19.2005', 'молоко'),
 (1, 3, '10.22.2005', 'гречка'),
 (1, 8, '10.04.2005', 'колбаса'),
 (1, 6, '10.18.2005', 'огурец'),
 (1, 5, '09.29.2005', 'холодец');



Результат должен быть таким: Выводятся покупки в обратном хронологическом порядке пока позволяет кредит, а когда остаток кредита меньше следующей покупки выводится сам остаток. Как видно с колбасой так и случилось: на неё осталась всего одна единица кредита тогда как её стоимость 8.

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

Форум

select
	b.id, b.dt,
	case when a.credit >= c.s then b.s else d.cr end,
	b.name
from
	@t1 a 
	join @t2 b on b.id = a.id 
	cross apply (select sum(s) from @t2 where id = b.id and dt >= b.dt) c(s) 
	cross apply (select a.credit - (c.s - b.s)) as d(cr)
where
	d.cr >= 0
order by
	b.id, b.dt desc

Мой

;with cte(id, credit, dt, s, name, Row)
as
(
    select t1.id, t1.credit, t2.dt, t2.s, t2.name, ROW_NUMBER() OVER(order by t1.id, t2.dt desc) AS Row
    from @t1 t1
    left join @t2 t2 on t2.id=t1.id
)
select c.id, c.dt,
    (case when c.credit-a.sum_s>0 then c.s else c.s+(c.credit-a.sum_s) end) as 'покупка/остаток',
    c.name, a.sum_s as 'сумма покупок'
from cte c
outer apply (select sum(x.s) as sum_s from cte x where x.id=c.id and x.Row<=c.Row) a
where (case when c.credit-a.sum_s>0 then c.s else c.s+(c.credit-a.sum_s) end)>=0
order by c.Row;
go

Подвох заключается в том что будет если добавить ещё одну покупку "гречка2" с такой же датой что и у "гречка"

insert into @t2 values(1, 19, '10.22.2005', 'гречка2');

Результаты начали отличаться. В первом случае гречка ушла даром
Собственно почему же так случилось и вечный вопрос что делать?
Случилось это на 8-й строке запроса из форума: dt >= b.dt и ничего с этим сделать уже нельзя т.к суммировать нужно именно те покупки которые были до этого, а не те которые были до этой даты включительно. Т.е не правильно считается сумма покупок/нарастающий итог. Для наглядности в оба запроса я добавил колонку "сумма покупок".
Конечно если бы было указано время покупок то вероятность такого события была бы мала..

Хоть я и указал что применение ROW_NUMBER обосновано мне так и не ответили

comments powered by Disqus
Яндекс.Метрика