#514
You have a table of in-app purchases by user. Users that make their first in-app purchase are placed in a marketing campaign where they see call-to-actions for more in-app purchases. Find the number of users that made additional in-app purchases due to the success of the marketing campaign.
The marketing campaign doesn't start until one day after the initial in-app purchase so users that only made one or multiple purchases on the first day do not count, nor do we count users that over time purchase only the products they purchased on the first day.
table: marketing_campaign
select user_id,created_at,product_id from marketing_campaign
group by user_id,created_at,product_id
having count(user_id)=1
Final Solution:
with cte as (
select user_id,created_at,product_id from marketing_campaign
group by user_id,created_at,product_id
having count(user_id)=1
),
cte2 as
(
select user_id from cte
group by user_id
having count(user_id)>1 and count(distinct created_at) = count(created_at)
)
select count(user_id) as cnt from cte2;
Output:
SQL Script:
USE [StraScratch]
GO
/****** Object: Table [dbo].[marketing_campaign] Script Date: 26-09-2024 15:39:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[marketing_campaign](
[user_id] [int] NULL,
[created_at] [datetime] NULL,
[product_id] [int] NULL,
[quantity] [int] NULL,
[price] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (10, CAST(N'2019-01-01T00:00:00.000' AS DateTime), 101, 3, 55)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (10, CAST(N'2019-01-02T00:00:00.000' AS DateTime), 119, 5, 29)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (10, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 111, 2, 149)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (11, CAST(N'2019-01-02T00:00:00.000' AS DateTime), 105, 3, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (11, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 120, 3, 99)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (12, CAST(N'2019-01-02T00:00:00.000' AS DateTime), 112, 2, 200)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (12, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 110, 2, 299)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (13, CAST(N'2019-01-05T00:00:00.000' AS DateTime), 113, 1, 67)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (13, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 118, 3, 35)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (14, CAST(N'2019-01-06T00:00:00.000' AS DateTime), 109, 5, 199)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (14, CAST(N'2019-01-06T00:00:00.000' AS DateTime), 107, 2, 27)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (14, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 112, 3, 200)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (15, CAST(N'2019-01-08T00:00:00.000' AS DateTime), 105, 4, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (15, CAST(N'2019-01-09T00:00:00.000' AS DateTime), 110, 4, 299)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (15, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 116, 2, 499)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (16, CAST(N'2019-01-10T00:00:00.000' AS DateTime), 113, 2, 67)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (16, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 107, 4, 27)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (17, CAST(N'2019-01-11T00:00:00.000' AS DateTime), 116, 2, 499)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (17, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 104, 1, 154)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (18, CAST(N'2019-01-12T00:00:00.000' AS DateTime), 114, 2, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (18, CAST(N'2019-01-12T00:00:00.000' AS DateTime), 113, 4, 67)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (19, CAST(N'2019-01-12T00:00:00.000' AS DateTime), 114, 3, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (20, CAST(N'2019-01-15T00:00:00.000' AS DateTime), 117, 2, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (21, CAST(N'2019-01-16T00:00:00.000' AS DateTime), 105, 3, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (21, CAST(N'2019-01-17T00:00:00.000' AS DateTime), 114, 4, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (22, CAST(N'2019-01-18T00:00:00.000' AS DateTime), 113, 3, 67)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (22, CAST(N'2019-01-19T00:00:00.000' AS DateTime), 118, 4, 35)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (23, CAST(N'2019-01-20T00:00:00.000' AS DateTime), 119, 3, 29)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (24, CAST(N'2019-01-21T00:00:00.000' AS DateTime), 114, 2, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (25, CAST(N'2019-01-22T00:00:00.000' AS DateTime), 114, 2, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (25, CAST(N'2019-01-22T00:00:00.000' AS DateTime), 115, 2, 72)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (25, CAST(N'2019-01-24T00:00:00.000' AS DateTime), 114, 5, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (25, CAST(N'2019-01-27T00:00:00.000' AS DateTime), 115, 1, 72)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (26, CAST(N'2019-01-25T00:00:00.000' AS DateTime), 115, 1, 72)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (27, CAST(N'2019-01-26T00:00:00.000' AS DateTime), 104, 3, 154)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (28, CAST(N'2019-01-27T00:00:00.000' AS DateTime), 101, 4, 55)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (29, CAST(N'2019-01-27T00:00:00.000' AS DateTime), 111, 3, 149)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (30, CAST(N'2019-01-29T00:00:00.000' AS DateTime), 111, 1, 149)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (31, CAST(N'2019-01-30T00:00:00.000' AS DateTime), 104, 3, 154)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (32, CAST(N'2019-01-31T00:00:00.000' AS DateTime), 117, 1, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (33, CAST(N'2019-01-31T00:00:00.000' AS DateTime), 117, 2, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (34, CAST(N'2019-01-31T00:00:00.000' AS DateTime), 110, 3, 299)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (35, CAST(N'2019-02-03T00:00:00.000' AS DateTime), 117, 2, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (36, CAST(N'2019-02-04T00:00:00.000' AS DateTime), 102, 4, 82)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (37, CAST(N'2019-02-05T00:00:00.000' AS DateTime), 102, 2, 82)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (38, CAST(N'2019-02-06T00:00:00.000' AS DateTime), 113, 2, 67)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (39, CAST(N'2019-02-07T00:00:00.000' AS DateTime), 120, 5, 99)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (40, CAST(N'2019-02-08T00:00:00.000' AS DateTime), 115, 2, 72)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (41, CAST(N'2019-02-08T00:00:00.000' AS DateTime), 114, 1, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (42, CAST(N'2019-02-10T00:00:00.000' AS DateTime), 105, 5, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (43, CAST(N'2019-02-11T00:00:00.000' AS DateTime), 102, 1, 82)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (43, CAST(N'2019-03-05T00:00:00.000' AS DateTime), 104, 3, 154)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (44, CAST(N'2019-02-12T00:00:00.000' AS DateTime), 105, 3, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (44, CAST(N'2019-03-05T00:00:00.000' AS DateTime), 102, 4, 82)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (45, CAST(N'2019-02-13T00:00:00.000' AS DateTime), 119, 5, 29)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (45, CAST(N'2019-03-05T00:00:00.000' AS DateTime), 105, 3, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (46, CAST(N'2019-02-14T00:00:00.000' AS DateTime), 102, 4, 82)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (46, CAST(N'2019-02-14T00:00:00.000' AS DateTime), 102, 5, 29)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (46, CAST(N'2019-03-09T00:00:00.000' AS DateTime), 102, 2, 35)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (46, CAST(N'2019-03-10T00:00:00.000' AS DateTime), 103, 1, 199)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (46, CAST(N'2019-03-11T00:00:00.000' AS DateTime), 103, 1, 199)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (47, CAST(N'2019-02-14T00:00:00.000' AS DateTime), 110, 2, 299)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (47, CAST(N'2019-03-11T00:00:00.000' AS DateTime), 105, 5, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (48, CAST(N'2019-02-14T00:00:00.000' AS DateTime), 115, 4, 72)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (48, CAST(N'2019-03-12T00:00:00.000' AS DateTime), 105, 3, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (49, CAST(N'2019-02-18T00:00:00.000' AS DateTime), 106, 2, 123)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (49, CAST(N'2019-02-18T00:00:00.000' AS DateTime), 114, 1, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (49, CAST(N'2019-02-18T00:00:00.000' AS DateTime), 112, 4, 200)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (49, CAST(N'2019-02-18T00:00:00.000' AS DateTime), 116, 1, 499)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (50, CAST(N'2019-02-20T00:00:00.000' AS DateTime), 118, 4, 35)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (50, CAST(N'2019-02-21T00:00:00.000' AS DateTime), 118, 4, 29)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (50, CAST(N'2019-03-13T00:00:00.000' AS DateTime), 118, 5, 299)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (50, CAST(N'2019-03-14T00:00:00.000' AS DateTime), 118, 2, 199)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (51, CAST(N'2019-02-21T00:00:00.000' AS DateTime), 120, 2, 99)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (51, CAST(N'2019-03-13T00:00:00.000' AS DateTime), 108, 4, 120)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (52, CAST(N'2019-02-23T00:00:00.000' AS DateTime), 117, 2, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (52, CAST(N'2019-03-18T00:00:00.000' AS DateTime), 112, 5, 200)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (53, CAST(N'2019-02-24T00:00:00.000' AS DateTime), 120, 4, 99)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (53, CAST(N'2019-03-19T00:00:00.000' AS DateTime), 105, 5, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (54, CAST(N'2019-02-25T00:00:00.000' AS DateTime), 119, 4, 29)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (54, CAST(N'2019-03-20T00:00:00.000' AS DateTime), 110, 1, 299)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (55, CAST(N'2019-02-26T00:00:00.000' AS DateTime), 117, 2, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (55, CAST(N'2019-03-20T00:00:00.000' AS DateTime), 117, 5, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (56, CAST(N'2019-02-27T00:00:00.000' AS DateTime), 115, 2, 72)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (56, CAST(N'2019-03-20T00:00:00.000' AS DateTime), 116, 2, 499)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (57, CAST(N'2019-02-28T00:00:00.000' AS DateTime), 105, 4, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (57, CAST(N'2019-02-28T00:00:00.000' AS DateTime), 106, 1, 123)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (57, CAST(N'2019-03-20T00:00:00.000' AS DateTime), 108, 1, 120)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (57, CAST(N'2019-03-20T00:00:00.000' AS DateTime), 103, 1, 79)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (58, CAST(N'2019-02-28T00:00:00.000' AS DateTime), 104, 1, 154)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (58, CAST(N'2019-03-01T00:00:00.000' AS DateTime), 101, 3, 55)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (58, CAST(N'2019-03-02T00:00:00.000' AS DateTime), 119, 2, 29)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (58, CAST(N'2019-03-25T00:00:00.000' AS DateTime), 102, 2, 82)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (59, CAST(N'2019-03-04T00:00:00.000' AS DateTime), 117, 4, 999)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (60, CAST(N'2019-03-05T00:00:00.000' AS DateTime), 114, 3, 248)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (61, CAST(N'2019-03-26T00:00:00.000' AS DateTime), 120, 2, 99)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (62, CAST(N'2019-03-27T00:00:00.000' AS DateTime), 106, 1, 123)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (63, CAST(N'2019-03-27T00:00:00.000' AS DateTime), 120, 5, 99)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (64, CAST(N'2019-03-27T00:00:00.000' AS DateTime), 105, 3, 234)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (65, CAST(N'2019-03-27T00:00:00.000' AS DateTime), 103, 4, 79)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (66, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 107, 2, 27)
GO
INSERT [dbo].[marketing_campaign] ([user_id], [created_at], [product_id], [quantity], [price]) VALUES (67, CAST(N'2019-03-31T00:00:00.000' AS DateTime), 102, 5, 82)
GO