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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 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 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 分别求,然后做个outer join?
|