Mysql – Is it possible to find the user whose aggregated score reaches X value first

aggregatemysql-5.5

Given this sample fiddle containing sales value and date for two users: http://sqlfiddle.com/#!2/8148be/3

How can I find the first user who has reached a total saleValue of 30 since the beginning of 2001?

The background: assume there is a contest running from date X, which ends the moment someone passes a specific threshold of sale value, and that person becomes the sole winner. There can be multiple contest running at the same time, so it is not feasible to check for winner in all contest when inserting new sales into the database.

Best Answer

First you will need a table with a column representing how many sales a seller has accumulated at a certain point in time:

SELECT 
  `sellerId`,
  `saleDate`,
  ( SELECT SUM(`saleValue`) 
    FROM `mzarzycki_test_sale` b 
    WHERE b.`sellerId` = `a.sellerId`
    AND b.`saleDate` >= '2001-01-01 00:00:00'
    AND b.`saleDate` <= a.`saleDate`
    GROUP BY `sellerId`
  ) AS `saleSum`
FROM 
  `mzarzycki_test_sale` a
WHERE
  `saleDate` >= '2001-01-01 00:00:00'
GROUP BY
  `sellerId`,`saleDate`
ORDER BY `sellerId`,`saleDate`

The rest is easy:

SELECT SaleDate,SellerId FROM
(...) c
WHERE c.Salesum >= 30
ORDER BY c.SaleDate
LIMIT 1

SQL Fiddle

Related Question