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,
Get top 2 records of each group /category in SQL Server, order by date and then get difference between them by category wise
[value] [int] NOT NULL,
[date] [datetime] NOT NULL
) ON [PRIMARY]
Solution:
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,
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,CREATE TABLE [dbo].[test](
[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