1

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.

1 Answer 1

4

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

7
  • Wow. It seems it creates two temporary tables, so I guess it might be quite a bottleneck and memory hog if there is a long running contests with lots of participants and sale data.
    – Maurycy
    May 13, 2014 at 11:03
  • Definitely. I opted for a version that is easy to understand over an efficient one. You don't have many contests per minute, do you?
    – Twinkles
    May 13, 2014 at 11:09
  • No, but the assumption of the project is that there might be thousands or tens of thousands of users and hundreds of sales per user during a single contest and a few contests running at the same time (though there can only be one 'parser' running at the same time). There is also the sake of making the query as generic as possible, because there will be at least a dozen of metrics for contests, each using a different table or different columns in a table.
    – Maurycy
    May 13, 2014 at 11:32
  • I think have to remind you that you explicitly asked whether it is possible at all...
    – Twinkles
    May 13, 2014 at 11:46
  • Whatever, tuning the above query is not difficult, but this is as far as I'm willing to go without getting paid.
    – Twinkles
    May 13, 2014 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.