CODEWITHSUNDEEP
sqlsql-serverdatabase
Roshan
Roshan

Reputation: 167

query for get inactive users

I Got stuck with the query. Here is my scenario. I have two tables one is User and another is Transaction.

User table

Id   Name    Email
1    user1   [email protected]
2    user2   [email protected]
3    user3   [email protected]
4    user4   [email protected]
5    user5   [email protected]

Transaction table

Id   UserId   Credit   Debit   Description      DateAdded
1    1        10.00     0.00   Add-Cash         2020-05-19 00:00:00.000
2    2         0.00    15.00   Buy Course XYZ   2020-04-01 00:00:00.000
3    3         0.00    20.00   Buy Course ABC   2020-05-15 00:00:00.000
4    4         0.00    15.00   Buy Course IJK   2020-05-01 00:00:00.000

I need to get the list of users who haven't bought any course from the last 30 days. So I need to use where clause in the query. I used where clause but didn't get the expected result.

Below is my expected output.

Id   Name    Email
1    user1   [email protected]
2    user2   [email protected]
5    user5   [email protected]

Here is what I'm doing to get the output

SELECT U.Id, U.Name, U.Email FROM [User] U
LEFT JOIN [Transaction] T ON U.Id=T.UserId
WHERE T.Description LIKE '%Buy Course%'
GROUP BY U.Id, U.Name, U.Email
HAVING MAX(T.DateAdded) <= (GETDATE()-30)

Below is the script for tables

create database Test
use Test
create table [User]
(
   [Id] int,
   [Name] nvarchar(50),
   [Email] nvarchar(50)
)

create table [Transaction]
(
   [Id] int,
   [UserId] int,
   [Credit] decimal(18,2),
   [Debit] decimal(18,2),
   [Description] nvarchar(100),
   [DateAdded] datetime
)

insert into [User](Id, Name, Email) values(1,'user1','[email protected]');
insert into [User](Id, Name, Email) values(2,'user2','[email protected]');
insert into [User](Id, Name, Email) values(3,'user3','[email protected]');
insert into [User](Id, Name, Email) values(4,'user4','[email protected]');
insert into [User](Id, Name, Email) values(5,'user5','[email protected]');

insert into [Transaction](Id, UserId, Credit, Debit, Description, DateAdded) values(1, 1, 10.00, 0.00, 'Add-Cash', '2020-05-19 00:00:00.000');
insert into [Transaction](Id, UserId, Credit, Debit, Description, DateAdded) values(2, 2, 0.00, 15.00, 'Buy Course XYZ', '2020-04-01 00:00:00.000');
insert into [Transaction](Id, UserId, Credit, Debit, Description, DateAdded) values(3, 3, 0.00, 20.00, 'Buy Course ABC', '2020-05-15 00:00:00.000');
insert into [Transaction](Id, UserId, Credit, Debit, Description, DateAdded) values(4, 4, 0.00, 15.00, 'Buy Course IJK', '2020-05-01 00:00:00.000');

Upvotes: 2

Views: 2550

Answers (2)

Hogan
Hogan

Reputation: 70513

You can use not exists or a left join and check in join for nulls -- like this

SELECT DISTINCT users.id, users.name, users.email
FROM USERS
LEFT JOIN TRANSACTIONS AS T ON USERS.ID = T.USERID 
       AND T.Description LIKE 'Buy Course%'
       AND T.DateAdded >= DATEADD(day,-30, GETDATE())
WHERE T.USERID IS NULL

Here we join items you want to exclude and then take all the rows that are not joined. I'm using the criteria you posted.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I need to get the list of users who haven't bought any course from the last 30 days. So I need to use where clause in the query.

It is unclear what "bought any course" means in your data. Presumably it is related to the where clause in the query.

The basic idea is not exists:

select u.*
from users u
where not exists (select 1
                  from transactions t
                  where t.userid = u.id and and
                        t.Description like '%Buy Course%' and
                        t.dateadded > dateadd(day, -30, getdate())
                 );

Here is a db<>fiddle. Note that it changes the table names to users and transactions so they do not conflict with SQL Server reserved words.

You can modify a where clause in the subquery if your definition of "bought a course" is different.

Upvotes: 3

Related Questions