# 4K

Business Intelligence Consultant interview questions shared by candidates

## Top Interview Questions

Sort: Relevance|Popular|Date
Business Intelligence Analyst was asked...21 November 2014

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 Show More Responses ### 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 not well 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 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 Show More Responses ### 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 8 Answers 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 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 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 Show More Responses ### 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. 6 Answers 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 Show More Responses ### 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. 6 Answers Read policies and procedures, get to know coworkers and look for projects to help on beyond my work concentration. Less I'd like to do both creative and hierarchical environment. Differences between me and others. I'm positive, team player, loyal and honest. Show More Responses ### 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 5 Answers 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 Show More Responses ### Question 5 : An ad campaign has a CPC =$0.5, a conversion rate = 3% and an average transaction value of $260.What is the Cost of Sales of the campaign (cost of the ad campaign divided by the revenues generated, in percentage)? Question 6 : With a margin on revenues of 13%, an average transaction value of$290 and a conversion rate = 0.7%, what is the maximum CPC an advertiser can afford without losing money (in dollar)? Question 7 : During his browsing, a user is randomly exposed to two ad banners A & B. Those two banners are equally likely to be shown. One and only one banner is shown per page. After two pages of browsing, what’s the probability that the user was shown only banners A (in percentage)? Question 8 : A/B Testing campaign: Measuring the impact of Criteo retargeting ads compared to a control group. Number of transactions on client site : • Group A exposed to Criteo banners 600,000 • Group B Control group not exposed 50,000a. b. What incremental revenues per user CompanyA has generated for the client advertiser (in dollar, rounded to the cent)? c. What total incremental revenues CompanyA has generated for the client advertiser? Total incremental revenue is simply the incremental revenue per user multiplied by the number of users exposed to Company A's retargeting. d. With $200.000 revenues following clicks on banners for group A (post click), what is the related post view (view through) effect in revenues generated by CompanyA campaign? View through effects on revenues are a bit tricky as they would require view through conversion tracking. A post impression visit that results in a transaction can be credited as a 'view through conversion'. If CompanyA is not tracking revenue on post-impression ('view through') visits, then you can estimate it by taking the average revenue per transaction - in this case$200,000 - and divide it by the number of post-click transactions in group A. This would give you the average revenue per transaction, often referred to as Average Order Value. You could then take the Average Order Value and multiply it by the number of view through conversions generated by Company A.

4 Answers

Answer 5 CPC $0.5 meaning each click cost$0.5 Conversion rate of 3% means that for 100 clicks, 3 sales are done 100 clicks generate 3 x $260 100 clicks =$780 Cost = 100 * 0,5 = \$50 50/780 = 6,4% Less

maxCPC: conversion rate x average transaction value x margin 0,007 x 290 x 0,13= 0,26 dollars Less

Answer 5: (CPC/CVR)/AOV=(0.5/0.03)/260=6.4%

Show More Responses
Viewing 1 - 10 of 4,390 Interview Questions