Sunday, May 8, 2016

How to get difference of top 2 records of each group in SQL Server order by date

I went though today Codility online exam for one of the top investment banking client interview, and I found one interesting question on SQL as below,


Question: 

Get top 2 records of each group /category in SQL Server, order by date and then get difference between them by category wise


In SQL Terms:

Retrieve difference between top 2 records of each group in sql server, order by date OR


Get difference of top 2 records of each group/category in SQL Server, order by date OR


Select top 2 records for each category in sql server order by date


Table Given:

CREATE TABLE [dbo].[test](
[event_type] [int] NOT NULL,
[value] [int] NOT NULL,
[date] [datetime] NOT NULL
) ON [PRIMARY]


Table Values 




Solution:

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY date DESC) AS rn
   FROM test
)

-- Now select from cte.



now retrieve top 2 elements of each group and get difference between them as below,

SELECT event_type, (max(value)-min(value)) as v FROM cte
WHERE rn <= 2
group By event_type

And here goes,




No comments:

Post a Comment