Business Intelligence Interview Questions in Conshohocken, PA
6K
Business Intelligence interview questions shared by candidates
50,000 shoppers with a 0.5% conversion rate for a chair that costs $250. Wayfair makes a 27% profit. Next, 50,000 shoppers will get a 10% discount. What is the conversion rate they must achieve to achieve the same profits as before?
13 Answers↳
This is incorrect Old revenue = 50000*250*.005=250*250 New revenue with conversion rate r% = 50000*r*250*.9 = old revenue = 250*250 r = .55% Less
↳
July 9 is wrong because the profit margin changes(As sale price changes, but the cost doesn't change) Less
↳
The new conversion rate is 0.944, Profit from case 2 = $16,875 and the third part, if this scenario actually occurred I would give a 10% discount (with the new conversion rate .944%) because the overall profit margin would remain same i.e. $16,875 Less
Describe the different parts of an SQL statement
2 Answers↳
SELECT for which columns to include in the result, (tables must be present in the FROM clause), can also include aggregates like COUNT(), SUM(), AVG() FROM for which tables to select rows/attributes from WHERE which is used to filter rows based on a given criteria with comparisons (=, !=, , and keywords LIKE and BETWEEN), INNER JOIN and LEFT/RIGHT OUTER JOIN to combine data across multiple tables ORDER BY DESC/ASC to specify ordering of a specific column name GROUP BY to group data based on the tables passed HAVING to filter rows based on an aggregate used in the SELECT statement that cannot be used with the WHERE clause Less
↳
I described a simple statement: Select (Name all columns from tables or use * for all) From (table name(s)) Where (any conditions, join statements ect) Group By / Order By to sort or aggregate the data Less
What is a clustered/non clustered index.
2 Answers↳
I explained how a clustered index was in sequential physical order on disk and a non clustered index used pointers or a hash table for index lookup. I started to explain the advantages/disadvantages of each index type but the two guys interviewing me just seemed so confused and lost. I think I would have been better of simply saying "it makes lookup faster". Less
↳
I explained how a clustered index was in sequential physical order on disk and a non clustered index used pointers or a hash table for index lookup. I started to explain the advantages/disadvantages of each index type and how I would use them. Less
Whats the difference between inner join and outer join?
2 Answers↳
Inner Join returns values where the key between the two tables are the same, and values are present in both tables. Outer Join returns the Values from both tables, based on the key, even if there is not any data the joining table. If not value is available, then NULL is returned for that specific Row Data based on the Key Less
↳
Specified in the WHERE clause, joins simply combine data from multiple tables in the result. INNER JOIN, the most common, returns the rows for which the given ON condition is satisfied for both tables. LEFT/RIGHT OUTER JOIN statements return all the rows from the specified table regardless if there is a match in the unspecified table, with the matching rows specified in the ON condition in the unspecified table. Less
What reporting tools have you used? Actuate, Brio, Crystal Reports? How much experience have you had in scheduling jobs on Unix? Can you write XML and implement it?
1 Answers↳
The interviewer was interested in breath of experience rather than specific technical skill. Unix work with scheduling and XML skills are necessary, but not heavy (the same procedures are done over and over again). Emphasis was on selling yourself through experience. Less
Explain/define "Weighted Average".
1 Answers↳
An average in which each quantity to be averaged is assigned a weight. These weightings determine the relative importance of each quantity on the average. Weightings are the equivalent of having that many like items with the same value involved in the average. Less
The typical "difficult" question that comes up in Business Intelligence is (and I'm paraphrasing), "What's the best data warehouse solution?".
1 Answers↳
It's a great question but analogous to asking, "What's the best house or the best car?". The answer depends on several factors. I'd start with requirements and budget and discuss what is possible within those parameters. If you watch "Property Virgins" on HGTV, the educational process is similar, as are the "buyers" perceptions. Less
Basic SQL questions. Describe a join to a non-technical person. How do you handle a query that does not perform quickly? They want to know that you can use 'explain plans', which I currently do not use (I'm still entry level). Select all customers who purchased at least two items on two separate days. Given a table with a combination of flight paths, how would you identify unique flights if you don't care which city is the destination or arrival location.
11 Answers↳
Customer problem: select customerId from orders group by customerId having count(distinct date(orderDate)) > 1; -- Assuming the orderDate has time associated with it. Flights problem: select arrival, departure from flights union select departure, arrival from flights; Less
↳
select distinct(b.id) from ( select a.id, a.d, a.#items, row_number() over (partition by id order by d) as rn from ( select id, d, sum(q) as #items from cust group by id, d having sum(q) >=2 ) a)b where b.rn>2 Less
↳
CREATE TABLE test_flights ( origin VARCHAR(255), destination VARCHAR(255) ); INSERT INTO test_flights (origin, destination) VALUES ('Boston', 'Los Angeles'), ('Los Angeles', 'Boston'), ('New York', 'Pittsburgh'), ('Pittsburgh', 'New York') SELECT * FROM test_flights WHERE origin < destination Less
probability of the product coming from location A is 0.8 and from location B is 0.6. What is the probability the customers will receive the product from location A or location B SQL - tested on different join , lead , lag, pivoting in sql , sub query, group by having, where, aggregate and think about how you would find outliers)
10 Answers↳
probability of the product coming from location A is 0.8 and from location, B is 0.6. What is the probability the customers will receive the product from location A or location B P(A)=0.8 P(B)=0.6 Assuming the events are independent: P(A OR B) = 1 - P(not A AND not B) = 1-(0.2*0.4) = 1-0.08 = 0.92 The other ways: P(A or B) = P(A) + P(B) - P(A AND B) = 0.8 + 0.6 - (0.8*0.6) = 1.4 - 0.48 = 0.92 OR P(A or B) = P(A) + P(B )*P(not A) = 0.8 + (0.6*0.2) = 0.8 + 0.12 = 0.92 OR P(A OR B) = P(B) + P(A)*P(not B) = 0.6 + (0.8*0.4) = 0.6 + 0.32= 0.92. Less
↳
helpful
↳
hint : P(A U B) = P(A) + P(B) - P(A and B)
Wayfair is going to send 2 different catalogs to their customers. One of the catalogs costs 50 cents to make and is 50 pages long. The conversion rate for the catalog is 5% and each customer brings in 315 dollars. The second catalog costs 95 cents to make, is 100 pages long and each customer brings in 300 dollars from it. The profit margin is 30%. What should the conversion rate for the second catalog be to make at least the same amount of profit as the first one. After you find the conversion rate for the second one, there is a second part of the problem. Wayfair is planning to make a new catalog which is going to cost 10 cents more than the 100 page one. The more expensive catalog is going to be sent out to 20% of the customers while the remaining 80% are going to get the 100 page one. Assume the same 30% profit margin and 300 dollar profit from each customer. What should the conversion rate for the new catalog be in order to receive the same profit at the end?
9 Answers↳
I got 5.75% for the first question and 5.86% for the second part of the question. I assumed we are sending out the catalogs to a total of 100 people and that the cost of the catalog is not calculated as part of the profit margin which means we can expect 5 people to buy from the catalogs (5*315*.3)-(.5*100) = 422.5 dollars in profit from the first catalog. Now 422.5 = 100x * .3 * 300 - (.95*100) = .0575. For the second part I assumed we are comparing the 20/80 split with the option of only sending the second catalog. So we make $338 from the second catalog (80% * 422.5) so now we just need to solve for x: 422.5 - 338 = 20x * .3 *300 - (20 * 1.05) leaving us with x = .0586 Less
↳
1) Conversion % for Catalog 2(.95 cents) = 5.25% ((Average Sale price($315) * Conversion rate))/New Average selling price ((315*5))/300 = 1575/300 = 5.25 2) Conversion % for Catalog 3(1.05 cents) = 21% Since Average selling price and profit margin are the same for both - IGNORE them. The ratio of the reach is 4:1 (80%:20%)... So, just by eyeballing, you know that the third catalog must perform 4 times better than the second one - which is, 5.25*4 = 21% Nothing is missing from the question, there's a lot of garb that you need to ignore to get the answer. Less
↳
part 1 : 5.4% part 2: 5.5% -don't use PM -just do a regular Revenue - Cost -no point multiplying Revenue with PM-> this would just yield profit instead of Revenue (as done in some questions above) Less