MySQL, Determine number of customer doing first transaction on certain store

I have transaction table as below

OrderID | CustomerID | StoreID | Date   
1       |          C | Store2  | June 28
2       |          A | Store1  | June 30
3       |          A | Store1  | July 01
4       |          B | Store1  | July 10
5       |          C | Store1  | July 11

“I want number of newly registred customer who did their first transaction on Store1 during July 01 – July 30”

In this case the expected result is 1 which is CustomerID B with OrderID 4

How to query out the data with using EXISTS / NOT EXISTS, but not using IN / NOT IN?

This is what I got so far but I don’t want to use NOT IN for solution

>     SELECT CustomerID
>     FROM table_transaction
>     WHERE StoreID = 'Store1'
>     AND OrderDate BETWEEN '2021-07-01' AND '2021-07-30'
>     AND CustomerID not IN (SELECT CustomerID FROM table_transaction WHERE OrderDate < '2021-07-01') GROUP BY CustomerID

Moreover since the date is parameterized, the where clause on date should be the same. Above solution the date parameter is not exactly the same, as first I’m using between and in subquery I’m using date less than the initial date.

Answer

One approach… with no IN or EXISTS…

CREATE TABLE my_table
(OrderID INT AUTO_INCREMENT PRIMARY KEY
,CustomerID CHAR(1) NOT NULL
,StoreID INT NOT NULL
,Date DATE NOT NULL
,KEY(StoreID,Date)
,KEY(CustomerID,StoreID,Date)
);

INSERT INTO my_table VALUES
(1,'C',2,'2021-06-28'),
(2,'A',1,'2021-06-30'),
(3,'A',1,'2021-07-01'),
(4,'B',1,'2021-07-10'),
(5,'C',1,'2021-07-11');

…

SELECT COUNT(*) total
  FROM my_table a
  JOIN
     ( SELECT CustomerID, StoreID, MIN(Date) date FROM my_table GROUP BY CustomerID, StoreID ) b
    ON b.CustomerID = a.CustomerID 
   AND b.StoreID = a.StoreID
   AND b.Date = a.Date
 WHERE a.StoreID = 1
   AND a.Date BETWEEN '2021-07-01' AND '2021-07-30';
   
+-------+
| total |
+-------+
|     2 |
+-------+

Edit: For the alternative (and incorrectly stated) interpretation of your problem, something like this should suffice:

SELECT COUNT(DISTINCT CustomerID) total
  FROM my_table a
  JOIN
     ( SELECT CustomerID, MIN(Date) date FROM my_table GROUP BY CustomerID) b
    ON b.CustomerID = a.CustomerID 
   AND b.Date = a.Date
 WHERE a.StoreID = 1
   AND a.Date BETWEEN '2021-07-01' AND '2021-07-30';