sql - Calculating Running Total with OVER Clause and PARTITION BY Clause with counter

Question

how to calculate the sum of points each week_number and user_name using a counter ? I use sum() OVER (PARTITION BY ORDER BY )

And I would like to have the maximum sum at 10.
How can I do this?

Here is my original table:

Here is the result I would like to obtain:

Here is the sql code:

SELECT week_number,
user_name,
sum(points) OVER (PARTITION BY user_name ORDER BY week_number) AS total_prime
FROM team;

Answer 1

Try this

select 
  week_number, 
  user_name, 
  points, 
  case when total_prime > 10 then 10 else total_prime end as sum, 
  case when total_prime > 1 and total_prime < 10 then null 
       when total_prime > 10 then total_prime - 10 end as dif
from
  (select 
     week_number, 
     user_name, points, 
     sum(points) over (partition by user_name order by week_number) as total_prime 
   from 
     team) a

I don't get it in your second table where alan have point 4 but have sum 2.