运行总计-找到运行总计的日期为X

我需要确定哪些玩家(CID)的存款总额为100或更多,以及他们达到该金额的确切日期。

存款表:

CID        DATE        Deposit_Amount
===        =====       ===============
1234       1/6          20
2345       5/6          30
1234       16/6         1
1234       18/6         50
3456       19/6         18
1234       25/6         150 
2345       31/7         50
2345       1/8          18
3456       1/9          50 
1234       5/9          23
3456       5/10         33

答案应该是:

CID    Date
===    ====
1234   25/6
3456   5/10

我尝试使用以下查询,但是我不知道如何找到用户达到100和更多的确切日期

SELECT [CID]
      ,[Date]
      ,deposit 
      ,SUM([Deposit])OVER(PARTITION BY CID ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total_deposit
  FROM [ISC_RAS_CD_MAXDB].[dbo].[Deposits]

有什么建议吗? 谢谢 !

评论
  • 懒喵喵~
    懒喵喵~ 回复

    考虑:

    select cid, min(date)
    from (
        select t.*, sum(deposit_amount) over(partition by cid order by date) sum_deposit_amount
        from mytable t
    ) t
    where sum_deposit_amount >= 100
    group by cid
    

    The subquery does a window sum of the deposit_amount per cid over the date; then the outer query aggregates by cid, and selects the minimum where the window sum is equal or above to 100.