Bloomberg LP Interview Question for Software Engineer / Developers






Comment hidden because of low score. Click to expand.
1
of 1 vote

This will definitely work.

Select [MonthID] AS 'Year', [1980] AS '1980', [1981] AS '1981', ... [2009] AS '2009'
FROM
(select MonthID, YearID, Amount from Ledger) ps
PIVOT
(
sum(Amount)
For YearID in
( [1980], [1981], [1982], ... [2009])
) AS pvt

I dont think that there is a dynamic way of specifying 1980, 1981, ... 2009. Anyone?

- Altruist November 13, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select month, year, sum(amount) from ledger group by year, month;

- group by year, month July 07, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

That doesn't work. Your select query will return results like:

JAN, 1980, $$
JAN, 1981, $$
...
DEC, 2009, $$

The original question is asking you to transpose the results. So that you get 1 month per row with years as the columns.

The answer is to do it with a self join:

SELECT led.month, led1980.amount, led1981.amount [...] led2009.amount
FROM ledger led
  JOIN ledger led1980 ON led.month=led1980.month and led1980.year=1980
  JOIN ledger led1981 ON led.month=led1981.month and led1981.year=1981
[...]
  JOIN ledger led2009 ON led.month=led2009.month and led2009.year=2009

Unfortunately, you can't do this dynamically using standard sql.

- Tazzy July 15, 2009 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

Assuming the shown text is 100% accurate:

It's impossible!
1. What Tazzy said.
2. Month is varchar(2). It's impossible to make it show 3 letters.

- ml July 17, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

The following syntax won't be 100%. Somebody check cuz I'm lazy.

select l.Month, sum(Amount) ' Or average, or whatever operation u like
from LEDGER l
group by l.Month
pivot l.Year

- wihenao August 13, 2009 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

pivot is the right solution for this question

- Anonymous March 27, 2010 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

select v.month, max(v.y_1980), max(v.y_1981),max(v.y_1982), max(v.y_1983)
from
(
select month,
decode(lg.year, '1980', lg.amount) y_1980,
decode(lg.year, '1981', lg.amount) y_1981,
decode(lg.year, '1982', lg.amount) y_1982,
decode(lg.year, '1983', lg.amount) y_1983
from ledger lg
) v
group by v.month

- Anonymous January 06, 2011 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

It is very easy to solve no big deal. I will wait for few more months to see anyone get this if not will post.

- KrishFunBoy August 15, 2014 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More