Get amount of users that bought something soon after registration, Sql Server

Get amount of users that bought something soon after registration

-- Create schema
CREATE TABLE [Users](
	[Id] [int] IDENTITY NOT NULL,
	[RegistrationDate] [datetime2] NOT NULL,
 CONSTRAINT [PK_Users_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))

CREATE TABLE [Sales](
	[Id] [int] IDENTITY NOT NULL,
	[UserId] [int] NOT NULL,
	[SaleDate] [datetime2] NOT NULL,
 CONSTRAINT [PK_Sales_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))

ALTER TABLE [Sales]  WITH CHECK ADD CONSTRAINT [FK_Sales_Users_UserId_Id] FOREIGN KEY([UserId])
REFERENCES [Users] ([Id])

CREATE NONCLUSTERED INDEX [IX_Sales_UserId] ON [Sales]
(
	[UserId] ASC
)

DECLARE @Now [datetime2] = '2020-02-01T00:00:00.000';
DECLARE @LastMonth [datetime2] = '2020-01-01T00:00:00.000';
DECLARE @SalesThresholdInDays [int] = 5;

-- Insert user that bought smth late after registration
INSERT INTO [Users]
VALUES (@LastMonth)

INSERT INTO [Sales]
VALUES (SCOPE_IDENTITY(), @Now)

-- Insert user that bought smth soon after registration
INSERT INTO [Users]
VALUES (@LastMonth)

INSERT INTO [Sales]
VALUES (SCOPE_IDENTITY(), @LastMonth)

-- Get results
SELECT COUNT(*) AS AmountOfUsers
FROM [Users]
WHERE DATEDIFF(
	day,
	[RegistrationDate],
	(SELECT TOP(1) [SaleDate]
		FROM [Sales]
		WHERE [UserId] = [Users].[Id]
		ORDER BY [Id])) <= @SalesThresholdInDays


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development