# 5T

Business Intelligence Consultant interview questions shared by candidates

## Top Interview Questions

Sort: Relevance|Popular|Date

### 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 Show more responses ### 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)) &gt; 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) &gt;=2 ) a)b where b.rn&gt;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 &lt; destination Less Show more responses ### 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) Show more responses ### 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-&gt; this would just yield profit instead of Revenue (as done in some questions above) Less

### 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

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) &gt; 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

### SQL question - Table1 year| month| order_id| seller_id| book| quantity| prices 2008|June| 1|888|HP| 2| 2000 2008|June| 1|888|LoTR| 1| 1000 2009|July| 2|999|HP| 1| 1000 Q1. find avg quantity of books solder for every order_id every year? Q2. find max units of books sold for every order_id

select distinct year, order_id, avg(quantity) over(partition by year, order_id order by year) as avg_books from table a group by 1, 2 Less

If the question is correct, we can just use group by command, No partition needed. Select orderid, avg(qty) from table group by year, orderid Less

q1. select distinct year, order_id, avg(quantity) over(partition by year, order_id order by year) as avg_books from table a group by 1, 2 q2. USE MAX(QUANTITY) OVER(PARTITIOB BY YEAR, ORDER_ID ORDER BY YEAR) Less

### Schemas - Sales (sales_id, date , customer_id, Product_id, purchase_amount): Product (P_id, P_Name, Brand_id,B_name) Top 10 products in year XXXX Top 10 products in each year List of customers whose total purchase increased from XXXX-XXXX but decreased from XXXX-XXXX. List of customers who bought both brands "X" & "Y" and at-least 2 products in each brand.

elect t.customer_id "Customer ID" from ( select s.customer_id, count(distinct p.brand_id) over (partition by s.customer_id) brands_counter, count(distinct p.product_id) over (partition by s.customer_id, p.brand_id) products_counter from sales s inner join product p on p.product_id = s.product_id where p.brand_name in ('X', 'Y') ) t where t.brands_counter = 2 group by t.customer_id having min(t.products_counter) &gt;= 2 Less

WITH X_SAL AS ( SELECT customer_id, COUNT(DISTINCT Product_id) CNT FROM Sales Sal JOIN Product AS Prod ON Sal.Product_id = Prod.P_id WHERE B_name IN ('X') GROUP BY customer_id HAVING COUNT(DISTINCT Product_id) &gt;= 2 ) , Y_SAL AS ( SELECT customer_id, COUNT(DISTINCT Product_id) CNT FROM Sales Sal JOIN Product AS Prod ON Sal.Product_id = Prod.P_id WHERE B_name IN ('Y') GROUP BY customer_id HAVING COUNT(DISTINCT Product_id) &gt;= 2 ) SELECT customer_id FROM X_SAL JOIN Y_SAL ON Y_SAL.customer_id = X_SAL.customer_id ORDER BY customer_id Less

with t as ( select customer_id, Brand_id, COUNT(DISTINCT(Product_id)) AS C_PID from sales s join product p on s.product_id = p.p_id where Brand_id in ('X','Y') GROUP BY customer_id,BRAND_ID HAVING COUNT(DISTINCT(Product_id)) &gt;= 2 ) select customer_id group by customer_id having count(distinct BRAND_ID) = 2; Less

### 1. What would you do the first few weeks on the job? 2. Would you rather be in a creative or hierarchical, structured environment? 3. What makes you different than other candidates? 4. Strengths and Weaknesses 5. What type of people do you work best with? 6. Describe yourself in 6 words.

Read policies and procedures, get to know coworkers and look for projects to help on beyond my work concentration. Less

Differences between me and others. I'm positive, team player, loyal and honest.

q1.answr. I will work here. Will see there first. What is the work style and how is the work done here. If there is a new recipe. New is something new. He will fulfill his desire to learn and all who are our employees, treat them well and communicate and make good bond with them. Q2answr Yes, I in a creative and hierarchically protected environment. Q3answr Yes, I am different and better than other candidates. I believe it and I will show it to you. Q4answr Strength is knowledge and good courage and knowledge and negligence and fear are weakness. Q5answr I like working with them. People who are good, have good knowledge and have the courage to do something ahead and become new people, and always support the truth. Work with integrity. Q6 answer good thinking, good, knowledge, honest , aim Less

### Q1) Find the number of unique days each employee worked Emp Id Task Id Start date End date 1 1 Monday Wednesday 1 2 Monday Tuesday 1 3 Friday Friday 2 1 Monday Friday 2 1 Tuesday Wednesday Hint: Calendar day table or date dimension table Calendar_day Calendar_day_of_week Calendar_year Calendar_month 1900/01/01 Wednesday (3) 1990 1 Q2) How many customers placed orders every month? Table 1: Customer Date customer_id order_id units country 2019/07/01 A 112 5 US 2019/07/02 A 211 4 US 2019/08/02 B 511 4 EU 2019/09/01 C 322 1 JP 2019/09/01 C 322 2 JP 2019/08/05 A 378 6 US 2019/09/10 A 456 7 US

with t as ( SELECT Emp_Id, Task_Id, c.date FROM ACTIVITY A join calender c on c.date between a.start_date and a.end_date ) select emp_id, count(distinct(date)) as c1 from t group by emp_id; ---------------------------------- with t as ( select customer_id, extract(month from date) month, order_id from Customer ) select customer_id from t group by customer_id having (count(distinct month)) = 12; Less

create table unq_days( emp_id number, task_id number, start_dy varchar2(20), end_dy varchar2(20) ); insert into unq_days values(2,1,'Tuesday','Wednesday'); insert into unq_days values(2,1,'Monday','Friday'); insert into unq_days values(1,3,'Friday','Friday'); insert into unq_days values(1,2,'Monday','Tuesday'); insert into unq_days values(1,1,'Monday','Wednesday'); with range as ( select (to_date('4/5/2020','MM/DD/YYYY') + level -1) dt, trim(to_char(to_date('4/5/2020','MM/DD/YYYY')+level-1,'Day')) dy from dual connect by level &lt;= 7 ) select emp_id, count(distinct p.dt) unq_days from ( select u.emp_id, u.task_id, r.dt as start_dt, m.dt as end_dt from unq_days u left join range r on u.Start_dy = r.dy left join range m on u.end_dy = m.dy ) join range p on p.dt between start_dt and end_dt group by emp_id; EMP_ID UNQ_DAYS 1 4 2 5 Less

create table cus_ord ( ord_dt date, cus_id varchar2(1), order_id number, units number, country varchar2(2) ); insert into cus_ord values(to_date('2019/07/01','YYYY/MM/DD'), 'A',112,5,'US'); insert into cus_ord values(to_date('2019/07/02','YYYY/MM/DD'), 'A',211,4,'US'); insert into cus_ord values(to_date('2019/08/02','YYYY/MM/DD'), 'B',511,4,'EU'); insert into cus_ord values(to_date('2019/09/01','YYYY/MM/DD'), 'C',322,1,'JP'); insert into cus_ord values(to_date('2019/09/01','YYYY/MM/DD'), 'C',322,2,'JP'); insert into cus_ord values(to_date('2019/08/05','YYYY/MM/DD'), 'A',378,6,'US'); insert into cus_ord values(to_date('2019/09/10','YYYY/MM/DD'), 'A',456,7,'US'); select * from cus_ord; with dts as ( select count(distinct to_char(ord_dt,'YYYYMM')) cnt from cus_ord )select T.*, (case when dts.cnt = T.cnt then 'Y' ELSE 'N' END) ORDERED_ALL_MONTHS from dts join ( select cus_id, country, count(distinct to_char(ord_dt,'YYYYMM')) cnt from cus_ord group by cus_id, country)T on 1=1; Customer A ordered all 3 months. Less