Get rows from table whose sum is less than value

Question

Consider I have the following rows in the table

|  id |   user_id   |  amount |      date      |
------------------------------------------------
|  1  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  2  |     2       |  100    |   2019-09-30   |
------------------------------------------------
|  3  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  4  |     3       |  100    |   2019-10-01   |
------------------------------------------------
|  5  |     1       |  75     |   2019-10-01   |
------------------------------------------------
|  6  |     3       |  100    |   2019-10-01   |
------------------------------------------------
|  7  |     1       |  35     |   2019-10-01   |
------------------------------------------------

I am trying find a way to get all the rows with user_id = 1 where the sum(amount) < 300 and date <= '2019-10-01'.

What I am trying to do is to only process records that meet a certain threshold sum. I am not quite sure where to start.

Expected Result

|  id |   user_id   |  amount |      date      |
------------------------------------------------
|  1  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  3  |     1       |  100    |   2019-09-30   |
------------------------------------------------
|  5  |     1       |   75    |   2019-10-01   |
------------------------------------------------

Here is what I have tried so far

SELECT id, SUM(amount) as total_sum 
   FROM table 
   WHERE date <= '2019-10-01' AND user_id = 1
     ORDER BY date ASC
   HAVING total_sum <= 300

I don't get the desired output based on the above query.

MySQL Version currently using: 5.7.25

I did look at this question MySQL select records with sum greater than threshold assuming they are trying to do the same thing, but this isn't what I am looking at

Answer 1

If you don't like to use Session Variables (some experienced SO users dislike them vehemently), you can utilize a technique based on "Self-Join" and then use GROUP BY with HAVING to filter out.

General idea is that we left join to get previous rows for the specific user_id, and then aggregate to get the rolling sum, and then filtering using Having clause.

Query

SELECT 
  t1.* 
FROM 
  your_table AS t1 
LEFT JOIN your_table AS t2 
  ON t2.user_id = t1.user_id 
     AND t2.date <= t1.date 
     AND t2.id <= t1.id 
WHERE t1.user_id = 1 
  AND t1.date <= '2019-10-31'
GROUP BY t1.user_id, t1.date, t1.id, t1.amount
HAVING COALESCE(SUM(t2.amount),0) < 300;

Result

| id  | user_id | amount | date       |
| --- | ------- | ------ | ---------- |
| 1   | 1       | 100    | 2019-09-30 |
| 3   | 1       | 100    | 2019-09-30 |
| 5   | 1       | 75     | 2019-10-01 |

View on DB Fiddle

You can benchmark both the approaches and decide which one is suitable.

For this query, you will need the composite index: (user_id, date)