Intelligence Analyst Interview Questions
An intelligence analyst evaluates data and information to identify security risks and mitigate them for various organisations. Some intelligence analysts work for government agencies, but the field is broad and spans across various industries. When interviewing for a position as an intelligence analyst, you may face questions about the tools you use to identify risks.
Top Intelligence Analyst Interview Questions & How to Answer
Here are three top intelligence analyst interview questions and tips on how to answer them:
Question No. 1: Describe your method for identifying and mitigating security risks.
How to answer: An interviewer uses this question to understand your methods and processes for managing important tasks. Use the STAR method to describe a specific situation that demonstrates how you analyse data to identify potential security risks for an organisation and take action.
Question No. 2: How do you track the data you use to make decisions and share information with others on the team?
How to answer: Data analysis is part of the role of an intelligence analyst, but this individual also needs to be able to track the data they use and disseminate it across the intelligence team. When answering this question, talk about the software or tools you have experience with that allow(s) for accurate data analysis and management.
Question No. 3: What do you think is the greatest responsibility of an intelligence analyst?
How to answer: This question allows you to share your perspective on the role of an intelligence analyst and their responsibilities to an organisation. Your perspective might outline the importance of mitigating security risks or describe the responsibility of protecting a group of people.
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
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
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-> this would just yield profit instead of Revenue (as done in some questions above) 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
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
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) >= 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) >= 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) >= 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)) >= 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.
6 Answers↳
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
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 <= 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
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%