Business Intelligence Analyst Interview Questions
"Business intelligence analysts use their analysis of market trends and internal data to recommend where a business can improve profits and reduce expenses. Prepare for your interview by brushing up on your financial math and SQL skills, as well as your knowledge of the company's competitive space. A few years of experience and a degree in business, finance, or computer science is required for this position."
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)
Derive customer's account status as of month end for all the months in 2019. If for given month, there are more than one rows, pick the data from the latest date within the month. If for given month, there is no data, pick the data from latest date prior to the month You can use last_day function to get month ending date(Eg: last_day(01/01/2015) = 01/31/2015) customer_id event_date status credit_limit 1 1/1/2019 C 1000 1 1/5/2019 F 1000 1 3/10/2019 1000 1 3/10/2019 1000 1 8/27/2019 L 1000 2 1/1/2019 L 2000 2 1/5/2019 2500 2 3/10/2019 2500 3 1/1/2019 S 5000 3 1/5/2019 6000 3 3/10/2019 B 5000 4 3/10/2019 B 10000
9 Answers↳
select customer_id, last_day(a.event_date) month, coalesce( a.status,b.latest_status_ever) latest_status_per_month from table a join( -- latest if no record select customer_id, last_day(event_date) month, status as latest_status_ever from table QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY event_date desc) = 1 group by 1,2 )b on a.customer_id = b.customer_id and last_day(a.event_date) > b.month QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id, month(event_date) ORDER BY event_date desc) = 1 group by 1,2 Less
↳
Try this - Assumptions - The first record for a customer begins in the month it first had a status. Data is restricted to 2019. End month for all customers will be Dec 2019. All the status provided are valid status even including NULL status. with temp_data as ( SELECT 1 as customer_id, '1/1/2019'::date as event_date, 'C' as status, 1000 as credit_limit union all SELECT 1, '1/5/2019', 'F', 1000 union all SELECT 1, '3/10/2019','', 1000 union all SELECT 1, '3/10/2019','', 1000 union all SELECT 1, '8/27/2019', 'L', 1000 union all SELECT 2, '1/1/2019', 'L', 2000 union all SELECT 2, '1/5/2019', '', 2500 union all SELECT 2, '3/10/2019', '', 2500 union all SELECT 3, '1/1/2019', 'S', 5000 union all SELECT 3, '1/5/2019', '', 6000 union all SELECT 3, '3/10/2019', 'B', 5000 union all SELECT 4, '3/10/2019', 'B', 10000 ) ,tab2 as ( select customer_id ,last_day(event_date) as event_month ,status ,credit_limit ,dense_rank() over (partition by customer_id, event_month order by event_date desc ) as status_rank from temp_data group by customer_id,event_date,status,credit_limit ) ,cal as ( select cal_month_end_date from calendar_table where cal_year_id = 2019 group by 1 ) ,tab3 as ( select t.* ,event_month as start_month ,coalesce(lead(add_months(event_month,-1)) over(partition by customer_id order by event_month),year_end) as end_month from tab2 t ,(select max(cal_month_end_date) as year_end from cal) cal_end_month where status_rank = 1 ) select customer_id, cal_month_end_date, status, credit_limit from tab3, cal where cal_month_end_date between start_month and end_month group by 1,2,3,4 order by 1,2 Less
↳
Assumptions - The first record for a customer begins in the month it first had a status. Data is restricted to 2019 End month for all customers will be Dec 2019 All Values in Status column is a valid status including NULLs with temp_data as ( SELECT 1 as customer_id, '1/1/2019'::date as event_date, 'C' as status, 1000 as credit_limit union all SELECT 1, '1/5/2019', 'F', 1000 union all SELECT 1, '3/10/2019','', 1000 union all SELECT 1, '3/10/2019','', 1000 union all SELECT 1, '8/27/2019', 'L', 1000 union all SELECT 2, '1/1/2019', 'L', 2000 union all SELECT 2, '1/5/2019', '', 2500 union all SELECT 2, '3/10/2019', '', 2500 union all SELECT 3, '1/1/2019', 'S', 5000 union all SELECT 3, '1/5/2019', '', 6000 union all SELECT 3, '3/10/2019', 'B', 5000 union all SELECT 4, '3/10/2019', 'B', 10000 ) ,tab2 as ( select customer_id ,last_day(event_date) as event_month ,status ,credit_limit ,dense_rank() over (partition by customer_id, event_month order by event_date desc ) as status_rank from temp_data group by customer_id,event_date,status,credit_limit ) ,cal as ( select cal_month_end_date from calendar_table where cal_year_id = 2019 group by 1 ) ,tab3 as ( select t.* ,event_month as start_month ,coalesce(lead(add_months(event_month,-1)) over(partition by customer_id order by event_month),year_end) as end_month from tab2 t ,(select max(cal_month_end_date) as year_end from cal) cal_end_month where status_rank = 1 ) select customer_id, cal_month_end_date, status, credit_limit from tab3, cal where cal_month_end_date between start_month and end_month group by 1,2,3,4 order by 1,2 Less