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
student table create
Hospital
Record rows
Row wise arithmetic operation using pivot and unpivt
Studiu Individual 1
All the k-mers/n-grams
FIRST DATABASE
aawrish
db_hotel29102020
Teacher