How to calculate cumulative sums in MySQL

Question

I am preparing for interviews and came across this question while practicing some SQL questions recently asked in Amazon. I could not find the table though, but the question is as follows:

Find the cumulative sum of the top 10 most profitable products of the last 6 months for customers in Seattle.

Does the approach to solving this type of query look correct? If not, what would be the best way to approach this problem?

SELECT t.day,
       t.product_count,
       @running_total:=@running_total + t.product_count AS cumulative_sum
FROM
( SELECT
  date(purchase_date) as day,
  count(product_id) as product_count
  FROM products
  where day > DATE_SUB(now(), INTERVAL 6 MONTH)
  AND customer_city = 'Seattle'
  GROUP BY day 
  ORDER BY product_count desc) t
JOIN (SELECT @running_total:=0) r
ORDER BY t.day
LIMIT 10;

Answer 1

use this

select day,product_count,
sum(product_count) over (order by t.day ROWS UNBOUNDED PRECEDING) as cumulative_sum from (
SELECT
  date(purchase_date) as day,
  count(product_id) as product_count
  FROM products
  where day > DATE_SUB(now(), INTERVAL 6 MONTH)
  AND customer_city = 'Seattle'
  GROUP BY day 
  ORDER BY product_count desc
)t