# 566

Business Intelligence Engineer interview questions shared by candidates

## Top Interview Questions

Sort: Relevance|Popular|Date

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

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

Hi can you please share the data tables for the problems?

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

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

(hint P(A or B) = P(A) + P(B) - P(A and B)

hint : P(A U B) = P(A) + P(B) - P(A and B)

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

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

1. Max units sold: SELECT order_id, MAX(qty) as MaximumBooks FROM orders GROUP BY order_id 2. Find avg quantity of books solder for every order_id every year SELECT order_id, year, AVG(qty) as AvgQuantity FROM orders GROUP BY order_id, year Less

1). Select year, sum(quantity)/count(distinct order_id) as avg_ quantity From Booksale group by 1 2)Straight forward one 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

declare v_date date; vcount number; cursor c1 is select distinct customer_id, '31jan2019' ref_date from amazon order by 1 asc; begin execute immediate'truncate table amazon_status'; for i in c1 loop v_date :=i.ref_date; while v_date &lt;= '31dec2019' loop select count(*) into vcount from (select CUSTOMER_ID,EVENT_DATE,STATUS,CREDIT_LIMIT, row_number() over(partition by CUSTOMER_ID,last_day(EVENT_DATE) order by EVENT_DATE desc) rnk from amazon a) where rnk = 1 and last_day(EVENT_DATE) = v_date and CUSTOMER_ID = i.CUSTOMER_ID; if vcount != 0 then insert into amazon_status select CUSTOMER_ID,last_day(EVENT_DATE) month_day,STATUS,CREDIT_LIMIT from (select CUSTOMER_ID,EVENT_DATE,STATUS,CREDIT_LIMIT, row_number() over(partition by CUSTOMER_ID,last_day(EVENT_DATE) order by EVENT_DATE desc) rnk from amazon a) where rnk = 1 and last_day(EVENT_DATE) = v_date and CUSTOMER_ID = i.CUSTOMER_ID; commit; elsif vcount =0 and v_date = '31jan2019'then insert into amazon_status values(i.customer_id,i.ref_date,'',''); commit; elsif vcount =0 and v_date != '31jan2019'then insert into amazon_status select customer_id, EVENT_DATE,STATUS,CREDIT_LIMIT from (select customer_id, EVENT_DATE,STATUS,CREDIT_LIMIT ,row_number() over(partition by customer_id order by EVENT_DATE desc ) rnk from amazon_status a) where rnk = 1 and customer_id = i.customer_id; commit; end if; v_date := last_day(v_date+1); end loop; end loop; end; -- --select * from amazon_status -- --truncate table amazon_status 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

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

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

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

### Monthly Revenue : company_name,month,revenue Write a query to pull the monthly revenue as columns instead of rows.

select date_part('year',invoice_date) yr, sum(case when date_part('Month',invoice_date)=1 then revenue else 0 end) as January, sum(case when date_part('Month',invoice_date)=2 then revenue else 0 end) February, sum(case when date_part('Month',invoice_date)=3 then revenue else 0 end) March, sum(case when date_part('Month',invoice_date)=4 then revenue else 0 end) April, sum(case when date_part('Month',invoice_date)=5 then revenue else 0 end) May, sum(case when date_part('Month',invoice_date)=6 then revenue else 0 end) June, sum(case when date_part('Month',invoice_date)=7 then revenue else 0 end) July, sum(case when date_part('Month',invoice_date)=8 then revenue else 0 end) August, sum(case when date_part('Month',invoice_date)=9 then revenue else 0 end) September, sum(case when date_part('Month',invoice_date)=10 then revenue else 0 end) October, sum(case when date_part('Month',invoice_date)=11 then revenue else 0 end) November, sum(case when date_part('Month',invoice_date)=12 then revenue else 0 end) December FROM revenue group by 1 order by 1; Less

select company_name, [January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December] from Monthly_Revenue pivot( sum(revenue) for month in ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December]) ) as PivotTable Less

Use PIVOT function

### Some sql questions. Tell me a time when you did something without getting your manager's approval. What will you do differently?

I solved the sql questions.

May I know the type of sql questions?

Hi! As an Amazon employee who interviewed and hired a lot of people here, I've created a guide that has all the questions and winning answers from an Amazonian recruiter perspective. Please check it out at interviewjoy.com/services/interview-process-details/amazon-senior-manager-interview-questions/ . Pls also check the positive feedback at the bottom of that page! Thanks. Less

I have work experience in spandana spoorthy finance company in role of backend operation Less

Hi, I have online assessment next week, Can you please let me know if it will be SQL or Python based? Less

Did you apply as a graduate or already have work experience as BI engineer?