Intelligence Officer Interview Questions
1K
Intelligence Officer interview questions shared by candidates
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
↳
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
You have been asked to send an e-mail campaign to customers that have made a purchase on Amazon.com in the past but not recently. Tell me how you would go about the process. Now tell me the structure of the query you would use.
4 Answers↳
The question is pretty straightforward that we have to exclude the customers that have done the purchase in the past 6 months. Select * from orders where orders.customer_id NOT IN (Select customer_id FROM orders Where date between date_sub(curdate(), Interval 6 months) AND curdate()) AS B; Less
↳
select cust_id ,count(purchase) from order ord (nolock) group by cust_id having count(putchase)>1 where purchase_date not between dateadd(month,-6,getdate()) and getdate() Less
↳
with cust_purchased_recently as ( select cust_id from orders where order_date>= dateadd(month,datediff(month,0,getdate())-6,0) ) Select * from orders as o left join cust_purchased_recently as c on o.Cust_ID = c.Cust_ID where c.Cust_ID is null Less
salary(department_id,employee_id,salary) List all employees with salary greater than the everage department salary and also greather than $50K
4 Answers↳
select employee_id from (select employee_id, salary, avg(salary) over (partition by department_id) as avg_dept from salary) as SourceTable where salary > avg_dept and salary > 50000 Less
↳
Select id,first_name,last_name from (Select * from employees A join (SELECT department_id, avg(salary) as avgsal from employees group by department_id) B on A.department_id=B.department_id) X where salary>avgsal and salary>50000 Less
↳
WITH temp_dept as( SELECT dept_id,avg(salary) as average_department_salary FROM salary GROUP BY dept_id HAVING avg(salary)>50000 ) SELECT emp_id FROM salary s join temp_dept td on s.dept_id=e.dept_id and e.salary>td.average_department_salary Less
From SQL: 1. If there are 2 tables with m and n rows respectively, then what will be the minimum and maximum number of rows in all the joins. 2. Get the 3rd highest employee salary from employee salary table. 3. Given a product table, the new record gets appended in table with the new timestamp for the product ID. Find out the latest row for that particular product with or without analytical functions. 4. Given there are 2 tables of employee details and employee project details, which all employee are not yet assigned a project. For python: 1. Given a product data frame, the new record gets appended in table with the new timestamp for the product ID. Find out the latest data frame for that particular product with pandas.
4 Answers↳
SELECT productID,timestamp,productField1,productField2 FROM productTable GROUP BY productID ORDER BY max(timestamp); Less
↳
This sql isn’t even remotely right lol
↳
SQL: 1. Minimum = 0 and Maximum = m * n. 2. Select salary from Emp_salary order by salary desc limit 3,1; 3. Select * from Products where timestamp = (Select MAX(timestamp) from Products) ; 4. Select * from Employees where employee_id NOT IN (select employee.id from Projects) Less
Was asked to write queries for two problems - Q. Table: Customer_orders customer_id order_id order_day 123 27424624 25Dec2011 123 89690900 25Dec2010 797 12131323 25Dec2010 876 67145419 15Dec2011 Write SQL for customers who placed orders on both the days, 25th Dec 2010 and 25th Dec 2011? Customer_id order_id order_datetime 1234 4141-4814 25/12/2010:06:15:00 1234 4141-4815 25/12/2010:06:20:00 1234 4141-4816 25/12/2010:06:41:00 1234 4141-4817 25/12/2010:06:50:00 8153 2525-1414 26/12/2010:07:13:00 8153 2525-1415 26/12/2010:13:10:10 Let's say I can combine two orders that are placed by the same customer within 20 mins of each other, what % of orders can be combined.
3 Answers↳
SELECT COUNT(order_id_20pct)/count(order_id) FROM ( SELECT CASE WHEN DATEDIFF('minute',lag(order_datetime) over (partition BY customer_id ORDER BY order_DATETIME),order_datetime) <22 THEN order_id END order_id_20pct, order_id FROM bi_adhoc.AMZTIMES ) Less
↳
SELECT COUNT(order_id_20pct)/count(order_id) FROM ( SELECT CASE WHEN DATEDIFF('minute',lag(order_datetime) over (partition BY customer_id ORDER BY order_DATETIME),order_datetime) <22 THEN order_id END order_id_20pct, order_id FROM bi_adhoc.AMZTIMES ) Less
↳
SELECT ( SELECT COUNT (order_id) FROM ( SELECT customer_id, order_id, order_datetime, DATEDIFF('minute',lag (order_datetime) over (partition BY customer_id ORDER BY order_datetime),order_datetime) order_lag FROM bi_adhoc.amztimes ) WHERE order_lag < 22) / (SELECT COUNT(order_id) FROM bi_adhoc.amztimes) as pct_combined Less
No questions at all were asked about business intelligence analyst or data analytics The only question asked was -How much do i rate myself on SQL on the scale of 1 to 10
3 Answers↳
I rate myself 9/10 on a scale of 1 to 10
↳
Thank you! Will check this out
↳
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
Question 1 : An ad campaign has generated 1 million ad impressions and 6000 clicks.What is the CTR (ClickThrough Rate)? (in percentage) Question 2 : An ad campaign has generated 5000 clicks and 100 transactions. What is the conversion rate (in percentage)? Question 3 : With a budget of $5000 and a CPC (Cost per click) = $0.4, how many clicks an advertiser can buy (integer without unit)? Question 4 : An ad campaign has a CTR = 0.6% and a CPC = $0.4.What is the equivalent CPM (in dollar)? 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. a. Is it a problem to have a control group smaller than the CompanyA group? No - what you want to know is if the transaction rate for the experiment group is significantly greater than that of the control group. As long as the results are statistically significant, the uneven sample size between groups should not be a problem. b. What incremental revenues per user CompanyA has generated for the client advertiser (in dollar, rounded to the cent)? For this question you need to first calculate the revenue per user for both groups and then take the difference between them to arrive at the incremental revenue per user. 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?
3 Answers↳
Hi can you please post the answers and the process from questions 5 and on? Having no idea how to solve it.. Less
↳
In the last question, there is a table with figures and measures that you can use to compute the answers Less
↳
Can you explain the eigth question in more detail? Is there a missing part before the sub questions a. b. c. d.? The only data is the number of users : "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,000" how can you calculate revenue? or something else? Less
Provide suggestions of where marketing spend can be cut
2 Answers↳
The data set they provide has the answer in it as long as you can find it
↳
sounds like this drug-dealing company just wanted you to do some consulting work for free. Typical rip-off company of the modern age, filled with obnoxious young sociopaths Less
What would you do if one of our client receives a death threat?
2 Answers↳
Call the police.
↳
Interesting. Almost impossible to imagine they only asked you that one, oddly short and seemingly context stricken question. I'd be interested to know what the other interview questions genuinely were, and what your actual responses were to those ones. That would actually provide some better insight as to what the actual interview process looks like, and if it's a good way to screen competent analysts or security practitioners. Less
Signed Non Disclosure
2 Answers↳
Hi, Thanks for this review. There is no real information anywhere about a BI Analyst interview at Google. I have my interview in a few days. Could you please provide more details on the type of technical questions that were asked? Thanks a lot! Less
↳
Hi, Hope you are doing good. I too have interview in few days, can you please provide some details like how to prepare and what to expect. Less