Assume you are given the table below on Uber
transactions made by users. Write a query to obtain the third transaction of
every user. Output the user id, spend and transaction date.
table name: transactions
Solution:
with cte as
(
select rank() over (partition by user_id order by transaction_date) as rnk,
* from transactions
)
select user_id,spend,transaction_date from cte where rnk=3
Output:
SQL Script:
CREATE TABLE [dbo].[transactions](
[user_id]
[int] NULL,
[spend]
[decimal](18, 2) NULL,
[transaction_date]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[transactions] ([user_id], [spend], [transaction_date]) VALUES (111, CAST(100.50 AS Decimal(18, 2)), CAST(N'2022-01-08T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([user_id], [spend], [transaction_date]) VALUES (111, CAST(55.00 AS Decimal(18, 2)), CAST(N'2022-01-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([user_id], [spend], [transaction_date]) VALUES (121, CAST(36.00 AS Decimal(18, 2)), CAST(N'2022-01-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([user_id], [spend], [transaction_date]) VALUES (145, CAST(24.99 AS Decimal(18, 2)), CAST(N'2022-01-26T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[transactions] ([user_id], [spend], [transaction_date]) VALUES (111, CAST(89.60 AS Decimal(18, 2)), CAST(N'2022-02-05T00:00:00.000' AS DateTime))
GO