07 May 2008

How to Group Customers by Number of Purchases Quickly!

This is a great little sql statement that packs a nice punch for the number crunchers out there...

A disclaimer first: make sure you don't do this on a live OLTP table with millions of rows unless you want your customers to wait. Thus, run it on your slave :).

Onto business...

The Request

"I need to know the number of people who made one purchase, two purchases, three purchases, and so on. Right now."

The Answer
SELECT total, COUNT(1) FROM (SELECT customer_id, COUNT(1) AS total FROM purchases_table GROUP BY customer_id) derived_table GROUP BY total ORDER BY total;

And would you know it, the result was there in seconds....

Until next time, (I do hope to put up some juicy warehousing how-to's soon)
David