Re:Ask HN: What's your speciality, and what's your "FizzBuzz...
by seanalex on Thursday January 02, 2014 @01:24PM
(#7001160)
In SQL, this would be an issue due to the date column not existing in the group by. Additionally, because of the having statement, the left outer join is being treated as an inner join with respect to the results given.
Something like this could work (without going for an alternate approach such as Unions or sub-selects):
SELECT a.name, ISNULL(SUM(s.price), 0.0) FROM authors a
INNER JOIN books b ON (a.id=b.author_id)
LEFT OUTER JOIN sales s ON (b.id = s.book_id)
GROUP BY a.name, s.date
HAVING (ISNULL(s.date, '20131101') >= '20131101' AND ISNULL(s.date, '20131101') <= '20131130')
Edit: Actually might be better to keep the null in the results like the original query. It's definitely better to know whether a book sold 1000 copies for $0.00 each or if no copies were sold.
Re:Ask HN: What's your speciality, and what's your "FizzBuzz...
by hdennis on Thursday January 02, 2014 @12:53PM
(#7000924)
Instead of the Left Outer Join, couldn't you also make it an Inner Join to S.BOOK_ID? The question, if I read it correctly, only requires you to return sales in Nov 2013. If there is no data for a book_id, you want to exclude it from your returned data, no?
Re:Ask HN: What's your speciality, and what's your "FizzBuzz...
by seanalex on Thursday January 02, 2014 @01:30PM
(#7001198)
I would imagine it is up to interpretation. It would probably be a good thing to ask the interviewer about to show that you are thinking about edge cases.
If it were me, I'd prefer to see those that made no sales based on the question. I guess if I didn't want to see them, I would ask for "where sales are greater than $0.00".
Re:Ask HN: What's your speciality, and what's your "FizzBuzz...
by zAy0LfpBZLC8mAC on Thursday January 02, 2014 @12:50PM
(#7000900)
s.date is not a group column and a.name seems like a bad PK
- price could be 0.0 for a book
- Total sales means the sum and not count
- Date format will be yyyymmdd (if not, assume that we converted in Oracle or Sybase)