query for get inactive users

Question

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   user1@example.com
2    user2   user2@example.com
3    user3   user3@example.com
4    user4   user4@example.com
5    user5   user5@example.com

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   user1@example.com
2    user2   user2@example.com
5    user5   user5@example.com

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','user1@example.com');
insert into [User](Id, Name, Email) values(2,'user2','user2@example.com');
insert into [User](Id, Name, Email) values(3,'user3','user3@example.com');
insert into [User](Id, Name, Email) values(4,'user4','user4@example.com');
insert into [User](Id, Name, Email) values(5,'user5','user5@example.com');

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');

user_1144035 · Answer

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.

user_215752 · Answer

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.

query for get inactive users

2 Answers