count existing buyer with mysql

Question

i have table like this with mysql version 5.7

CREATE TABLE order_match (
  ID INT,
  user_id INT,
  createdAt DATE,
  status_id INT,
  quantity INT
);

INSERT INTO order_match VALUES
  (1, 12, '2020-01-01', 4, 1),
  (2, 12, '2020-01-03', 7, 1),
  (3, 12, '2020-01-06', 7, 2),
  (4, 13, '2020-01-02', 5, 2),
  (5, 13, '2020-01-03', 6, 1),
  (6, 14, '2020-03-03', 8, 0.5),
  (7, 13, '2020-03-04', 4, 1),
  (8, 15, '2020-04-04', 7, 3),
  (9, 14, '2020-03-02', 7, 2),
  (10, 14, '2020-03-10', 5, 4),
  (11, 13, '2020-04-10', 8, 3),
  (12, 13, '2020-04-11', 8, 2),
  (13, 16, '2020-04-15', 8, 3);

select * from order_match
order by createdAt;

the output just like this

+---------+---------+------------+-----------+----------+
|   ID    | user_id | createdAt  | status_id | quantity |
+---------+---------+------------+-----------+----------+
| 1       |      12 | 2020-01-01 |         4 |        1 |
| 4       |      13 | 2020-01-02 |         5 |        2 |
| 2       |      12 | 2020-01-03 |         7 |        1 |
| 5       |      13 | 2020-01-03 |         6 |        1 |
| 3       |      12 | 2020-01-06 |         7 |        2 |
| 9       |      14 | 2020-03-02 |         7 |        2 |
| 6       |      14 | 2020-03-03 |         8 |        1 |
| 7       |      13 | 2020-03-04 |         4 |        1 |
| 10      |      14 | 2020-03-10 |         5 |        4 |
| 8       |      15 | 2020-04-04 |         7 |        3 |
| 11      |      13 | 2020-04-10 |         8 |        3 |
| 12      |      13 | 2020-04-11 |         8 |        2 |
| 13      |      16 | 2020-04-15 |         8 |        3 |
| 13 rows |         |            |           |          |
+---------+---------+------------+-----------+----------+

with ID as the id of transaction, user_id as the buyer who doing transaction, createdAt as the date transaction happen, status_id as the status of transaction (which 4, 5, 6, 8 as the approval transaction) and quantity as the amount of quantity of every transaction

this is the fiddle so i want to find out the statistic of how many transaction, total amount of quantity, and total frequency of unique user between 2020-03-01 until 2020-04-01, unique user is the user who doing his first approval transaction before 2020-03-01 and at least doing 1 approval transaction in between 2020-03-01 until 2020-04-01, based on the table i made the expected result just like this

+------------+------------------+-----------------+
| count user | total_order (kg) | total_order (x) |
+------------+------------------+-----------------+
|          1 |                1 |               1 |
+------------+------------------+-----------------+

explanation : as we know the user who become unique user in between 2020-03-01 until 2020-04-01 are user_id 13, because he doing his first approval transaction on 2020-01-02 (before 2020-03-01) and then doing his approval transaction at least one time on 2020-03-01 until 2020-04-01, on time range, user_id 13 (count user) doing 1 transaction (total_order (x)) and the amount are 1 kg (total_order (kg )

i've doing this syntax

select
       count(distinct om.user_id) as count,
       sum(om.quantity) as total_order_kg,
       count(om.id) as order_x
  from (select count(xx.count_) as count_
          from (select count(user_id) as count_ from order_match
                where status_Id in (4, 5, 6, 8)
                 group by user_id
                 ) xx
        ) x1,
        (select user_id
           from order_match
          group by user_id
          ) yy,
        order_match om
 where yy.user_id = om.user_id and
 status_id in (4, 5, 6, 8)
 and om.createdAt < '2020-03-01'
   and  EXISTS (select 1 from order_match om2
                where om.user_id = om2.user_id
               and status_id in (4, 5, 6, 8)
                  and om2.createdAt >= '2020-03-01'
                  and om2.createdAt <= '2020-04-01');

but idk why the result like this

+------------+------------------+-----------------+
| count user | total_order (kg) | total_order (x) |
+------------+------------------+-----------------+
|          1 |                3 |               2 |
+------------+------------------+-----------------+

THE FIDDLE

Answer 1

-- separate users statistic
SELECT user_id,
       SUM(quantity * (createdAt >= @start)) total_order_kg,
       SUM(createdAt >= @start) order_x
FROM order_match
WHERE createdAt <= @finish
GROUP BY user_id
HAVING SUM(createdAt >= @start)
   AND SUM(createdAt >= @start) < COUNT(createdAt);

-- overall statistic
SELECT COUNT(*) users_count,
       SUM(order_kg) total_order_kg,
       SUM(order_count) total_order_count
FROM ( SELECT user_id,
              SUM(quantity * (createdAt >= @start)) order_kg,
              SUM(createdAt >= @start) order_count
       FROM order_match
       WHERE createdAt <= @finish
       GROUP BY user_id
       HAVING SUM(createdAt >= @start)
          AND SUM(createdAt >= @start) < COUNT(createdAt) ) totals;

fiddle

Answer 2

'why the result like this' - you are using comma joins so are starting from a cartesian product you can see what is happening if you substitute the aggregations for actual values for example

select
       om.user_id,
       om.quantity,
       om.id,
       x1.count_,
       yy.user_id
  from (select count(xx.count_) as count_
          from (select count(user_id) as count_ from t
                where status_Id in (4, 5, 6, 8)
                 group by user_id
                 ) xx
        ) x1,
        (select user_id
           from t
          group by user_id
          ) yy,
        t om
where yy.user_id = om.user_id and
 status_id in (4, 5, 6, 8)
 and om.createdAt < '2020-03-01' 
 and  EXISTS (select 1 from t om2
                where om.user_id = om2.user_id
               and status_id in (4, 5, 6, 8)
                  and om2.createdAt >= '2020-03-01'
                  and om2.createdAt <= '2020-04-01');

Where t is my table name and a copy of order_match.

If you run this query without the where clause then you get 65 rows returned, if you run it with the where clause but not the exists check you get 3 rows returned if you run it in it's entirety you get

---------+----------+------+--------+---------+
| user_id | quantity | id   | count_ | user_id |
+---------+----------+------+--------+---------+
|      13 |        2 |    4 |      4 |      13 |
|      13 |        1 |    5 |      4 |      13 |
+---------+----------+------+--------+---------+
2 rows in set (0.002 sec)

Which when aggregated produces the result you get from your query.

NB group by without any aggregation functions is just wrong.