This task was given to me in a programming exercise. I have already submitted my solution, but I didn't really manage to answer this problem in a good way. Since I want to learn I ask the question here in hopes that someone might help me figure this out. I have changed the wording of the task and the column names in hopes that this will not be the first answer on Google for other people trying to solve this exact problem.

I have this table of transactions, and I want to fetch the top 3 sellers for the top 5 items, for the past 10 days. By "top" I mean the sellers with the most transactions, and the items that have been sold the most times (most transactions).

| Field      | Type         | Null | Key | Default           | Extra          |
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| item       | varchar(255) | NO   | MUL | NULL              |                |
| seller     | varchar(255) | NO   |     | NULL              |                |
| created_at | datetime     | NO   | MUL | CURRENT_TIMESTAMP |                |

Is there a way to get this data in 1 SQL query. Keep in mind this is using MySQL. I think this should be solvable with Postgres with window functions, but I don't know them that well.

Currently my solution is to in a loop of days, do 1 query for each day getting the top 5 items for that day, then for each of those item, find the top 3 sellers. This results in 10*5*3 database queries which is not good. Surely this must be solvable in a more efficient manner.

I was testing with fetching a list of top items grouped by date by counting the items. And then fetching the list the top sellers grouped by date and items.Which potentially could solve this with just 2 queries. But I can't find a way to limit the number of results inside a group. So I end up with a list of all items for a given day, not just the top 5 items. And the same with sellers.

Any one now how to solve this efficiently?

  • 1
    Define "top 5 items". Define "top 3 sellers". Different answers for each "day"? Or one set of answers for the 10 days? – Rick James Sep 17 '16 at 22:59
  • 1
    Efficient grouwise-max – Rick James Sep 17 '16 at 23:00
  • Top 5 items means the items that have been sold the most times (most transactions), and by top sellers the sellers that have the most transactions. – espenhogbakk Sep 18 '16 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.