Another Data Analyst Interview – Oh Wow, Oh Wow, Oh Wow

Well, a couple of days ago I got through this other interview with a local nonprofit for a data analyst position. How the heck I got to be interviewed in that place g-d only knows. Anyways… This time I got the question answered correctly, but what a question it was! Sometimes I really wonder how can’t corporate America just teach ordinary computer users to do SQL joins? Can it really be that hard? Can’t you teach that in high school? Do you really need to have an ‘analytical’ mind to get through this? What is the meaning of ‘analytical’ mind anyway then?

You have four tables:

Invoices (Invoice#[pk], Date, Amount)

Transactions(Detail#, Transaction#[pk], Date, Amount)

Detail (Detail#[pk], Member#, Amount)

Member(Member#[pk], Name)

The question is to summarize invoice amount by member name. The first observation is that Invoices relation doesn’t have a suitable primary key found in other tables (in fact it only has one primary key, invoice# not found anywhere else). Then half way through the problem the interviewer says that you don’t actually have to use the invoices table, you can do without it. But you asked to summarize invoices, right? Hmmm….

Well, you see, Invoices table is really a roll up of Transactions by date. So the straightforward answer is to join Member, Detail and Transactions and summarize Amount by member name – the amount recorded in the Transactions table, because it’s the relation with the highest granularity of data, if you summarize the amount recorded on the member table then you double counting. Later I also thought that probably ‘Detail’ relation is a summary of ‘Transactions’ relation by Detail and Member, so I could have used this as well.

So is that it? ‘You got it, you talked it out’.

I’m like – ok. No questions about one-to-one, one-to-many, many-to-many? No hidden dependencies? I mean c’mon! This should have been a real puzzle. Surprisingly, the interviewer is from MIT and worked in Amalgamated Insurance.

Ymg’bf lzs lz ae owzckxd pt. B hbfxp X’it iias gt mtx lqvzq, mttmqlpg oh ph.


~ by Monsi.Terdex on February 1, 2013.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Normal Boy

Nothing out of the ordinary

Data Engineering Blog

Compare different philosophies, approaches and tools for Analytics.

%d bloggers like this: