Assume you are given the table on Walmart user
transactions. Based on a user's most recent transaction date, write a query to
obtain the users and the number of products bought.
Output the user's most recent transaction date,
user ID and the number of products sorted by the transaction date in
chronological order.
table name: user_transactions
Solution:
with cte as
(
select user_id,transaction_date,count(product_id) as purchase_count,
rank() over (partition by user_id order by transaction_date desc) as rnk
from user_transactions
group by user_id,transaction_date
)
select transaction_date,user_id,purchase_count from cte where rnk=1
order by transaction_date
Output:
SQL Script:
CREATE TABLE [dbo].[user_transactions](
[product_id]
[int] NULL,
[user_id]
[int] NULL,
[spend]
[decimal](18, 2) NULL,
[transaction_date]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[user_transactions] ([product_id], [user_id], [spend],
[transaction_date]) VALUES (3673, 123, CAST(68.90 AS Decimal(18, 2)), CAST(N'2022-08-07T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[user_transactions] ([product_id], [user_id], [spend],
[transaction_date]) VALUES (9623, 123, CAST(274.10 AS Decimal(18, 2)), CAST(N'2022-08-07T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[user_transactions] ([product_id], [user_id], [spend], [transaction_date]) VALUES (1467, 115, CAST(19.90 AS Decimal(18, 2)), CAST(N'2022-08-07T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[user_transactions] ([product_id], [user_id], [spend],
[transaction_date]) VALUES (2513, 159, CAST(25.00 AS Decimal(18, 2)), CAST(N'2022-08-07T12:00:00.000' AS DateTime))
GO
INSERT [dbo].[user_transactions] ([product_id], [user_id], [spend],
[transaction_date]) VALUES (1452, 159, CAST(74.50 AS Decimal(18, 2)), CAST(N'2022-10-07T12:00:00.000' AS DateTime))
GO