What questions do you get in an interview?

Question

SQL

Good day, comrades.
The other day I ran into a job search.
The company called and the girl offered to do (in her opinion) a simple and small task (look at the picture in the attached file)
I called back after 5 days and found out that I can’t do anything and it’s strictly contraindicated for the manager to communicate with me :)
Her answer was: " the employer is not ready to invite you for an interview"
Am I being deceived or did I have to somehow dodge differently in solving this task?)

Answer 1

One case is not significant at all.
There are mistakes, there are accidents, there are inadequate tasks.
The light has not converged on this firm. Fill in and send resumes to other vacancies.

Answer 2

1) As the obvious thing was already said above, the task at the interview is not "do something to make it work", but "do it as well as possible to show how well you can do this job at all"
2) The condition says "above the fields and table records allow any single and group operations". This means that, for example, with an update, they can reduce the number of transactions that have already been completed for a long time, or assign a new random destination warehouse to each operation. And all such actions must be supported. So the task is not actually completed.
3) Do not use cursors and loops if you can solve the problem without them. A million 1 row inserts in a loop will take orders of magnitude longer than 1 million row inserts. In this case, the problem is also solved without a cycle.
4) What happens if in the if (@flag_in > 0 and @flag_out > 0) block after the first update is executed, the second update fails with an error (due to lack of memory on the server, for example)? And what if between two updates wedged another, from another request? It’s worth looking at transactions and try / catch
How would I do (it’s not the fact that this is an ideal solution):
1) wrap everything in a try block, roll back the transaction to a point at the beginning of the block in catch, if something went wrong.
2) put a constraint on the Placement table to not allow negative quantities. In which case an error occurs when inserting, it works out 1)
3) In the case of deletions-updates, we find all the warehouses that are affected by this, recalculate the data for them in the Placement according to the Movement. No cycles, 1 request. (deletions can actually be optimized, similar to point 4)
4) In the case of an insert, you can do the same, but you can optimize and calculate the difference relative to the already processed state in the Placement. Again, no cycles.
5) Notice to the employer that, in general, the statement of the problem is not particularly correct: there is no time for Movements, so it is impossible to adequately process the situation, for example, (+10) (-5) (+100) came to the warehouse, and then replaced (-5) with ( -fifty). As a result, the sum in stock is positive, but there was a moment when it was < 0.
In general, if I were you, I would rewrite the solution as well as possible, just to improve my skills. Here they can give an opinion on how much better it has become. Plus try-catch and transactions. Plus, get rid of the "programmer" style in SQL code - if there are loops and a bunch of if, then something is most likely done wrong.

Answer 3

To be fair, we don't see your decision either)) Criteria can be very different. Whether or not you used transactions, whether or not you used try/catch blocks. Taken cursor or redundant FULL JOIN. Or they filled in the IF ELSE code, but they wanted one request ... But as Sergey answered - One case is really not indicative. Keep searching.
UPD: I looked at your code superficially and in general I understand the customer. There are enough sins.

set @flag_item	= (select [1] from #tmp_insert where [0] = @i)
set @flag_in	= (select [2] from #tmp_insert where [0] = @i)
set @flag_out	= (select [3] from #tmp_insert where [0] = @i)
set @flag_count	= (select [4] from #tmp_insert where [0] = @i)

For this, my leader will crucify me and leave me in the sun of Golgotha.

SELECT @flag_item = col1, @flag_in=col2,@flag_out=col3, @flag_count=col4 FROM #tmp
WHERE col0 = @i

4 times fewer requests for one instruction, and I’d rather not say anything about unsupported and unreadable fields.
By the way - you rollback a transaction under certain conditions. What is a transaction? there is no risk that you will roll back some parallel normally functioning one?

Answer 4

And now all in one procedure and without temporary tables, as many wanted :) ...

IF OBJECT_ID ( '_data_look', 'P' ) IS NOT NULL 
    DROP PROCEDURE _data_look;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE _data_look (@item int,@item_in int,@item_out int,@v int,@x nvarchar(max))
AS 
BEGIN
if @x = 'движение'
  if 	(@item_in <> @item_out) and (@item_in + @item_out > 0) and (@v >= 0)
  begin
  DECLARE @follow	int
  SET @follow = 	(
            case 
              when @item_in = 0 and  @item_out > 0 then  1
              when @item_in > 0 and  @item_out = 0 then -1
              when @item_in > 0 and  @item_out > 0 then  0
            end
          )
    if @follow = 1
        if (select SUM(Количество) from _Размещение where Место = @item_out) is null
          begin
          BEGIN  TRANSACTION;
            insert into _Движение   values (@item,@item_in,@item_out,@v)
            insert into _Размещение values (@item,@item_out,@v)
          COMMIT TRANSACTION;		
          end
        else
          begin
          BEGIN  TRANSACTION;
            insert into _Движение   values (@item,@item_in,@item_out,@v)
            update _Размещение
            set Количество = Количество + @v
            where Место = @item_out
          COMMIT TRANSACTION;	
          end
      if @follow = -1
        if (select SUM(Количество) from _Размещение where Место = @item_in) is null return;
        else
        begin
          if (select Количество  - @v from _Размещение where Место = @item_in) < 0 return;
          BEGIN  TRANSACTION;
            insert into _Движение   values (@item,@item_in,@item_out,@v)
            update _Размещение
            set Количество = Количество - @v
            where Место = @item_in
          COMMIT TRANSACTION;	
        end
      if @follow = 0
        if (select SUM(Количество) from _Размещение where Место = @item_in) is null or (select Количество - @v from _Размещение where Место = @item_in) < 0  return;
        else
        begin
        if (select SUM(Количество) from _Размещение where Место = @item_out) is null 
          begin 
          BEGIN  TRANSACTION;
          insert into _Размещение values (@item,@item_out,0)	
          COMMIT TRANSACTION;	
          end
        BEGIN  TRANSACTION;
          insert into _Движение   values (@item,@item_in,@item_out,@v)
          update _Размещение
            set Количество = Количество - @v
            where Место = @item_in
          update _Размещение
            set Количество = Количество + @v
            where Место = @item_out
        COMMIT TRANSACTION;		
        end			
  end
  else 
    return;
if @x = 'изменение'
  begin

    BEGIN  TRANSACTION;
    
    update _Движение
    set Количество = @v
    where	Товар		= @item		and
        Откуда		= @item_in	and
        Куда		= @item_out
    
    
    update _Размещение
    set Количество = 
      (select case when sum(Количество) is null then 0 else sum(Количество) end from _Движение where Куда =  @item_out)
      -
      (select case when sum(Количество) is null then 0 else sum(Количество) end from _Движение where Откуда =  @item_in)
    where Место = @item_out 
    
    COMMIT TRANSACTION;	
  end
if @x = 'снести N'
begin

    BEGIN  TRANSACTION;
    
    delete from _Движение
    where 
        Товар		= @item		and
        (
                                 Откуда		= @item_out	
                                 or  
                                 Куда		= @item_out
                                 )
        
    delete from _Размещение
    where 
        Товар		= @item		and
        Место		= @item_out


    COMMIT TRANSACTION;	
  end	
END
GO

/*=================================*/
/*=================================*/

/* собственно психи одиночки, а можно в цикле + табличка */
exec _data_look 1, 0, 3, 10,   'движение'
exec _data_look 1, 0, 2, 5,     'движение'
exec _data_look 1, 2, 3, 5,     'движение'
exec _data_look 1, 0, 3, 100, 'изменение'   
 /* а тут меняем данные первого exec на 100 и пересчитываем результат, 
но тут на отрицательное не ставил условие- забил :) */
exec _data_look 1, 2, 3, 4,  'снести N'
/* сносим вообще всю историю со складом 3 */

select * from _Движение
select * from _Размещение
/*
truncate table _Движение
truncate table _Размещение
*/