JOP JOP - 4 months ago 12
SQL Question

MYSQL inline view query (top customer)

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
Yea
r
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.

Answer

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.

Comments