The Friday Fragment

It's Friday, and time again for a new Fragment: my weekly programming-related puzzle.

This Week's Fragment

This week's fragment is programming-related (by a stretch), and is borrowed from a recent Car Talk episode. A listener there wrote how he didn't get a programming job partly because he failed to solve the following interview puzzle within the allotted five minutes:

You have a four ounce glass, a nine ounce glass and an unlimited supply of water, which can be poured and dumped as much as needed. Can you measure exactly six ounces of water in as few steps as possible?

If you want to “play along”, post the solution as a comment or send it via email. To avoid “spoilers”, simply don’t expand comments for this post. It's your chance to demonstrate your qualifications for a job as a programmer. Or at least a soda jerk.

Last Week's Fragment - Solution

Last week was another SQL challenge, where IRS programmer Owen Moore had trouble joining his Employee table to the EmployeeAddress table. When he ran his SQL, he found that it dropped some of the employees: those who did not have addresses on file. He doesn’t mind having multiple rows for one employee (whenever there are multiple addresses), but the IRS never wants to overlook a taxpayer. His SQL was:

select companyid, e.employeeid, hiredate, address
from employee e, employeeaddress ea
where e.employeeid = ea.employeeid
order by companyid, hiredate

Owen's bane was that, by default, SQL joins are inner joins, meaning that results are included only if there are matching rows in both tables. Owen needs an outer join, so that it includes all the rows from the Employee table, even if there aren't matching rows in the EmployeeAddress table (the EmployeeAddress column values will be null were the rows are missing). Outer joins can be left joins or right joins, depending on the order you list the tables. Owen reads and thinks left-to-right, so he'll list the Employee table first and use a left join, like so:

select companyid, e.employeeid, hiredate, address
from employee e
left join employeeaddress ea
on e.employeeid = ea.employeeid

Congratulations to Spencer for quickly spotting the problem and proposing the solution. For that, he gets an extra hour to file his quarterly estimated tax payments.

If you've been following along, you know this was part of a larger SQL for Owen's "pink slip pick list" report which shows IDs and addresses for all but the 49 most senior employees in each company. The full SQL with the left join is now:

select companyid, e.employeeid, hiredate, address
from employee e
left join employeeaddress ea
on e.employeeid = ea.employeeid
where
not exists
 (select *
 from employee ei
 where ei.companyid = e.companyid
 and ei.employeeid = e.employeeid
 and 49 >
    (select count(*) from employee eii
    where eii.companyid = ei.companyid and
    eii.hiredate < ei.hiredate))