October 18, 2018 at 8:47 pm
Jeff Moden - Thursday, October 18, 2018 8:44 PMsaravanatn - Thursday, October 18, 2018 8:33 PMScottPletcher - Thursday, October 18, 2018 2:25 PMI suggest starting with your actual solution, not listing things you would not do first. You can offer to explain why you wouldn't use other possible solutions if they're interested in that. I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.I have to give best possible solution in the first and then explain him why I chose that particular answer instead of other possible solutions . Is my understanding correct?
Like anything else, I'll say "It Depends". It mostly depends on how you "read the room" Like I said, I've been on interview teams that have people that stop listening if they don't hear what they think the solution should be even though, if they listened, they might be hearing a better answer.
Exactly
Saravanan
October 22, 2018 at 3:19 pm
Just for fun I thought I'd throw in a solution that leverages NGrams8k. This will likely be a little slower than what Jason posted but it parallelizes nicely and can be used to replace substrings or any length with a substring of equal length.
DECLARE
@find VARCHAR(100) = 'o',
@replace VARCHAR(100) = 'A',
@instance INT = 3;
SELECT
s.productid,
s.productdescription,
answer = COALESCE(STUFF(f.productdescription,f.position,LEN(@replace),@replace),s.productdescription)
FROM #store AS s
LEFT JOIN
(
SELECT s.productid, s.productdescription, ng.position,
rn = ROW_NUMBER() OVER (PARTITION BY s.productid ORDER BY ng.position)
FROM #store AS s
CROSS APPLY samd.NGrams8k(s.productdescription,LEN(@find)) AS ng
WHERE ng.token = @find
) AS f ON s.productid = f.productid AND f.rn = @instance;
If I change the parameters like so (for example):DECLARE
@find VARCHAR(100) = 'is',
@replace VARCHAR(100) = 'XX',
@instance INT = 2;
I can replace the second instance of "is" with "XX"
-- Itzik Ben-Gan 2001
February 11, 2019 at 10:09 am
Alan.B - Monday, October 22, 2018 3:19 PMJust for fun I thought I'd throw in a solution that leverages NGrams8k. This will likely be a little slower than what Jason posted but it parallelizes nicely and can be used to replace substrings or any length with a substring of equal length.
DECLARE
@find VARCHAR(100) = 'o',
@replace VARCHAR(100) = 'A',
@instance INT = 3;SELECT
s.productid,
s.productdescription,
answer = COALESCE(STUFF(f.productdescription,f.position,LEN(@replace),@replace),s.productdescription)
FROM #store AS s
LEFT JOIN
(
SELECT s.productid, s.productdescription, ng.position,
rn = ROW_NUMBER() OVER (PARTITION BY s.productid ORDER BY ng.position)
FROM #store AS s
CROSS APPLY samd.NGrams8k(s.productdescription,LEN(@find)) AS ng
WHERE ng.token = @find
) AS f ON s.productid = f.productid AND f.rn = @instance;If I change the parameters like so (for example):
DECLARE
@find VARCHAR(100) = 'is',
@replace VARCHAR(100) = 'XX',
@instance INT = 2;
I can replace the second instance of "is" with "XX"
Sorry Alan I should have read Ngrams8K before 4 month . It is well thought and well written article. You put lot of efforts in writing that article. Thanks!!!!
Saravanan
February 11, 2019 at 12:24 pm
saravanatn - Monday, February 11, 2019 10:09 AMAlan.B - Monday, October 22, 2018 3:19 PMJust for fun I thought I'd throw in a solution that leverages NGrams8k. This will likely be a little slower than what Jason posted but it parallelizes nicely and can be used to replace substrings or any length with a substring of equal length.
DECLARE
@find VARCHAR(100) = 'o',
@replace VARCHAR(100) = 'A',
@instance INT = 3;SELECT
s.productid,
s.productdescription,
answer = COALESCE(STUFF(f.productdescription,f.position,LEN(@replace),@replace),s.productdescription)
FROM #store AS s
LEFT JOIN
(
SELECT s.productid, s.productdescription, ng.position,
rn = ROW_NUMBER() OVER (PARTITION BY s.productid ORDER BY ng.position)
FROM #store AS s
CROSS APPLY samd.NGrams8k(s.productdescription,LEN(@find)) AS ng
WHERE ng.token = @find
) AS f ON s.productid = f.productid AND f.rn = @instance;If I change the parameters like so (for example):
DECLARE
@find VARCHAR(100) = 'is',
@replace VARCHAR(100) = 'XX',
@instance INT = 2;
I can replace the second instance of "is" with "XX"Sorry Alan I should have read Ngrams8K before 4 month . It is well thought and well written article. You put lot of efforts in writing that article. Thanks!!!!
Thanks for the kind words!
-- Itzik Ben-Gan 2001
February 11, 2019 at 12:53 pm
saravanatn - Thursday, October 18, 2018 8:42 PMJeff Moden - Thursday, October 18, 2018 4:01 PMScottPletcher - Thursday, October 18, 2018 2:25 PMI suggest starting with your actual solution, not listing things you would not do first. You can offer to explain why you wouldn't use other possible solutions if they're interested in that. I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.It does depend on the environment that you're interviewing in. I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer. In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.
The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉
Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.
SELECT MAX(salary) FROM employee
WHERE salary NOT IN ( SELECT MAX (salary) FROM employee)
If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 11, 2019 at 1:03 pm
Michael L John - Monday, February 11, 2019 12:53 PMsaravanatn - Thursday, October 18, 2018 8:42 PMJeff Moden - Thursday, October 18, 2018 4:01 PMScottPletcher - Thursday, October 18, 2018 2:25 PMI suggest starting with your actual solution, not listing things you would not do first. You can offer to explain why you wouldn't use other possible solutions if they're interested in that. I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.It does depend on the environment that you're interviewing in. I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer. In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.
The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉
Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.
SELECT MAX(salary) FROM employee
WHERE salary NOT IN ( SELECT MAX (salary) FROM employee)If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!
Assuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:
SELECT TOP (1) salary FROM ( SELECT TOP (2) salary FROM employee ORDER BY salary DESC ) AS qry
ORDER BY salary
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 14, 2019 at 2:05 am
I was just asked for a job. And was asked if I could help with an overview of some info. Told how I would solve the problem with a simple solution. I was thanked and didn't get the job.
Ben
I was just asked for a job by a collegue, with my anwser he could solve the problem himself in a simple way, so he did not need me for this job.
This time:
Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime.
Next time:
Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.
(Free after Terry Pratchett)
February 14, 2019 at 2:32 am
ScottPletcher - Monday, February 11, 2019 1:03 PMAssuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:
What would be the function of an index on salary ?
(I can immagine overviews or that during processing the height of the salary has in impact (for example in the where clause), but can not imagine an index which would be really usefull. Even for a large company say a 100 000 employee's, I can not think of a single really usefull function. )
Ben
February 14, 2019 at 2:40 am
ScottPletcher - Monday, February 11, 2019 1:03 PMMichael L John - Monday, February 11, 2019 12:53 PMsaravanatn - Thursday, October 18, 2018 8:42 PMJeff Moden - Thursday, October 18, 2018 4:01 PMScottPletcher - Thursday, October 18, 2018 2:25 PMI suggest starting with your actual solution, not listing things you would not do first. You can offer to explain why you wouldn't use other possible solutions if they're interested in that. I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.It does depend on the environment that you're interviewing in. I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer. In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.
The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉
Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.
SELECT MAX(salary) FROM employee
WHERE salary NOT IN ( SELECT MAX (salary) FROM employee)If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!
Assuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:
SELECT TOP (1) salary FROM ( SELECT TOP (2) salary FROM employee ORDER BY salary DESC ) AS qry
ORDER BY salary
Thanks Scott .
Saravanan
February 14, 2019 at 2:44 am
Michael L John - Monday, February 11, 2019 12:53 PMsaravanatn - Thursday, October 18, 2018 8:42 PMJeff Moden - Thursday, October 18, 2018 4:01 PMScottPletcher - Thursday, October 18, 2018 2:25 PMI suggest starting with your actual solution, not listing things you would not do first. You can offer to explain why you wouldn't use other possible solutions if they're interested in that. I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.It does depend on the environment that you're interviewing in. I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer. In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.
The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉
Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.
SELECT MAX(salary) FROM employee
WHERE salary NOT IN ( SELECT MAX (salary) FROM employee)If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!
But it depends upon the person their knowledge and their attitude. Some people are happy with what they got and some others are ambitious . You can't blame both .
Saravanan
February 14, 2019 at 7:31 am
Michael L John - Monday, February 11, 2019 12:53 PMsaravanatn - Thursday, October 18, 2018 8:42 PMJeff Moden - Thursday, October 18, 2018 4:01 PMScottPletcher - Thursday, October 18, 2018 2:25 PMI suggest starting with your actual solution, not listing things you would not do first. You can offer to explain why you wouldn't use other possible solutions if they're interested in that. I've interviewed lots of people, and believe me, keeping your initial answer short and to the point is appreciated.It does depend on the environment that you're interviewing in. I have been in interviews (as one of the interviewers) where the other people conducting the interview were looking for a particular answer. In the follow up meeting after the interview, they wanted to give someone that actually did provide a (much!) better answer the "sack" because some of them didn't actually understand the answer and some of the others simply stopped listening to the answer because the key word(s) they were looking for weren't used.
The other thing is that if they're not actually interested in the things that you shouldn't be using, you might not want to work there. 😉
Ha ha . This has happen to me .They asked me to find second highest salary of a employee. I given answer using rank and top function.Frankly speaking he didn't know rank function. Then I given below answer and interviewer finally agreed with the answer and selected me.
SELECT MAX(salary) FROM employee
WHERE salary NOT IN ( SELECT MAX (salary) FROM employee)If these are the kinds of questions they are asking on interviews, then I would probably cross them off my list of places I would want to work!!!
Why? They're just asking rudimentary questions to test the interviewee's knowledge of T-SQL in a non-esoteric fashion. It's why I start with the "How do you get the current date and time using T-SQL" question in every interview. I typically follow that with a series of very simple questions that any "fresher" should be able to answer (only to find out people that claim 10 years of experience don't know). I'll also tell you that these introductory/get comfortable questions are the same whether I'm interviewing a DBA, Database Developer, or Application/Web Developer.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2019 at 8:09 am
ben.brugman - Thursday, February 14, 2019 2:32 AMScottPletcher - Monday, February 11, 2019 1:03 PMAssuming an index on salary, neither of those answers works that well, since you don't need to read all the rows:What would be the function of an index on salary ?
(I can immagine overviews or that during processing the height of the salary has in impact (for example in the where clause), but can not imagine an index which would be really usefull. Even for a large company say a 100 000 employee's, I can not think of a single really usefull function. )
Ben
If a place continually looked at top 3, say, salaries? Or compared the top n salaries of one category of employee to another. My first job was at a public university, and they had endless request for top salaries by race and gender by job code, etc.. It's like the entire Board of Regents spent all their time comparing salaries to, I guess, try to "balance" them out.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply