MYSQL inline view query (top customer)

Question

I try to make a query, so that I can see who is the top customer in a month (every month since begin till now).

Now I have the tables:

orders (orderID, orderdate, customerID, Netamount, tax, totalamount)

orderline (orderlineID, orderID, prodID, quantity, orderdate)

customer (firstname lastname zip creditcardtype etc.)

I think the other tables aren't necessarily here.

Of course there are customers who never bought a thing and customers who already bought plenty of times.

Now I used this query:

    SELECT customerid, Sum(netamount) 
    FROM orders
    GROUP BY customerid limit 1000000;

Now I see all customers who already bought sth. with the total amount they paid.

With the query

    SELECT YEAR ( Orderdate ) Year ,
    MONTHNAME ( Orderdate ) Month ,
    COUNT(*) TotOrd ,
    FROM orders
    GROUP BY YEAR ( Orderdate ),
    MONTH ( Orderdate );

I get a table where each row shows me the Year Month Total order (placed in that month).

Still I want just to see the Top Customer of a month.

I searched a lot in the internet still couldn't find that what I want (maybe I just googled wrong). I know that I need at least one inline view still no idea how to realize it.

Hope someone can help me out here.

can you show what output you need? I am bit unclear. Perhaps post sqlfiddle. — DevelopmentIsMyPassion, Mar 23 '13 at 22:44
How do you define **Top Customer**? Is it someone whose `total amount` for the month is the largest? — PM 77-1, Mar 23 '13 at 22:53
Sorry for the late reply: There are two possible definitions of a Top Customer. 1: Who ordered most(the amount of money they spent isn't important just the amount of orders(e.g. someone buys 5 products in one order, otherone buys the same products but always with a new order = 5 orders). 2 definition: Who paid the most in a specific month(orderamount isnt necessary). @Ashreva the output should be exactly as in the query from gordon. Just 2-3 little syntax errors, but he already mentioned it. — JOP, Mar 24 '13 at 00:09

score 0 · Accepted Answer · answered Mar 23 '13 at 22:46

You need to join back to the data to get the top customer. So, first calculate the maximum amount in each month, then join back to get the customer with that amount:

select my.year, my.month, myc.customerid, myc.totord
from (select year, month, max(totord) as maxtotord
      from (SELECT YEAR ( Orderdate ) Year, MONTHNAME ( Orderdate ) Month, customerid, COUNT(*) TotOrd ,
            FROM orders
            GROUP BY YEAR ( Orderdate ), MONTH ( Orderdate ), customerid
           ) myc
      group by year, month
     ) my join
     (SELECT YEAR ( Orderdate ) Year, MONTHNAME ( Orderdate ) Month, customerid, COUNT(*) TotOrd ,
      FROM orders
      GROUP BY YEAR ( Orderdate ), MONTH ( Orderdate ), customerid, count(*) as totord
     ) myc
     on my.year = myc.year and my.month = myc.month and my.maxtotord = myc.totord

Note that this is untested, so there might be a syntax error.

Also, this returns multiple customers if there are multiple customers with the max value.

Finally, this is much easier in almost any other database, because most databases now support the row_number() function.

Thanks, this answer is nearly perfect, besides you used 2x comma (both times beofre FROM) and i(think) it wasn't necessary to use: count(*) as totord it was also markes as an error, after deleting it and run that query the solution was exactly as i wanted. Still i have a question, cause i don't really get this query. why is in the (i think third?the part with my join) innerview exactly the same query as in the second query?) could you explain me the query? i would really appreciate it. and thanks a lot — JOP, Mar 24 '13 at 00:19

Jakub Kania · Answer 2 · 2013-03-23T23:04:06.150

0

It's a group-wise max problem but unfortunately MySQL doesn't support window functions or CTE so this can be messy.

SELECT s1.year,s1.month,s1.customerid,s1.totord FROM
(SELECT YEAR ( Orderdate ) Year ,
MONTHNAME ( Orderdate ) Month , 
customerid,
COUNT(*) TotOrd 
FROM orders
GROUP BY YEAR ( Orderdate ),
MONTH ( Orderdate ),customerid) as s1
LEFT JOIN
(SELECT YEAR ( Orderdate ) Year ,
MONTHNAME ( Orderdate ) Month , 
customerid,
COUNT(*) TotOrd 
FROM orders
GROUP BY YEAR ( Orderdate ),
MONTH ( Orderdate ),customerid) as s2
ON
s1.year=s2.year AND s1.month=s2.month AND s2.TorOrd>s1.TotOrd AND s1.customerid>s2.customerid
WHERE s2.customerid IS NULL;

In case of doubles it will return the customer with lower id.

edited Mar 23 '13 at 23:04

answered Mar 23 '13 at 22:56

Jakub Kania

15,665
2
37
47

This query "should" work i think. Just a little syntaxerror(second lastline u have TorOrd instead of TodOrd) but this query is too slow respectively needs too long). I set a Limit of 100 and the query needed nearly 40 seconds. There are about 1000 products , about 130.000 data sets in orders and over 500.000 data sets in orderlines. It should still work. I think i could set an Index here then it would be faster. Anyway thanks for your great help :) – JOP Mar 24 '13 at 00:26
@JOP I think you could use a temp table to avoid calculating the same subquery twice. – Jakub Kania Mar 24 '13 at 00:38

MYSQL inline view query (top customer)

2 Answers2