Including and excluding specific records

Question

I want to find some of buyer who had special condition (in this case, transaction >= 600000 called star member)

In this case, I want to find out star member (transaction >= 600000) who exists in January 2020 and March 2020, but it does not include star member who is doing transaction in February 2020.

here's my syntax

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);

and here's my fiddle

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c85037215fe71f700b51c8fd3a5ae76

on my fiddle, the expected result are the users_Id 15 because that id order at january and march but not in february

Answer 1

First in the inner t we group by month to determine all the star members.

The outer grouping groups by user_id. Their score is the sum of their star_member.

For February (m=2 (February being the second month) on the first line of the query below) if they are a star_member, they get an penalty (-100) as an arbitrary value that the SUM cannot overcome.

The only way a month_score=2 can exist if if a user has a star_member being true (1) for both January and March but not February.

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

fiddle