MySQL query help

[Tauheed]

I don't have MySQL experience, I need some help to write a query for my work. I need a query that count transaction for a week, for example, I wanna run the query every Friday and want to see the result from last Friday to Thursday, I mean just last week's data. The start date is last Friday (11/12/2021) and the End date is Thursday (11/18/2021). It is a weekly process that I can run every week to download the output. 

There are two tables, sales, and customers. The customer table has fields such as "customer_id", FirstName, LastName, etc and the sales table has "id" which is the same as customer_id from customer table, salesDate, etc. 

I want to count data of those who are customer_id match to the sales table. I would like to ignore some of the users as well like our IT team has a couple of test users that uses for testing. for example testuser1 and testuser2. I would like the query to not count these two users.

Here is an example that I want to make work. Can someone please help me to write the query? 

SELECT COUNT(CustomerID) AS NumberOfCustomer FROM Customer
INNER JOIN Sales ON ID = CustomerID
where user != (testuser1 and testuser2
where sales date between last Friday to Thursday;

 

Thanks!

 

[niche]

What kind of duplication do you have in both tables?

 

 

 

Edited November 16, 2021 by niche

[Tauheed]

15 hours ago, niche said:

What kind of duplication do you have in both tables?

 

 

 

ID from the sales table and customer id from the customer table are the same. Customer ID is primary key in customer table and id is foreign key in sales table. Thanks 

[niche]

If you want to do a COUNT, one of both tables have some dupes.  Which ones or both? 

Edited November 18, 2021 by niche