SQL Interview Questions - Time Series Based Problems

I was just wondering about time-series based SQL problems, and decided to put up a data sample based on the tea & chocolate consumption of Microsoft & Sun Microsystems. Let's take the sample data below.

SALESORDERID	ITEM_NAME	QUANTITY	COST_PER_UNIT	ORDERDATE	DUEDATE	ORDERSTATUS	CUSTOMERNAME
1	TEA	45.0000	1.2900	2/29/2008	3/31/2008	P	MICROSOFT
2	TEA	30.0000	1.2500	4/30/2008	7/31/2008	P	MICROSOFT
3	TEA	90.0000	1.5000	5/31/2008	7/31/2008	P	MICROSOFT
4	TEA	12.0000	2.0000	6/30/2008	8/31/2008	P	MICROSOFT
5	TEA	34.0000	4.3800	2/29/2008	4/30/2008	P	SUN
6	TEA	45.0000	3.9900	3/31/2008	5/31/2008	P	SUN
7	TEA	79.0000	3.5000	4/30/2008	7/31/2008	P	SUN
8	TEA	36.0000	5.3800	6/30/2008	7/31/2008	P	SUN
9	TEA	12.0000	4.3800	7/31/2008	7/31/2008	P	SUN
10	TEA	50.0000	4.2200	8/30/2008	9/30/2008	P	SUN
11	CHOCOLATE	110.0000	0.6900	3/31/2008	4/30/2008	P	SUN
12	CHOCOLATE	130.0000	1.2200	4/30/2008	5/31/2008	P	SUN
13	CHOCOLATE	76.0000	0.5500	5/31/2008	6/30/2008	P	SUN
14	CHOCOLATE	200.0000	0.8900	6/30/2008	7/31/2008	P	SUN
15	CHOCOLATE	230.0000	0.2300	7/31/2008	8/31/2008	P	SUN
16	CHOCOLATE	210.0000	0.7800	8/31/2008	9/30/2008	P	SUN
17	CHOCOLATE	199.0000	1.1000	9/30/2008	10/31/2008	P	SUN
18	CHOCOLATE	102.0000	2.0000	2/29/2008	3/31/2008	P	MICROSOFT
19	CHOCOLATE	245.0000	2.0000	3/31/2008	4/30/2008	P	MICROSOFT
20	CHOCOLATE	312.0000	2.0000	4/30/2008	5/31/2008	P	MICROSOFT
21	CHOCOLATE	345.0000	2.0000	5/31/2008	6/30/2008	P	MICROSOFT
22	CHOCOLATE	307.0000	2.0000	6/30/2008	7/31/2008	P	MICROSOFT
23	CHOCOLATE	120.0000	2.0000	7/15/2008	7/31/2008	P	MICROSOFT
24	CHOCOLATE	300.0000	2.0000	7/31/2008	8/31/2008	P	MICROSOFT
25	CHOCOLATE	299.0000	2.0000	8/31/2008	9/30/2008	P	MICROSOFT
26	CHOCOLATE	290.0000	2.0000	9/30/2008	10/31/2008	P	MICROSOFT
27	CHOCOLATE	10.0000	2.0000	10/5/2008	10/31/2008	P	MICROSOFT
28	CHOCOLATE	12.0000	2.0000	10/15/2008	10/31/2008	P	MICROSOFT
29	CHOCOLATE	12.0000	2.0000	10/20/2008	10/31/2008	P	MICROSOFT

 

--Write a query to get the TOTAL AMOUNT SPENT on tea consumption by each company between 03/31/2008 and 08/31/2008

SELECT CUSTOMERNAME, SUM(QUANTITY*COST_PER_UNIT) FROM #TMP_SALES

WHERE ORDERDATE BETWEEN '03/31/2008' AND '08/31/2008'

GROUP BY CUSTOMERNAME

 

--QUERY TO FIND AVERAGE NUMBER OF DAYS EACH CUSTOMER GIVES FOR AN ORDER

SELECT CUSTOMERNAME, AVG(DATEDIFF(D, ORDERDATE, DUEDATE))

FROM #TMP_SALES

GROUP BY CUSTOMERNAME

 

--QUERY TO FIND THE MONTHS WHEN EACH CUSTOMER DID NOT PLACE

--ANY ORDERS

SELECT * FROM

#TMP_SALES WHERE SALESORDERID NOT IN

(

      SELECT T_OUTER.SALESORDERID

      FROM #TMP_SALES T_OUTER

      INNER JOIN #TMP_SALES T_INNER

      ON T_OUTER.CUSTOMERNAME = T_INNER.CUSTOMERNAME

      AND T_OUTER.ITEM_NAME = T_INNER.ITEM_NAME

      AND MONTH(T_OUTER.ORDERDATE) = MONTH(DATEADD(M, -1, T_INNER.ORDERDATE))

)