November 29, 2020 at 9:46 pm
ScottPletcher wrote:I provide the table structure, that's it. They are in SSMS session, so they can use whatever SSMS tools there are to view that structure.
Ah... therein lies the key. Didn't know it was going to be an actual SSMS sesssion. Thanks for the feedback, Scott... totally agreed.
Why would that matter really? Isn't the set up already enough to write the query, assuming an experienced developer, as you are?
Btw, I messed up the set up: the count of scores is not in the original request, only the avg, high, middle and low.
So, here's the data setup, followed by the query I roughly expect a good candidate to first turn in / try:
IF OBJECT_ID('dbo.student_scores') IS NOT NULL
DROP TABLE dbo.student_scores;
CREATE TABLE dbo.student_scores
(
student_id int NOT NULL,
asssignment_number bigint NOT NULL,
score int NOT NULL
);
TRUNCATE TABLE dbo.student_scores;
INSERT INTO dbo.student_scores VALUES
(1,184,80), (1,235,60), (1,481,95),
(2,623,80), (2,819,100),
(3,944,65), (3,1017,82), (3,1139,76),
(4,1372,70),
(5,2471,98), (5,2553,94), (5,2614,95), (5,2712,95),
(6,3001,44), (6, 3487,55);
SELECT
student_id,
--COUNT(*) AS score_count,
CAST(AVG(score * 1.0) AS decimal(4, 1)) AS avg_score,
MAX(score) AS highest_score,
CASE WHEN COUNT(*) = 1 THEN NULL
WHEN COUNT(*) = 2 THEN MIN(score)
ELSE SUM(score) - MAX(score) - MIN(score) END AS middle_score,
CASE WHEN COUNT(*) = 3 THEN MIN(score) ELSE NULL END AS lowest_score
FROM dbo.student_scores
GROUP BY
student_id
Here are the results:
student_id avg_score highest_score middle_score lowest_score
1 78.3 95 80 60
2 90.0 100 80 NULL
3 74.3 82 76 65
4 70.0 70 NULL NULL
5 95.5 98 190 NULL
6 49.5 55 44 NULL
Hopefully when they see that result, they will change the query to roughly this:
SELECT
student_id,
COUNT(*) AS score_count,
CAST(AVG(score * 1.0) AS decimal(4, 1)) AS avg_score,
MAX(score) AS highest_score,
CASE WHEN COUNT(*) = 2 THEN MIN(score)
WHEN COUNT(*) = 3 THEN SUM(score) - MAX(score) - MIN(score) /*maybe they get this, maybe not*/
ELSE NULL END AS middle_score,
CASE WHEN COUNT(*) >= 3 THEN MIN(score) ELSE NULL END AS lowest_score,
CASE WHEN COUNT(*) > 3 THEN 'Error, more than 3 scores, cannot accurately calculate middle score.' ELSE '' END AS message
FROM dbo.student_scores
GROUP BY student_id
ORDER BY student_id --optional, of course
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 29, 2020 at 9:58 pm
The "trick" part of the query is determining the middle score. Many developers don't figure that one out.
In my own way I tried to help (sort of), since you can assume that the only data you know is in the final query.
Then the q becomes: "looking at the query results, what can I do with those values, and those values only, to calc the middle score?"...
Btw, if it makes the initial query q seem clearer/fairer to you, add this to the setup:
"Assume all scores range in value from 0 to 110 (more than 100 since bonus points are possible on some tests)."
But note that Jeff got the range of values right, even without that comment, for when he generated the test data:
"Domain of possible scores is 60 thru 100"
Exactly! We've all taken tests in the past and know that, in general, scores would be from 0 to 100 unless otherwise stated.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 29, 2020 at 10:10 pm
Jeff Moden wrote:ScottPletcher wrote:I provide the table structure, that's it. They are in SSMS session, so they can use whatever SSMS tools there are to view that structure.
Ah... therein lies the key. Didn't know it was going to be an actual SSMS sesssion. Thanks for the feedback, Scott... totally agreed.
Why would that matter really? Isn't the set up already enough to write the query, assuming an experienced developer, as you are?
Because if this were a question being asked without the benefit of being able to explore a bit in SSMS and, as you say, being the kind of person that I am, I might suggest adding a Clustered PK for performance and would need to know the answer to the question prior to suggesting that because, in this case, the Student_ID column is not unique but the combination of Student_ID and Assignment_ID might be.
Again, having the table in SSMS would answer that question but might such information might not be provided in a discussion simply because the interviewers probably aren't looking for such a suggestion. Depending on the nature of the interviewers, it's sometimes a really good thing to make such a suggestion, if for no other reason, than to show that you're paying attention and can make a difference.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 12:08 am
ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:I provide the table structure, that's it. They are in SSMS session, so they can use whatever SSMS tools there are to view that structure.
Ah... therein lies the key. Didn't know it was going to be an actual SSMS sesssion. Thanks for the feedback, Scott... totally agreed.
Why would that matter really? Isn't the set up already enough to write the query, assuming an experienced developer, as you are?
Because if this were a question being asked without the benefit of being able to explore a bit in SSMS and, as you say, being the kind of person that I am, I might suggest adding a Clustered PK for performance and would need to know the answer to the question prior to suggesting that because, in this case, the Student_ID column is not unique but the combination of Student_ID and Assignment_ID might be.
Again, having the table in SSMS would answer that question but might such information might not be provided in a discussion simply because the interviewers probably aren't looking for such a suggestion. Depending on the nature of the interviewers, it's sometimes a really good thing to make such a suggestion, if for no other reason, than to show that you're paying attention and can make a difference.
Frankly, you're still totally missing the point of q, thus your qs are misdirected. Performance does not matter here, since the company you're interviewing for is a commercial business, it has no students and never will. It's just a hypothetical upon which to write a query representative of your skills. Clustering is 100% irrelevant in this case. Other parts of a later interview will deal with things like that.
The interview process now is just too ridiculously long, in my view, but that is the style today. Back in the day you would be offered a job at the end of the first interview, never so nowadays.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2020 at 12:58 am
Frankly, you're still totally missing the point of q, thus your qs are misdirected. Performance does not matter here, since the company you're interviewing for is a commercial business, it has no students and never will. It's just a hypothetical upon which to write a query representative of your skills.
I guess we'll have to agree to disagree on that, Scott. Two of the skills being measuring during a technical interview, whether intentionally and specifically measured or not, are the ability to spot possible improvements and the ability to communicate them so that others understand them all while demonstrating the right attitude.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 4:37 am
I have a major problem with ego-maniacs that like to hijack meetings. Which is what we had here. I asked for a few practice tests so I can prepare for my interview, and as always, the ego-maniac jumps in and starts trying to make everyone look bad. I call these people energy vampire's. I have struggled in my career to avoid falling into the energy vampires trap, but time and time again, it happens. I should not have responded to this post, but yet I did, which means that the EV won and I lost again. The energy vampire is intelligent, but he uses his smarts to set people up so he can look good in front of his boss and higher ups instead of trying to be a team player.
The energy vampire loves causing shit in meetings so he can force people into a debate. This is my biggest challenge. At this stage all of my good energy is consumed by some jack-wagon telling me that I need to learn how to write DDL scripts. These people are toxic to team environments. Who are you trying to impress? Are you angry because you are out of work? Are you getting old like the rest of us and are afraid that you might not have a job tomorrow? Teamwork is what YOU need to learn, not DDL scripts and fancy SQL coding.
Forget about the practice tests... My new question is... "How do you co-exist with an energy vampire who is just waiting to throw you under the bus?" Perhaps I should focus on that?
I just want to say that you have called out Jeff for no reason. He is not an energy vampire, he is a very knowledgeable person that you should learn from. He has been a mentor to me for last the fifteen years and has taught me many concepts that have allowed me to become a better SQL Server DBA and Developer.
December 11, 2020 at 8:54 am
This was removed by the editor as SPAM
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply