By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE order_star_member ( users_id INT,
createdAt DATE,
total_price_star_member DECIMAL(10,2) );
INSERT INTO order_star_member VALUES
(12,'2019-01-01',100000),
(12,'2019-01-10',100000),
(12,'2019-01-20',100000),
(12,'2019-02-10',100000),
(12,'2019-02-15',300000),
(12,'2019-02-21',500000),
(13,'2019-01-02',200000),
(13,'2019-01-11',300000),
(13,'2019-01-18',400000),
(13,'2019-02-06',100000),
(13,'2019-02-08',200000),
(13,'2019-02-14',400000),
(14,'2019-01-21',500000),
(14,'2019-01-23',200000),
(14,'2019-01-24',300000),
(14,'2019-02-08',100000),
(14,'2019-02-09',200000),
(14,'2019-02-14',100000),
(15, '2019-03-04',1000000),
(14, '2019-03-04', 300000),
(14, '2019-03-04', 350000),
(13, '2019-03-04', 200000),
(15, '2019-01-23', 620000);
SELECT *
FROM order_star_member;
| users_id | createdAt | total_price_star_member |
|---|---|---|
| 12 | 2019-01-01 | 100000.00 |
| 12 | 2019-01-10 | 100000.00 |
| 12 | 2019-01-20 | 100000.00 |
| 12 | 2019-02-10 | 100000.00 |
| 12 | 2019-02-15 | 300000.00 |
| 12 | 2019-02-21 | 500000.00 |
| 13 | 2019-01-02 | 200000.00 |
| 13 | 2019-01-11 | 300000.00 |
| 13 | 2019-01-18 | 400000.00 |
| 13 | 2019-02-06 | 100000.00 |
| 13 | 2019-02-08 | 200000.00 |
| 13 | 2019-02-14 | 400000.00 |
| 14 | 2019-01-21 | 500000.00 |
| 14 | 2019-01-23 | 200000.00 |
| 14 | 2019-01-24 | 300000.00 |
| 14 | 2019-02-08 | 100000.00 |
| 14 | 2019-02-09 | 200000.00 |
| 14 | 2019-02-14 | 100000.00 |
| 15 | 2019-03-04 | 1000000.00 |
| 14 | 2019-03-04 | 300000.00 |
| 14 | 2019-03-04 | 350000.00 |
| 13 | 2019-03-04 | 200000.00 |
| 15 | 2019-01-23 | 620000.00 |
-- summary per-month data
SELECT users_id,
SUM( CASE WHEN MONTHNAME(createdAt) = 'January'
THEN total_price_star_member
END ) total_price_star_member_January,
SUM( CASE WHEN MONTHNAME(createdAt) = 'February'
THEN total_price_star_member
END ) total_price_star_member_February,
SUM( CASE WHEN MONTHNAME(createdAt) = 'March'
THEN total_price_star_member
END ) total_price_star_member_March
FROM order_star_member
GROUP BY users_id
ORDER BY 1;
| users_id | total_price_star_member_January | total_price_star_member_February | total_price_star_member_March |
|---|---|---|---|
| 12 | 300000.00 | 900000.00 | null |
| 13 | 900000.00 | 700000.00 | 200000.00 |
| 14 | 1000000.00 | 400000.00 | 650000.00 |
| 15 | 620000.00 | null | 1000000.00 |
-- select users who have total_price_star_member sum over 600000 in both months
-- as shown in previous query it is user with id=13 only
SELECT users_id
FROM order_star_member
GROUP BY users_id
HAVING SUM(CASE WHEN MONTHNAME(createdAt) = 'January'
THEN total_price_star_member END) >= 600000
AND SUM(CASE WHEN MONTHNAME(createdAt) = 'March'
THEN total_price_star_member END) >= 600000
AND NOT EXISTS (SELECT 1 FROM order_star_member
GROUP BY users_id
having sum(case when monthname(createdAt) = 'February'
THEN total_price_star_member END) >= 600000);
SELECT users_id,MONTH(createdAt) as m, SUM(total_price_star_member) >= 600000 as r
FROM order_star_member
WHERE createdAt BETWEEN '20190101' AND '2019200331'
GROUP BY users_id,m
SELECT users_id,
MONTH(createdAt) as m,
SUM(total_price_star_member) >= 600000 as star_member
FROM order_star_member
WHERE createdAt BETWEEN '20190101' AND '20190331'
GROUP BY users_id,m
| users_id | m | star_member |
|---|---|---|
| 12 | 1 | 0 |
| 12 | 2 | 1 |
| 13 | 1 | 1 |
| 13 | 2 | 1 |
| 14 | 1 | 1 |
| 14 | 2 | 0 |
| 15 | 3 | 1 |
| 14 | 3 | 1 |
| 13 | 3 | 0 |
| 15 | 1 | 1 |
SELECT users_id, SUM(IF(m=2 and star_member, -100, star_member)) as month_score
FROM
(SELECT users_id,
MONTH(createdAt) as m,
SUM(total_price_star_member) >= 600000 as star_member
FROM order_star_member
WHERE createdAt BETWEEN '20190101' AND '20190331'
GROUP BY users_id, m
) t
GROUP BY users_id
| users_id | month_score |
|---|---|
| 12 | -100 |
| 13 | -99 |
| 14 | 2 |
| 15 | 2 |
SELECT users_id, SUM(IF(m=2 and star_member, -100, star_member)) as month_score
FROM
(SELECT users_id,
MONTH(createdAt) as m,
SUM(total_price_star_member) >= 600000 as star_member
FROM order_star_member
WHERE createdAt BETWEEN '20190101' AND '20190331'
GROUP BY users_id, m
) t
GROUP BY users_id
HAVING month_score=2
| users_id | month_score |
|---|---|
| 14 | 2 |
| 15 | 2 |