How to return a record when the amount has reached a certain threshold

Question

I use SQL, and I have a table with three columns: account, transaction_date, Points. Each account will have multiple transactions and points earned for each transaction.

How can I return transaction_date when each account reaches a certain threshold (i.e. accumulates 100 points). Let's say the first account has 2,000 transactions, and the first five have 21 points each. I would like the request to return transaction No. 5, because this is when the account has reached 100.

Can anyone help? Thank you Cat

Answer 1

select min(a.transaction_date), a.account from

(select sum(t1.points) as thesum, t2.transaction_date, t2.account 
from table t1
inner join table t2 on t1.account = t2.account and t1.transaction_date <= t2.transaction_date
group by t2.transaction_date, t2.account
having thesum >= 100) a 

group by a.account

Answer 2

Use a triangular connection:

In T-SQL:

SELECT account, MIN(dt), MIN(points) 
FROM 
(
    SELECT t1.account, t1.date, sum(t2.points) AS points
    FROM table t1
      INNER JOIN table t2 ON t1.account = t2.account AND t1.dt >= t2.dt
    GROUP BY t1.account, t1.date
    HAVING SUM(t2.points) > 100
) iq
GROUP BY account

Answer 3

, .

SELECT account_id, MIN(transaction_date) FROM table t1 WHERE (SELECT SUM(points) FROM table t2 WHERE t2.transaction_date < t1.transaction_date) <= 100 GROUP BY account_id

Answer 4

, . . , .

, , , , , (), , , , . . .