Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Get amount of users that bought something soon after registration
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
-- 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
View schema
Execution time: 0,02 sec, rows selected: 4, rows affected: 4, absolute service time: 0,2 sec, absolute service time: 0,2 sec
edit mode
|
history
|
discussion
AmountOfUsers
1
1