question

I really agree with the comment that there are different way of solving a T-SQL problem and there is no way beforehand to say which one is the best way to solve it. I have put together one suggestion per question, but even the requirements are somewhat vague so I have already made some assumptions, like assuming that you want the results for a specific month for question 1 and 4. Even with clearer requirements, there is no way to say which way to solve a specific problem is the best. My suggestions are certainly not the only way, and probably not the best way, to answer the questions. If you are to test the SQL skills of an interviewee, please get assistance from someone who is really good at T-SQL. --In a month how many times does a person place orders declare @month int = 201205; declare @cust_id int = 1; SELECT COUNT(*) as NumberOfOrders FROM t2 WHERE cust_id = @cust_id AND month_of_purchase = @month; --Find the monthly expenses of senior citizen who are widowed DECLARE @LowerAgeBoundForSeniorCitizen tinyint= 65; SELECT SUM(order_amount) as SumOrderAmountForWidowedSeniors FROM t2 INNER JOIN t1 ON t2.cust_id = t1.cust_id WHERE t1.age >= @LowerAgeBoundForSeniorCitizen AND t1.marital_status = 'W'; --Find the month of highest purchase for all single persons WITH CTE AS ( SELECT SUM(order_amount) as SumOrderAmountPerMonth, cust_id, month_of_purchase FROM t2 GROUP BY cust_id, month_of_purchase ), CTE2 AS( SELECT SumOrderAmountPerMonth, cust_id, month_of_purchase, ROW_NUMBER() OVER(PARTITION BY cust_id, month_of_purchase ORDER BY SumOrderAmountPerMonth DESC) as rownum FROM CTE ) SELECT SumOrderAmountPerMonth as SumOrderAmount, cust_id, month_of_purchase FROM CTE2 WHERE rownum=1; declare @month int = 201205; --People from which locations have the max no of orders placed in a month and what is total expense? WITH CTE AS ( SELECT COUNT(*) as OrdersPerLocationForMonth, SUM(t2.order_amount) as OrderAmountPerLocationForMonth, t1.location FROM t1 INNER JOIN t2 ON t1.cust_id = t2.cust_id WHERE t2.month_of_purchase = @month GROUP BY t1.location ), CTE2 AS( SELECT OrdersPerLocationForMonth, OrderAmountPerLocationForMonth, location, ROW_NUMBER() OVER(PARTITION BY location ORDER BY OrdersPerLocationForMonth desc) as rownum FROM CTE )SELECT OrdersPerLocationForMonth, OrderAmountPerLocationForMonth, location FROM CTE2 WHERE rownum=1; And by the way - I have not spent any time producing test data for the queries, so they are untested and possibly (probably even) wrong. They are written from the top of my head, without knowing anything about datatypes, and I probably have messed up some sort order in parts of the queries. If you would provide some INSERT-scripts for test data along with a desired result for each Query, you are more likely to get answers which at least solves the specific requirements for each Query.