The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Popular items for given number of users

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.
LikeMe Send private email
Monday, March 07, 2005
 
 
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.
LikeMe Send private email
Monday, March 07, 2005
 
 
Good one.  I'd like to know this also.
AllanL5
Monday, March 07, 2005
 
 
select product from
(select count(*) as num, product from sales group by product)
where num > 5
Dino
Monday, March 07, 2005
 
 
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?
LikeMe Send private email
Tuesday, March 08, 2005
 
 
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.
Dino
Tuesday, March 08, 2005
 
 
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;
Dino
Tuesday, March 08, 2005
 
 
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.
LikeMe Send private email
Wednesday, March 09, 2005
 
 
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.
Dino
Thursday, March 10, 2005
 
 
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..?
LikeMe Send private email
Friday, March 11, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz