MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE sales(
    id_order VARCHAR(50) NOT NULL,
    createdAt datetime NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    id_location varchar(50) NOT NULL,
    createdby varchar(50) NOT NULL,
    order_status_id varchar(50) NOT NULL,
    PRIMARY KEY(id_order,createdAt)
);

INSERT INTO sales(id_order,createdAt,sale, id_location, createdby, order_status_id)
VALUES(1,'2016-02-02',100, 1, 123, 5),
      (2,'2017-03-02',150, 2, 233, 6),
      (3,'2018-02-02',200, 3, 234, 6),
      (4,'2016-03-03',150, 1, 123, 7),
      (5,'2017-03-04',100, 2, 2334, 7),
      (6,'2018-03-05',200,3, 234, 7),
       (7,'2016-03-10',200, 1, 233, 7),
      (8,'2017-02-01',150, 2, 124, 8),
      (9,'2018-02-04',250, 3, 233, 8),
      (10,'2018-02-05',300, 2, 124, 6);
 CREATE TABLE location(
 id_location varchar(50) NOT NULL,
 location_city varchar(50) NOT NULL);

 INSERT INTO location(id_location, location_city)
 VALUES(1, 'Jakarta'),
 (2, 'Depok'),
 (3, 'Bekasi');

select * from sales;
select * from location;


id_order
createdAt
sale
id_location
createdby
order_status_id


1
2016-02-02 00:00:00
100.00
1
123
5

10
2018-02-05 00:00:00
300.00
2
124
6

2
2017-03-02 00:00:00
150.00
2
233
6

3
2018-02-02 00:00:00
200.00
3
234
6

4
2016-03-03 00:00:00
150.00
1
123
7

5
2017-03-04 00:00:00
100.00
2
2334
7

6
2018-03-05 00:00:00
200.00
3
234
7

7
2016-03-10 00:00:00
200.00
1
233
7

8
2017-02-01 00:00:00
150.00
2
124
8

9
2018-02-04 00:00:00
250.00
3
233
8




id_location
location_city


1
Jakarta

2
Depok

3
Bekasi


/* Find buyers who matches the condition
the buyers ( coloumn createdby) are doing transaction before '2018-03-04' 
and at least doing transaction again between '2016-02-01' - '2018-03-04'
*/
SELECT s1.createdby
FROM sales s1
where order_status_Id in (4, 5, 6, 8)
GROUP BY s1.createdby
HAVING SUM(s1.createdAt BETWEEN '2016-02-01' AND '2018-03-10')
   AND SUM(s1.createdAt <= '2018-03-10') > 1


createdby


124

233


/* Get all sales for all buyers from previous query */
SELECT *
FROM sales
NATURAL JOIN location
NATURAL JOIN ( SELECT s1.createdby
               FROM sales s1
               WHERE order_status_Id in (4, 5, 6, 8)
               GROUP BY s1.createdby
               HAVING SUM(s1.createdAt BETWEEN '2016-02-01' AND '2018-03-10')
                  AND SUM(s1.createdAt <= '2018-03-10') > 1 ) clients


createdby
id_location
id_order
createdAt
sale
order_status_id
location_city


124
2
10
2018-02-05 00:00:00
300.00
6
Depok

233
2
2
2017-03-02 00:00:00
150.00
6
Depok

233
1
7
2016-03-10 00:00:00
200.00
7
Jakarta

124
2
8
2017-02-01 00:00:00
150.00
8
Depok

233
3
9
2018-02-04 00:00:00
250.00
8
Bekasi


/* Get statistic needed */
SELECT COALESCE(location_city, 'Total') AS `Location`, 
       SUM(sale) AS `sale(kg)`,
       SUM(sale) / ANY_VALUE(totalsum) * 100 AS `sale (%)`, 
       COUNT(id_order) AS `count(id_order)`,
       COUNT(id_order) / ANY_VALUE(totalcount) * 100 AS `count(id_order) (%)`
FROM sales
NATURAL JOIN location
NATURAL JOIN ( SELECT s1.createdby
               FROM sales s1
               WHERE s1.order_status_Id in (4, 5, 6, 8)
               GROUP BY s1.createdby
               HAVING SUM(s1.createdAt BETWEEN '2016-02-01' AND '2018-03-10')
                  AND SUM(s1.createdAt <= '2018-03-10') > 1 ) clients
JOIN ( SELECT SUM(sale) totalsum, 
              COUNT(id_order) totalcount 
       FROM sales
       NATURAL JOIN ( SELECT s1.createdby
                      FROM sales s1
                      WHERE s1.order_status_id in (4, 5, 6, 8)
                      GROUP BY s1.createdby
                      HAVING SUM(s1.createdAt BETWEEN '2016-02-01' AND '2018-03-10')
                         AND SUM(s1.createdAt <= '2018-03-10') > 1 ) clients 
       WHERE order_status_Id in (4, 5, 6, 8)) totals
WHERE order_status_Id in (4, 5, 6, 8)
GROUP BY location_city WITH ROLLUP


Location
sale(kg)
sale (%)
count(id_order)
count(id_order) (%)


Bekasi
250.00
29.411765
1
25.0000

Depok
600.00
70.588235
3
75.0000

Total
850.00
100.000000
4
100.0000


/* Get statistic needed */
SELECT COALESCE(location_city, 'Total') AS `Location`, 
       SUM(sale) AS `sale(kg)`,
       SUM(sale) / ANY_VALUE(totalsum) * 100 AS `sale (%)`, 
       COUNT(id_order) AS `count(id_order)`,
       COUNT(id_order) / ANY_VALUE(totalcount) * 100 AS `count(id_order) (%)`
FROM sales

/* 1 */ INNER JOIN location 
/* 1 */ON sales.id_location = location.id_location
/* 2 */ INNER JOIN ( SELECT s1.createdby
               FROM sales s1
               WHERE s1.order_status_Id in (4, 5, 6, 8)
               GROUP BY s1.createdby
               HAVING SUM(s1.createdAt BETWEEN '2016-02-01' AND '2018-03-10')
                  AND SUM(s1.createdAt <= '2018-03-10') > 1 ) clients 
/* 2 */ ON sales.createdby = clients.createdby
JOIN ( SELECT SUM(sale) totalsum, 
              COUNT(id_order) totalcount 
       FROM sales
/* 3 */  INNER JOIN ( SELECT s1.createdby
                      FROM sales s1
                      WHERE s1.order_status_id in (4, 5, 6, 8)
                      GROUP BY s1.createdby
                      HAVING SUM(s1.createdAt BETWEEN '2016-02-01' AND '2018-03-10')
                         AND SUM(s1.createdAt <= '2018-03-10') > 1 ) clients 
/* 3 */ ON sales.createdby = clients.createdby
       WHERE order_status_Id in (4, 5, 6, 8)) totals
WHERE order_status_Id in (4, 5, 6, 8)
GROUP BY location_city WITH ROLLUP


Location
sale(kg)
sale (%)
count(id_order)
count(id_order) (%)


Bekasi
250.00
29.411765
1
25.0000

Depok
600.00
70.588235
3
75.0000

Total
850.00
100.000000
4
100.0000

id_order	createdAt	sale	id_location	createdby	order_status_id
1	2016-02-02 00:00:00	100.00	1	123	5
10	2018-02-05 00:00:00	300.00	2	124	6
2	2017-03-02 00:00:00	150.00	2	233	6
3	2018-02-02 00:00:00	200.00	3	234	6
4	2016-03-03 00:00:00	150.00	1	123	7
5	2017-03-04 00:00:00	100.00	2	2334	7
6	2018-03-05 00:00:00	200.00	3	234	7
7	2016-03-10 00:00:00	200.00	1	233	7
8	2017-02-01 00:00:00	150.00	2	124	8
9	2018-02-04 00:00:00	250.00	3	233	8

createdby	id_location	id_order	createdAt	sale	order_status_id	location_city
124	2	10	2018-02-05 00:00:00	300.00	6	Depok
233	2	2	2017-03-02 00:00:00	150.00	6	Depok
233	1	7	2016-03-10 00:00:00	200.00	7	Jakarta
124	2	8	2017-02-01 00:00:00	150.00	8	Depok
233	3	9	2018-02-04 00:00:00	250.00	8	Bekasi

Location	sale(kg)	sale (%)	count(id_order)	count(id_order) (%)
Bekasi	250.00	29.411765	1	25.0000
Depok	600.00	70.588235	3	75.0000
Total	850.00	100.000000	4	100.0000