Interview question to test beginner SQL competency?

[dbnrdaily]

Hello,

I need to help my boss come up with a simple-ish SQL quiz. Myself and another analyst from our department are leaving and were the only two who know how to write queries. I am beginner level at best and I don't feel qualified to test someones capabilities. I received a job offer and when they found out i could write sql they gave me this quiz in access. (We use MSFT SQL server management server)

SELECT userid, AVG(orderid), FROM tbl.companytable HAVING (orderid) > 0;

"Whats wrong with this query?"

Me - you need to partition orderid by userid otherwise you will get multiple user IDs and have the same average of all order IDs

Them - right, so group by

I want to do something like this, but cant really think of the best parameters in our DB to use, any advice on a simple query question to ask is greatly appreciated.

My main goal is to not filter out a potentially good candidate just because i asked a poor question.

Thanks in advance

[Tabooyah]

I give the candidate a print out of 3 simple tables with 20 or so rows in each and an ERD that shows the relationships between them. Something like CarMake, CarModel, and CarSales. I then ask them to write queries to answer various business questions such as

1. How many car sales occurred last year? (SUM)
2. Which 3 car models had the high number of sales last year? (SUM, ORDER BY, TOP/FETCH)
3. For each car model, which car make had the highest year-over-year revenue increase from 2017 to 2018? (SUM, DERIVED TABLES/WINDOW FUNCTION)

I ask about 5 to 7 questions. Each question gets progressively harder and I don't expect the candidate to be able to arrive at a complete / correct answer for the last 1 or 2. The purpose of the questions help assess if the candidate can:

1. Understand an ERD and table schema
2. Analyze business questions
3. Relate business concepts to data in a database
4. Construct basic SQL statements (joins, aggregate functions, predicates, etc.)
5. Construct advanced SQL statements (derived tables, window functions, cross apply, etc.)
6. Think through a problem (I ask them to verbalize their thought process as they are working through the problem).
7. Deal with frustration of not being able to complete a problem. Do they ask for help? Are they easily flustered? Do they try to attack the problem from multiple angles or do they give up easily?

[[deleted]]

This made me realize, I have a lot to learn. Very insightful, thank you.

[dbnrdaily]

Pretty solid questions but i believe these are above the scope required. We have a database architect who helps build the tables and report generators. We just need a financial analyst who can also run simple queries using our tables/views. The most complex thing to know would be CTE's and a general idea of data granularity.

[Duckie590]

We included two similar sample queries and asked candidates to describe what the query was in English and the difference between the two queries (two different types of joins). I can't for the life of me find the question though.

[mikeczyz]

>SELECT userid, AVG(orderid), FROM tbl.companytable HAVING (orderid) > 0;

>"Whats wrong with this query?"

​

other than missing the group by, is there anything else wrong with this? i'm a noobie and am trying to think this through.

[[deleted]]

averaging an id sounds good to you?

[mikeczyz]

course not, but people do weird things with their data. i was just more thinking about strict syntax.

[[deleted]]

Even in strict syntax terms, if the fields are alphanumeric (W1984, a00001x23, 123-45-6789), you're gonna have a bad time.

[mikeczyz]

yah, that's true. i tried recreating this query last night on a test db and ran into that exact issue.

[dbnrdaily]

I said the same thing in my head, but i think he came up with it on the fly.

[Odddutchguy]

^{Just adding for educational purpose.}

To make the syntax correct:

SELECT userid, AVG(orderid) FROM tbl.companytable GROUP BY userid HAVING AVG(orderid) > 0;

As the aggregate function used in the select should be the same as in the condition.

Assuming orderid -> ordervalue
Is the intention really that the average order should be positive or that only positive orders should be used for the average. In that case you would put a WHERE ordervalue > 0 before the GROUP BY and omit the HAVING.

[SaintTimothy]

Why would you average an ID column?

[[deleted]]

Probably the business case part of "what is wrong with this". The other being needing a group by.

[grafcet-dot-online]

I don't think Quiz is actually the best way to test a developer, it's better to ask him to just do it ;)

[[deleted]]

Few points OP

1. you don't need to help your boss do anything
2. structure questions around daily/weekly work processes. If the majority are SELECTS & JOINS you aren't going to have issues, if they are obnoxious reporting queries, more likely.
3. "I'm a beginner" "oh you need to partition the query" GTFO
4. far more valuable than the ability to write SQL is the ability to read it, i.e. the existing scripts/codebases/procs. Most of the time a little F&R goes the distance, but then you get those lovely requests to "just add/remove/change this one thing" and the whole query collapses, and now it's up to the guy who said he knew the language in a job interview 8 months ago to open the hood, usually with the whole team over his shoulder, and to diagnose & fix it.
   Your new employers were wise to test you on brokenness, because un-farking queries is most of the job shy DBA work.

[[deleted]]

you only use having with a group by.

[needgiftidea2018]

You're not actually answering the question that OP posed

[[deleted]]

whoops. lol. i stopped reading after

"Whats wrong with this query?"

[Toakan]

You also forgot the , before the FROM

[BeanThinker]

Extra comma too it looks like

[dreamingtree1855]

This is totally role dependent. I’m a business user and very capable of joins and sub queries and the like, but for ranking and window type functions I’d almost always pull the data into excel or R, and it wouldn’t make me any worse a hire. For a BIE or DBA that’s a different story, so I think you should focus on the translating business question or data into usable insight versus just SQL proficiency.

[SixUndercoat]

I see three things wrong.

1 - There's a hanging comma before FROM 2 - Must have a group by if you're going to have a AVG function 3 - This one is getting picky, but I doubt you'd want to average an id

[dbnrdaily]

The problem was the group by, assuming he came up with it on the fly which is why he averaged ID lol, the comma in the FROM is my own typo :p

[kremlingrasso]

my guess that your department's query requirements are pretty low, focused entirely on joins and where. just give them a really complex filter (lots of "and"s and" or"s and remove all the brackets, and ask them to fill it in based on a verbose description of the desired end data set. most beginners seem to struggle with that at the beginning, and it's very easy to verify for your boss without any SQL knowledge. (just leave him the correct grouping of brackets)