x***e
发帖数: 2449 | 1 you have a table called order history.
there are 3 columns in the table:
order_id (unique, indexed)
customer_ID (not unique, indexed)
order_date.(not null)
your boss asked you to find out customers who ordered within last 30 days
and how many orders they made during this timeframe as a second column.
"group by with where clause, .." you said to yourself
You figured out the query successfully and gave it to you boss.
Here come his another question:
"I also want to know the customers who have orde |
c*******e
发帖数: 8624 | 2 put a case statement in the sum
sum(if condition satisfied then whatever else null end)
【在 x***e 的大作中提到】
: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:
|
n******n
发帖数: 9 | 3 我怎末觉得他的意思是先找60内的
然后再在这些人里挑30天内？
【在 x***e 的大作中提到】
: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:
|
k********e
发帖数: 702 | 4
这位同学，请独立完成，不要在这里问家庭作业......
【在 x***e 的大作中提到】
: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:
|
k********e
发帖数: 702 | 5 CASE WHEN ... THEN .... END
SELECT CustomerID,SUM(CASE WHEN <30 days THEN 1 ELSE 0 END, count(*)
FROM table WHERE <60 days group by CustomerID
【在 c*******e 的大作中提到】
: put a case statement in the sum
: sum(if condition satisfied then whatever else null end)
|
x***e
发帖数: 2449 | 6 Actually, that is an interview question I got.
I provided the same answer as you guys.
But it seems that the A3 manager is not very satisfied.
I am thinking maybe we need to use pivot?
【在 k********e 的大作中提到】
: CASE WHEN ... THEN .... END
: SELECT CustomerID,SUM(CASE WHEN <30 days THEN 1 ELSE 0 END, count(*)
: FROM table WHERE <60 days group by CustomerID
|
c*******e
发帖数: 8624 | 7 分别求,然后做个outer join?
【在 x***e 的大作中提到】
: Actually, that is an interview question I got.
: I provided the same answer as you guys.
: But it seems that the A3 manager is not very satisfied.
: I am thinking maybe we need to use pivot?
|
t*****g
发帖数: 1275 | 8 hmm...不考虑efficiency先，
第一个好作，过去30天记录按customer_id groupby 一下，得view v1
把条件变一下，过去60天记录按customer_id groupby 一下得view v2
v1 right out join v2
【在 x***e 的大作中提到】
: you have a table called order history.
: there are 3 columns in the table:
: order_id (unique, indexed)
: customer_ID (not unique, indexed)
: order_date.(not null)
: your boss asked you to find out customers who ordered within last 30 days
: and how many orders they made during this timeframe as a second column.
: "group by with where clause, .." you said to yourself
: You figured out the query successfully and gave it to you boss.
: Here come his another question:
|
k********e
发帖数: 702 | 9 did he emphasis it's sql server 2005?
pivot is not standard. It is only available on sql server 2005.
【在 x***e 的大作中提到】
: Actually, that is an interview question I got.
: I provided the same answer as you guys.
: But it seems that the A3 manager is not very satisfied.
: I am thinking maybe we need to use pivot?
|
k********e
发帖数: 702 | 10 效率奇低。
不过兴许面世A3只懂这个，看不懂case when... haha
【在 c*******e 的大作中提到】
: 分别求,然后做个outer join?
|
