Finding User Purchases

Write a query that'll identify returning active users. A returning active user is a user that has made a second purchase within 7 days of any other of their purchases. Output a list of user_ids of these returning active users.

table name: amazon_transactions


Solution:
with cte as
(
select *, DATEDIFF(day, lag(created_at,1) over (partition by user_id order by created_at asc),created_at) as dateDiffr from amazon_transactions 
)
select distinct user_id from cte where dateDiffr <7

Output:

SQL Script:
CREATE TABLE [dbo].[amazon_transactions](
	[id] [int] NULL,
	[user_id] [int] NULL,
	[item] [varchar](550) NULL,
	[created_at] [datetime] NULL,
	[revenue] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (1, 109, N'milk', CAST(N'2020-03-03T00:00:00.000' AS DateTime), 123)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (2, 139, N'biscuit', CAST(N'2020-03-18T00:00:00.000' AS DateTime), 421)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (3, 120, N'milk', CAST(N'2020-03-18T00:00:00.000' AS DateTime), 176)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (4, 108, N'banana', CAST(N'2020-03-18T00:00:00.000' AS DateTime), 862)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (5, 130, N'milk', CAST(N'2020-03-28T00:00:00.000' AS DateTime), 333)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (6, 103, N'bread', CAST(N'2020-03-29T00:00:00.000' AS DateTime), 862)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (7, 122, N'banana', CAST(N'2020-03-07T00:00:00.000' AS DateTime), 952)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (8, 125, N'bread', CAST(N'2020-03-13T00:00:00.000' AS DateTime), 317)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (9, 139, N'bread', CAST(N'2020-03-30T00:00:00.000' AS DateTime), 929)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (10, 141, N'banana', CAST(N'2020-03-17T00:00:00.000' AS DateTime), 812)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (11, 116, N'bread', CAST(N'2020-03-31T00:00:00.000' AS DateTime), 226)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (12, 128, N'bread', CAST(N'2020-03-04T00:00:00.000' AS DateTime), 112)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (13, 146, N'biscuit', CAST(N'2020-03-04T00:00:00.000' AS DateTime), 362)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (14, 119, N'banana', CAST(N'2020-03-28T00:00:00.000' AS DateTime), 127)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (15, 142, N'bread', CAST(N'2020-03-09T00:00:00.000' AS DateTime), 503)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (16, 122, N'bread', CAST(N'2020-03-06T00:00:00.000' AS DateTime), 593)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (17, 128, N'biscuit', CAST(N'2020-03-24T00:00:00.000' AS DateTime), 160)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (18, 112, N'banana', CAST(N'2020-03-24T00:00:00.000' AS DateTime), 262)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (19, 149, N'banana', CAST(N'2020-03-29T00:00:00.000' AS DateTime), 382)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (20, 100, N'banana', CAST(N'2020-03-18T00:00:00.000' AS DateTime), 599)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (21, 130, N'milk', CAST(N'2020-03-16T00:00:00.000' AS DateTime), 604)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (22, 103, N'milk', CAST(N'2020-03-31T00:00:00.000' AS DateTime), 290)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (23, 112, N'banana', CAST(N'2020-03-23T00:00:00.000' AS DateTime), 523)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (24, 102, N'bread', CAST(N'2020-03-25T00:00:00.000' AS DateTime), 325)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (25, 120, N'biscuit', CAST(N'2020-03-21T00:00:00.000' AS DateTime), 858)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (26, 109, N'bread', CAST(N'2020-03-22T00:00:00.000' AS DateTime), 432)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (27, 101, N'milk', CAST(N'2020-03-01T00:00:00.000' AS DateTime), 449)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (28, 138, N'milk', CAST(N'2020-03-19T00:00:00.000' AS DateTime), 961)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (29, 100, N'milk', CAST(N'2020-03-29T00:00:00.000' AS DateTime), 410)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (30, 129, N'milk', CAST(N'2020-03-02T00:00:00.000' AS DateTime), 771)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (31, 123, N'milk', CAST(N'2020-03-31T00:00:00.000' AS DateTime), 434)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (32, 104, N'biscuit', CAST(N'2020-03-31T00:00:00.000' AS DateTime), 957)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (33, 110, N'bread', CAST(N'2020-03-13T00:00:00.000' AS DateTime), 210)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (34, 143, N'bread', CAST(N'2020-03-27T00:00:00.000' AS DateTime), 870)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (35, 130, N'milk', CAST(N'2020-03-12T00:00:00.000' AS DateTime), 176)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (36, 128, N'milk', CAST(N'2020-03-28T00:00:00.000' AS DateTime), 498)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (37, 133, N'banana', CAST(N'2020-03-21T00:00:00.000' AS DateTime), 837)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (38, 150, N'banana', CAST(N'2020-03-20T00:00:00.000' AS DateTime), 927)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (39, 120, N'milk', CAST(N'2020-03-27T00:00:00.000' AS DateTime), 793)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (40, 109, N'bread', CAST(N'2020-03-02T00:00:00.000' AS DateTime), 362)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (41, 110, N'bread', CAST(N'2020-03-13T00:00:00.000' AS DateTime), 262)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (42, 140, N'milk', CAST(N'2020-03-09T00:00:00.000' AS DateTime), 468)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (43, 112, N'banana', CAST(N'2020-03-04T00:00:00.000' AS DateTime), 381)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (44, 117, N'biscuit', CAST(N'2020-03-19T00:00:00.000' AS DateTime), 831)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (45, 137, N'banana', CAST(N'2020-03-23T00:00:00.000' AS DateTime), 490)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (46, 130, N'bread', CAST(N'2020-03-09T00:00:00.000' AS DateTime), 149)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (47, 133, N'bread', CAST(N'2020-03-08T00:00:00.000' AS DateTime), 658)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (48, 143, N'milk', CAST(N'2020-03-11T00:00:00.000' AS DateTime), 317)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (49, 111, N'biscuit', CAST(N'2020-03-23T00:00:00.000' AS DateTime), 204)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (50, 150, N'banana', CAST(N'2020-03-04T00:00:00.000' AS DateTime), 299)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (51, 131, N'bread', CAST(N'2020-03-10T00:00:00.000' AS DateTime), 155)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (52, 140, N'biscuit', CAST(N'2020-03-17T00:00:00.000' AS DateTime), 810)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (53, 147, N'banana', CAST(N'2020-03-22T00:00:00.000' AS DateTime), 702)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (54, 119, N'biscuit', CAST(N'2020-03-15T00:00:00.000' AS DateTime), 355)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (55, 116, N'milk', CAST(N'2020-03-12T00:00:00.000' AS DateTime), 468)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (56, 141, N'milk', CAST(N'2020-03-14T00:00:00.000' AS DateTime), 254)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (57, 143, N'bread', CAST(N'2020-03-16T00:00:00.000' AS DateTime), 647)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (58, 105, N'bread', CAST(N'2020-03-21T00:00:00.000' AS DateTime), 562)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (59, 149, N'biscuit', CAST(N'2020-03-11T00:00:00.000' AS DateTime), 827)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (60, 117, N'banana', CAST(N'2020-03-22T00:00:00.000' AS DateTime), 249)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (61, 150, N'banana', CAST(N'2020-03-21T00:00:00.000' AS DateTime), 450)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (62, 134, N'bread', CAST(N'2020-03-08T00:00:00.000' AS DateTime), 981)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (63, 133, N'banana', CAST(N'2020-03-26T00:00:00.000' AS DateTime), 353)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (64, 127, N'milk', CAST(N'2020-03-27T00:00:00.000' AS DateTime), 300)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (65, 101, N'milk', CAST(N'2020-03-26T00:00:00.000' AS DateTime), 740)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (66, 137, N'biscuit', CAST(N'2020-03-12T00:00:00.000' AS DateTime), 473)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (67, 113, N'biscuit', CAST(N'2020-03-21T00:00:00.000' AS DateTime), 278)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (68, 141, N'bread', CAST(N'2020-03-21T00:00:00.000' AS DateTime), 118)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (69, 112, N'biscuit', CAST(N'2020-03-14T00:00:00.000' AS DateTime), 334)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (70, 118, N'milk', CAST(N'2020-03-30T00:00:00.000' AS DateTime), 603)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (71, 111, N'milk', CAST(N'2020-03-19T00:00:00.000' AS DateTime), 205)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (72, 146, N'biscuit', CAST(N'2020-03-13T00:00:00.000' AS DateTime), 599)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (73, 148, N'banana', CAST(N'2020-03-14T00:00:00.000' AS DateTime), 530)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (74, 100, N'banana', CAST(N'2020-03-13T00:00:00.000' AS DateTime), 175)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (75, 105, N'banana', CAST(N'2020-03-05T00:00:00.000' AS DateTime), 815)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (76, 129, N'milk', CAST(N'2020-03-02T00:00:00.000' AS DateTime), 489)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (77, 121, N'milk', CAST(N'2020-03-16T00:00:00.000' AS DateTime), 476)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (78, 117, N'bread', CAST(N'2020-03-11T00:00:00.000' AS DateTime), 270)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (79, 133, N'milk', CAST(N'2020-03-12T00:00:00.000' AS DateTime), 446)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (80, 124, N'bread', CAST(N'2020-03-31T00:00:00.000' AS DateTime), 937)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (81, 145, N'bread', CAST(N'2020-03-07T00:00:00.000' AS DateTime), 821)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (82, 105, N'banana', CAST(N'2020-03-09T00:00:00.000' AS DateTime), 972)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (83, 131, N'milk', CAST(N'2020-03-09T00:00:00.000' AS DateTime), 808)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (84, 114, N'biscuit', CAST(N'2020-03-31T00:00:00.000' AS DateTime), 202)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (85, 120, N'milk', CAST(N'2020-03-06T00:00:00.000' AS DateTime), 898)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (86, 130, N'milk', CAST(N'2020-03-06T00:00:00.000' AS DateTime), 581)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (87, 141, N'biscuit', CAST(N'2020-03-11T00:00:00.000' AS DateTime), 749)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (88, 147, N'bread', CAST(N'2020-03-14T00:00:00.000' AS DateTime), 262)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (89, 118, N'milk', CAST(N'2020-03-15T00:00:00.000' AS DateTime), 735)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (90, 136, N'biscuit', CAST(N'2020-03-22T00:00:00.000' AS DateTime), 410)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (91, 132, N'bread', CAST(N'2020-03-06T00:00:00.000' AS DateTime), 161)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (92, 137, N'biscuit', CAST(N'2020-03-31T00:00:00.000' AS DateTime), 427)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (93, 107, N'bread', CAST(N'2020-03-01T00:00:00.000' AS DateTime), 701)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (94, 111, N'biscuit', CAST(N'2020-03-18T00:00:00.000' AS DateTime), 218)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (95, 100, N'bread', CAST(N'2020-03-07T00:00:00.000' AS DateTime), 410)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (96, 106, N'milk', CAST(N'2020-03-21T00:00:00.000' AS DateTime), 379)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (97, 114, N'banana', CAST(N'2020-03-25T00:00:00.000' AS DateTime), 705)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (98, 110, N'bread', CAST(N'2020-03-27T00:00:00.000' AS DateTime), 225)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (99, 130, N'milk', CAST(N'2020-03-16T00:00:00.000' AS DateTime), 494)
GO
INSERT [dbo].[amazon_transactions] ([id], [user_id], [item], [created_at], [revenue]) VALUES (100, 117, N'bread', CAST(N'2020-03-10T00:00:00.000' AS DateTime), 209)
GO


Comments (0)
Questions List