 |
| data analyst interview questions from Agoda |
Instructions
This assessment consists of 2 tests. You must complete both tests within the assessment.
- The first part of the assessment covers subjects such as Statistics, Data Literacy. Reports & Visualisations. Microsoft Excel and Chart Interpretation.
- You will have 25 minutes to complete the 16 questions within the test.
- All the questions are multiple-choice There are 5 answer options with one answer which is most correct. Some other answer options may be partially correct ensure you choose the MOST correct answer option.
- You will be awarded full marks for each correct answer and 0 marks for each incorrect answer You should therefore attempt to answer every question.
- For the SQL test, you will need to write SQL queries which will be validated against a database.
- You will have 15 minutes to complete the 2 questions within the test.
- You will need to write SQL queries using basic (ANSI) SQL statements. You will be able to execute your queries and view the output.
- Your query output will be compared with the expected output for the question. You will be awarded full marks if the output matches the expected output and 0 marks if it doesn't match The outputted column names are not considered only the order of the outputted columns Ensure you read the question thoroughly and provide the output expected.
- Once each test has started, as soon as the time has finished your responses will be automatically submitted.
- While in the test do not leave the test page. Your actions will be analyzed.
- You may reuse the original link to resume the assessment until 17 Apr 2023 at 10:02 am (GMT+07:00).
- You will be able to see your results as soon as you complete the assessment.
- Agoda will have access to your results and will contact you directly if you progress to the next stage
You will now complete a short pre-assessment survey
Best of luck!
MINIMUM TECHNICAL REQUIREMENTS
1. I have checked that have a stable internet connection, e.g. using Google Internet Speed Test
2. I am using an up-to-date version of Safari, Firefox, Chrome, or Edge.
3. I am using a desktop computer or laptop computer. am not attempting the assessment on a mobile or tablet device because the small screen size will prevent me from seeing some details properly.
4. I have a pen paper and calculator available to make notes and do some simple calculations.
I have ensured that my setup meets all the minimum technical requirements.
If you encounter any technical difficulties, please email us here if you need special dispensation for this assessment, please contact Agoda.
PRIVACY
I have read and accept Alooba's Privacy Policy.
CODE OF HONOUR
At Alooba, we are committed to providing a fair experience for everyone. Please read and agree to our Code of Honour
1. I agree that will complete this assessment on my own, without the help of anybody else.
2. I agree not to collude with anyone on this assessment.
3. I agree that will complete this assessment only once.
4. I agree that will not plagiarise anyone's answers.
5. I agree that I will not try to gain any unfair advantages.
6. I agree not to disclose to anyone or make public any of the questions or answers in the assessment.
7. I understand that Alooba monitors all assessments and will flag any suspicious activity with Agoda.
I agree with the code of honor.
1. The following visual, produced by ourworldindata.org, plots the world population along with the annual growth rate of the world population against time, using the latest data from the United Nations Population Division. The visual also includes the UN's latest forecast for population growth, compiling trends seen in subsets of the population that have already experienced rapid growth. Typically, this period of rapid growth is short-lived.
What was the Compound Annual Growth Rate (CAGR) between 1700 and 2019?
1. 0.5%
2. 1.4%
3. 0.8%
4. 1.1% -> Answer
5. 0.2%
2. In the following chart, you can see the evolution of imports and exports of cars of an Asian country.
Which of the following statements is not correct?
1. In 2010 the exports matched the imports.
2. The maximum number of imports has been slightly above 1.5 million during the period.
3. The gap between imports and exports is being reduced. -> Answer
4. In 2015 the difference between imports and exports was close to 250000 units.
5. On average, the country is a net importer; imports are higher than exports.
3. ABC News analyzed some Google Community Mobility Report data during the start of the first Covid-related lockdown in April 2020.
Which types of seasonality seem to be present in the chart?
1. Monthly seasonality
2. None of these
3. Weekly seasonality
4. Daily seasonality
5. All of these -> Answer
4. Suppose you wanted to measure the relationship between someone's height and their weight, as well as between their income and education level.
Which of these is correct?
1. The correlation coefficient between income and education level is normally -1
2. The correlation between someone's height and weight is normally negative
3. If you regressed height on weight, you'd expect the weight coefficient to be positive -> Answer
4. The covariance between someone's height and weight is normally negative
5. The correlation coefficient between income and education level is normally 0
5. you're provided with the report shown.
What is the following?
1. The conversion rate for the whole week
2. The total revenue in Euros assuming a rate of 1USD = 0.95EUR
1. 1: Not possible to calculate with the given data
2: 50M
2. 1: 4.5%
2: 47.5M
3. 1: Not possible to calculate with the given data
2: 47.5M
4. 1: 4.64% -> Answer
2: 50
5. 1: Not possible to calculate with the given data
2: 50
6. Alooba has decided to diversify its range of services and products and launch a premium ice cream brand - Alooba Luxury Ice Creams. The pricing structure for its 3 key flavors (Chocolate, Vanilla, and Strawberry), has been presented in the following visual.
A new client has been identified, however, they desire larger quantities of ice cream than shown in your pricing model. Your manager has told you that you can use linear extrapolation to provide a quote.
What is the correct quote for 200 liters of chocolate and 200 liters of strawberry ice cream, based on this modeling assumption?
1. $1,766.67
2. $1,360.00
3. Insufficient information to perform linear extrapolation -> Answer
4. $1,580.00
5. $1,840.00
7. 20,500 leads were generated in the previous month by a lead-generating platform (X) through multiple sources, out of which 30% of the leads were generated through surveys. The average revenue per lead for X was 245 AUD and the average cost per lead for X was 190 AUD. For leads generated specifically through surveys, both revenue per lead and cost per lead were 12% higher than average values for X.
Calculate the return on investment (ROI) for leads generated through surveys.
ROI is defined as (Revenue from Investment-Cost of Investment)/Cost of Investment
1. 23.2%
2. 17.8% -> Answer
3. 19.0%
4. 24.0%
5. 28.9%
8. A social media website X tracks the active customers on a daily basis.
For all the customers who were active on the website at least once a month, they also calculated the % of customers who were inactive for 2 weeks, and the % of customers who were inactive for 3 weeks. The average of both is considered as the net inactive %.
The table shows the number of customers active on the website for the top 5 countries.
As a marketing analyst for X, identify the country with the highest net inactive %.
1. India
2. Australia
3. USA -> Answer
4. United Kingdom
5. Canada
9. A study on COVID situations in the United States was performed. The author wants to present the death rate per 1000 by state. Hence, the death density was calculated by dividing the total fatalities related to COVID over the population and multiplied by 1000. A map was created as below.
An editor says the map is misleading. Which part of the map is misleading?
1. The font is too thin
2. Missing legend
3. Calculation of the death density
4. Too many decimals
5. Color -> Answer
10. Please see the plot to answer the question.
Which of the following plots is displayed in the figure in the attachment?
1. Linear trend -> Answer
2. A timer series plot
3. A boxplot
4. A histogram
5. A scatterplot
11. Your manager asks you to investigate whether the customers' age influences the cost of the sports shoes they buy. Which of the following can be used to investigate the effect of the customers' age on the cost of shoes purchased?
1. Variance
2. Correlation
3. Regression
4. Covariation -> Answer
5. Autocorrelation
12. A retail company decided to increase promotional signage in one of its stores, Store A, in the month of April. In the remaining stores, the signage remained the same. The average customer basket value (sales per transaction) was tracked in all stores for the year. The following table gives the average customer basket value in January (prior to the change in Store A) and in September (after the change in Store A).
Using the method of difference in differences, what is the estimated signage impact on the average customer basket?
1. $6.35
2. $4.24
3. $14.83
4. -$10.59
5. -$4.24 -> Answer
13. Suppose you measure the height and weight of 25000 university students. You record each student's height in both centimeters and nanometres, and you record their weight in kilograms as well as grams.
Suppose the variance of weight (kg) is 30 and the variance of the height (cm) is 80. What can we say about the standard deviations of those metrics?
1. The standard deviation of weight is 8.9 kg and height is 5.5 cm.
2. The standard deviation of weight is 5.5 kg and of height is 8.9 cm. -> Answer
3. The standard deviation of weight is 900 kg and height is 6400 cm.
4. The standard deviation of weight is 30 kg and the standard deviation of height is 80 cm.
5. We need more information to calculate the standard deviations.
14. You are working in a pet store. The store keeps all the pet's information in one Excel sheet.
Which is the proper function in Excel to find out which ID the pet "Cooper" belongs to?
1. =INDEX(A2:A6,MATCH("Cooper",C2:C6,0)) -> Answer
2. =MATCH(A2:A6,INDEX("Cooper",C2:C6,0))
3. =LOOKUP("Cooper",C:A)
4. =VLOOKUP("Cooper",C:A,1,0)
5. =VLOOKUP("Cooper",A:A,1,0)
15. In this Excel document you have the following formula in the cell G2:
=sumifs (B2:B5, B2: B5, ">20000", C2: C5, ">15%")
What is the result of the formula?
1. 10%
2. 25000
3. 0
4. 30000
5. 15% -> Answer
16. You are working on an Excel worksheet. In the right table, you want to find the corresponding Pet Name to the Pet ID. Such a relationship can be found in the left table. For those Pet IDs that do not exist in the left table, you want to leave the cell blank.
What is the proper function to use in cell F2?
1. =VLOOKUP(E2,A:C,3,0)
2. =ISTEXT(VLOOKUP(E4,A:C,3,0))
3. =IF(ISNA(VLOOKUP(E3,A:C,3,0)),VLOOKUP(E3,A:C,3,0),"")
4. =ISNA(VLOOKUP(E3,A:C,3,0))
5. =IFERROR(VLOOKUP(E2,A:C,3,0),"") -> Answer
SQL
For the SQL test, you will need to write SQL queries which will be validated against a database.
You will have 15 minutes to complete the 2 questions within the test.
Your query output will be compared with the expected output for the question. You will be awarded full marks if the output matches the expected output and 0 marks if it doesn't match The outputted column names are not considered, only the order of the outputted columns Ensure you read the question thoroughly and provide the output expected.
Ensure you execute your query and check the output before submitting. You may execute your queries as many times as needed. The database will be reset to its initial state before each execution.
Your queries will be executed using a database engine that is compliant with the majority of basic SQL syntax. Ensure your queries use ANSI SQL. You cannot use any database-specific functions or syntax.
A few points when writing your queries:
- Each question must be answered with a single SQL query
- RIGHT JOIN is not supported. Use LEFT JOIN instead.
- FULL OUTER JOIN is not supported
- TOP is not supported Use LIMIT at the end of the query instead.
- The CONCAT function is not supported Use || instead.
- Most date functions such as NOW and DATE_FORMAT are not supported Treat dates as text instead.
- ILIKE is not supported LIKE is supported, but note that it is case insensitive.
- EXTRACT is not supported.
- LEFT and SUBSTRING are not supported. Try SUBSTR instead.
Your queries will be automatically saved and will be recorded upon moving to the next question or when the timer runs out.
1. You work as a data analyst for a large supermarket chain called Comprare. You've been presented with 4 important tables from their data warehouse.
- The item table, where each row is an item available for purchase from Comprare.
- The customer table, where each row is a registered customer. Not all customers are registered; many shop anonymously.
- The purchase table, where each row is a purchase (transaction). A purchase will typically involve buying multiple items.
- The purchase_item table, where each row is an item bought as part of a purchase.
write an SQL query which returns a single column containing a single value of the store_id.
a) SELECT store_id
FROM purchase_item
INNER JOIN purchase ON purchase_item.purchase_id = purchase.purchase_id
WHERE DATE_TRUNC('month', purchase.purchase_date) = '2021-04-01'
GROUP BY store_id
ORDER BY COUNT(DISTINCT purchase_item.purchase_id) DESC
LIMIT 1;
2. Which customer has bought the most number of unique items?
Write an SQL query which returns the correct answer with three columns in this order. first_name, last_name, item_count, where item_count is the number of unique items that customer has bought.
a) SELECT c.first_name, c.last_name, COUNT(DISTINCT pi.item_id) as item_count
FROM customer c
JOIN purchase p ON c.customer_id = p.customer_id
JOIN purchase_item pi ON p.purchase_id = pi.purchase_id
GROUP BY c.customer_id
ORDER BY item_count DESC
LIMIT 1;
0 Comments