SQL running total group by id and limited by a condition (over window)

I'm having the following data in an SQL table. See SQL fiddle for playing around.

The data is created as follows.

CREATE TABLE activities (
  id varchar(14),
  pointsEarned int,
  createdAt varchar(24)
  );
  
INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 500, '2023-05-03T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 1000, '2023-05-12T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 800, '2023-05-15T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 300, '2023-05-21T09:05:05.034Z');

INSERT INTO activities (id, pointsEarned, createdAt)
VALUES ('234-00000206-0', 1100, '2023-05-28T09:05:05.034Z');

From that data I'm looking for those IDs that have passed 1.000 points and at what date that has happened. In the sample data this would have been May 12th in the second line already.

I tried this SQL statement, but here the result is of course May, 28th.

SELECT 
  id, 
  SUM(pointsEarned) as points, 
  MAX(createdAt) as lastActivity
FROM 
  activities
GROUP BY
  id
HAVING 
  points > 1000;

Then I was thinking about using window functions and tried this one, but getting an error that the statement is wrong. Any idea how to fix the error (see SQL fiddle link above) and how to limit that query to 1.000 points?

SELECT 
  id,
  SUM(pointsEarned) OVER(ORDER BY createdAt) points
FROM activities;

PS: the SQL fiddle uses MySQL. Finally, I need the statement for Google BigQuery though.

Your code doesn't work in MySQL 5.6 because in that version of MySQL window functions were not yet a feature available. Furthermore, you shouldn't test on MySQL if you need a Google Bigquery solution, because they're two deeply different products with different tools.

Given these premises, in Google Bigquery you have availability of the QUALIFY clause, that allows you to filter over the result of window functions. You can use qualify twice to check:

• when the running sum of points is higher than 1000
• when the date corresponds to the first date of your filtered running sum

by partitioning on "id" (assuming you can have multiple ids), and ordering on your date.

WITH cte AS (
    SELECT id, 
           SUM(pointsEarned) OVER(PARTITION BY id ORDER BY createdAt) AS runningPoints, 
           createdAt
    FROM activities
    QUALIFY runningPoints > 1000
)
SELECT id, runningPoints, createdAt 
FROM cte 
QUALIFY createdAt = MIN(createdAt) OVER(PARTITION BY id ORDER BY runningPoints)

This answer was tested on Bigquery and gave the following output:

This can be accomplished by first using the window function sum() with an order by clause to obtain the running total, followed by row number() to assign an id to each row ordered by the generated running total, then select only the row with rn = 1 :

with cte as (
  SELECT *, SUM(pointsEarned) over (partition by id order by createdAt) points
  FROM activities 
),
cte2 as (
  select *, row_number() over (partition by id order by points) as rn
  from cte
  where points > 1000
)
select id, createdAt
from cte2
where rn = 1

To get data of a specific id then :

with cte as (
  SELECT *, SUM(pointsEarned) over (order by createdAt) points
  FROM activities 
  where id = '234-00000206-0'
),
cte2 as (
  select *, row_number() over (order by points) as rn
  from cte
  where points > 1000
)
select id, createdAt
from cte2
where rn = 1

Demo here

You can express this in a single pass, with a single window function call. In BigQuery:

select a.*, 
    sum(pointsEarned) over(partition by id order by createdAt) as runningPoints, 
from activities a
qualify runningPoints >= 1000 and runningPoints - pointsEarned < 1000

The qualify clause identifies the row where the running sum reaches the threshold.

Note: this works as long as you don't have negative pointsEarned, as shown in your data (otherwise the query would bring one row for each occurence where the threshold is reached again).

You kind of answered this yourself, your SQL was valid (maybe you used an old version of MYSQL in your fiddle)

SELECT 
  id,
  SUM(pointsEarned) OVER(ORDER BY createdAt) points
FROM activities;

which yields

Result (MySQL v8.0)

now you only need to select the first row having > 1000 total points like this

SELECT sum_t.id, sum_t.createdAt
FROM (
	SELECT *, SUM(pointsEarned) OVER(ORDER BY createdAt) points
  	FROM activities
) as sum_t
WHERE sum_t.points > 1000
LIMIT 1

which leads to the following result

Result (MySQL v8.0)

Demo