RSS Feed

The Design of Software (CLOSED)

There are products and there are customers who have purchased the products. So I have table with fields: Product, Customer

I like to:
Find the max number of products for the given 'number' of customers. ie. For example, I like to find maximum number of products purchased by atleast 5 customers.

Is there specific name for this type of query?
How do I make SQL?

Thanks.

One clarification in above:
Those 5 customers in above case, should be the same for each product purchased.

So I am NOT looking for max  count from all the products appeared atleast 5 times in database.


The idea is know the items, popular with particular group of people.

Good one.  I'd like to know this also.

select product from
(select count(*) as num, product from sales group by product)
where num > 5

I have little problem with that query. It doesn't have 'customers' in picture. So, how I can retain the same set of customers who were selected first time?

select distinct cs.customer from sales cs,
  (select count(*) as num, product from sales group by product) ps and
where ps.num > 5 and ps.product = cs.product;

This should give you the customers that bought products which were purchased more than 5 times.

Sorry ...

select distinct cs.customer from sales cs, (select count(*) as num, product from sales group by product) ps where ps.num > 5 and ps.product = cs.product;

No, It  still does not satisfy my query:

I would put in other words,
 There are X products  which are purchased by atleast by 5 different customers. Out of these 'X' products, 'Z' products are purchased by same 5 customers. I like to find top values of  count(Z) and associated values.

select sa.product as product_a, sb.product_b, sa.customer as customer from

(select sa.* from (select count(*) as num, product from sales group by product) a, sales sa where a.product = sa.product and a.num > 5) ca,

(select sb.* from (select count(*) as num, product from sales group by product) b, sales sb where b.product = sb.product and b.num > 5) cb

where

sa.product > sb.product and sa.customer = sb.customer

This returns the set of product pairs (only products sold at least 5 times) for each customer. Use > strict such that you include pairs only  one time (<> would include both (a,b) and (b,a)).

You could expand this query to analyze 5 products (keep joining) but that's pretty abusive. Better off, you could start from the query above and write the code which gets to your result.

Thanks,

Wow, that's big. Especially 'Keep joining". Is there advanced SQL notation just like Big-O notation for algorithm to measure the fastness of the query in terms of number of tables and fields involved? How about this particular query..?