USE newdb
GO

-- rank the sales people's previous year's sales by territory
SELECT id, first_name, last_name, sales_ytd_previous,
 RANK() OVER(PARTITION BY territory ORDER BY sales_ytd_previous DESC) AS SalesRank,
 DENSE_RANK() OVER(PARTITION BY territory ORDER BY sales_ytd_previous DESC) AS SalesRankDense
 FROM salespeople
GO

-- delete the salespeople that had 
-- the lowest sales this quarter and this quarter
delete salespeople WHERE id IN (
SELECT id FROM (
  SELECT id, 
  NTILE(4) OVER(ORDER BY sales_ytd DESC) AS Q_Current,
  NTILE(4) OVER(ORDER BY sales_ytd_previous DESC) AS Q_Last
  FROM salespeople ) AS R
WHERE Q_Current = 4 AND Q_Last = 4
)