SQL 面试题 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>JobHunting - 待字闺中

SQL 面试题

SQL 面试题# JobHunting - 待字闺中

I*92011-01-28 08:01

1 楼

2 variables in table1 showing all records of transactions each customer
makes. one customer (usedid 001) may purchase same products (productid Q909)
several times.
table1:
userid productid
001 Q909
001 Q909
001 Q908
002 Q101
Question: use SQL to calculate how many customers purchase only 1 type of
product, 2 types of products, 3...and so on.
for example: if there were 694 unique userid who purchased only 1 unique
productid, 900 unique userid purchased only 2 unique productid, we want to
get below table:
number_of_type_product_purchased number_of_customer
1 694
2 900
3 200

请高手指点吧

t*e2011-01-28 08:01

2 楼

If the system doesn't support distinct count, do the following subquery
Select userid, count(productid) as ProdCount from
(select distinct userid, productid from Table)
group by userid

Q909)

【在 I*********9 的大作中提到】

: 2 variables in table1 showing all records of transactions each customer
: makes. one customer (usedid 001) may purchase same products (productid Q909)
: several times.
: table1:
: userid productid
: 001 Q909
: 001 Q909
: 001 Q908
: 002 Q101
: Question: use SQL to calculate how many customers purchase only 1 type of

s*v2011-01-28 08:01

3 楼

select userid, productid, count(*)
from (
select distinct * from table1
)
group by productid
好久不写sql了，就是先去除duplicate然后group by ?

Q909)

【在 I*********9 的大作中提到】

d*e2011-01-28 08:01

4 楼

select number_of_type_product_purchased, count(*) number_of_customer
from
(select userid, productid, count(*) number_of_type_product_purchased
from table1
group by userid, productid)
group by number_of_type_product_purchased
order by number_of_type_product_purchased, number_of_customer;

Q909)

【在 I*********9 的大作中提到】

x*i2011-01-28 08:01

5 楼

上面没一个可以运行的

d*e2011-01-28 08:01

6 楼

could u post the error message here?
i don't have a machine to test right now.
Thanks.

【在 x*******i 的大作中提到】

: 上面没一个可以运行的

t*e2011-01-28 08:01

7 楼

can you post your sql
Also we don't know the table name. you need insert the table name there.

【在 x*******i 的大作中提到】

: 上面没一个可以运行的

c*t2011-01-28 08:01

8 楼

SELECT num_of_type_prod_purchased, COUNT (*) AS number_of_customer
FROM (SELECT user_id, COUNT (*) AS num_of_type_prod_purchased
FROM (SELECT DISTINCT user_id, product_id
FROM t1)
GROUP BY user_id)
GROUP BY num_of_type_prod_purchased;
运行测试通过，思路：先统计每个customer买了几种product, 再从结果中统计出买1种，2种。。。。各多少个customers

Q909)

【在 I*********9 的大作中提到】

x*y2011-01-28 08:01

9 楼

select cnt, count(usr_id)
from (select usr_id, count(distinct product_id) cnt
from table
group by usr_id
) newTable
group by cnt
order by cnt \g

Q909)

【在 I*********9 的大作中提到】

c*e2011-01-28 08:01

10 楼

sel prod_cnt, count(1) from (
sel userid, count(distinct productid) as prod_cnt
from table1 group by 1) a
group by 1
order by 1 ;

Q909)

【在 I*********9 的大作中提到】

i*e2011-01-28 08:01

11 楼

select NoOfType as number_of_type_product_purchased, count(user) as number_
of_customer
from (
select userid, count(productid) as NoOfType
from (select distinct userid productid
from table1) a
group by userid) a1
group by NoOfType
order by number_of_type_product_purchased

j*s2011-01-28 08:01

12 楼

Select number_of_type_product_purchased , count (*) as number_of_customer
from
(select UserID, count (distinct ProductID) as number_of_type_product_
purchased from table1
Group by UserID) as table2
Group by number_of_type_product_purchased
Order by number_of_type_product_purchased
Go

l*a2011-01-28 08:01

13 楼

SELECT `cnt` AS number_of_type_product_purchased, COUNT(`userid`) AS number_
of_customer FROM
(
SELECT `userid`,COUNT(`productid`) AS cnt FROM `table1`
GROUP BY `userid`
) TMP
GROUP BY `cnt`