Finding previous records after specified one and joining them

Question

We have this table, where players actions are being recorded. I would like to find out where people buy their equipment (Item Bow or Sword). The equipment can be bought in Shop or in Auction (the place can be found in Action column). So when the player buys an item, we need to find Action Shop or Auction (depends on which one of them occured last before an item was bought)

**User       Time        Action         Item** 
  1          12:00       Auction
  2          12:01       Shop
  3          12:04       Shop
  4          12:09       Shop        
  4          12:15       Buy             Bow
  2          12:15       Auction
  2          12:19       Auction
  1          12:25       Chat    
  4          12:33       Auction         
  3          12:47       Chat
  1          12:47       Buy             Sword
  2          12:47       Buy             Bow
  3          12:50       Buy             Sword
  4          12:52       Buy             Bow
  3          12:56       Buy             Bow

The resulut should be

**Time        Item         Place**
 12:15        Bow          Shop
 12:47        Sword        Auction
 12:47        Bow          Auction
 12:50        Sword        Shop
 12:52        Bow          Auction
 12:56        Bow          Shop

I think I migh have a clue how to solve it with cross apply in mssql, but is it possible to solve it without it? I might have to use the query in hive as well. I would be grateful for any answer. Thank you!

Answer 1

You may want something like this (and I'm guessing you might like user in there as well, eh?)

UPDATED ANSWER thanks to Tim's suggestion

with p as    -- pick purchases
(SELECT [user], [time] purchased, [item]
   FROM actions 
   WHERE [action] = 'Buy'
), e as      -- pick entrances where something can be bought
(SELECT [user], [time] entered, [action] place
   FROM actions
   WHERE [action] IN ('Auction', 'Shop')
), j as      -- join purchases with all prior entrances
(SELECT p.[user], p.[purchased], p.[item],
        e.[entered], e.[place]
   FROM p
   JOIN e   on p.[user]=e.[user]
           and p.[purchased]>=e.[entered]
), r as      -- rank entrance closeness to purchase
(SELECT *, row_number() over( partition by [user],[purchased],[item] 
                              order by [entered] desc ) as rnk
   FROM j
)            -- select only where entrance is the closest
SELECT [user],[purchased],[item],[place]
  FROM r
  WHERE rnk = 1
  order by [user],[purchased],[item]

Caveat: TSQL is not my native dialect ;-)