Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
STACK
pk
Primary key and unique key for single column and multi columns
af
Varchar to Binary and vice-versa
db3
count events by 2 shift cycles using derived table
Status History
Trabalho.Agrecação
2021-03-06_LeetCodeSQL