question

Ashi avatar image
Ashi asked

There is 2 tables, tbl 1:Cust_table, tbl 2: Order_table

column in table 1: cust_id, name, location , age, marital status(single, married, widowed) Column in table 2: Order id, Cust_id, order amount, month of purchase Questions: 1. In a month how many times does a person place orders 2. Find the monthly expenses of senior citizen who are widowed 3. Find the month of highest purchase for all single persons 4. People from which locations have the max no of orders placed in a month and what is total expense?
interview-questionsordering
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

But if you are not the right person to be setting the questions how can you handle if someone answers 'differently' from what someone might post here? It won't help you recruit the right person? I know that's not what you want to hear, but I just find it odd that you are going to base (partly) a decision to hire someone on a set of questions that you yourself couldn't answer. If SQL knowledge is important for the job role, then there must be someone in your organisation who could help you?
1 Like 1 ·
Just to throw this out there, if there isn't someone in your organization that is knowledgeable in SQL, some consulting firms (that have SQL skills) will offer technical interview screenings for a fee. Might be an worthwhile cost to ensure that the individual is getting properly vetted.
1 Like 1 ·
Homework? What have you tried so far?
0 Likes 0 ·
I'm preparing question and answer for company recruitment, actually i work in java, so no idea about Sql and no idea why they told me to do this, so thought of taking help
0 Likes 0 ·
Sounds like you're in line to become an "Accidental DBA". Or, at least, an accidental SQL developer...
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.