Prepare for and practice interview questions from AECOM across topics like Analytics, SQL, Statistics and more.

AECOM Interview Questions

AECOM Interview Guides

Data Structures & Algorithms

Given two sorted lists, write a function to merge them into one sorted list.

Merge Sorted Lists

Find the missing integer from a array of consequtive integers

Find the Missing Number

Write a function to return the names and resume IDs for resumes whose IDs have not already been scraped

New Resumes

Given an integer N, write a function that returns all of the prime numbers up to N

Prime to N

Write a function move_zeros_back that moves all zeros in an array to the end

Move Zeros Back

Machine Learning

How would you encode a categorical variable with thousands of distinct values

Encoding Categorical Features

How do we deal with the missing square footage data to construct our model

Missing Housing Data

How would we build a model to detect fraud and text customers to approve or deny fraudulent transactions

Bank Fraud Model

In which case would you use a bagging algorithm versus a boosting algorithm

Bagging vs Boosting

How would you evaluate whether a decision tree is the right model and assess its performance before and after deployment

Decision Tree Evaluation

Probability

First to Six

What is the probability that each subsequent card is larger than the previous drawn card when picking three cards from a shuffled deck of 500 cards

500 Cards

What is the probability that it's actually raining in Seattle?

Raining in Seattle

What is the probability that item X would be found on Amazon's website?

Found Item

Given three uniform(0,4) random variables, what is the probability that the median of them is greater than 3?

Median Probability

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Write a query to get the total three-day rolling average for deposits by day

Rolling Bank Transactions

Paired Products

Get the top 3 highest employee salaries by department

Top Three Salaries

Manager Team Sizes

Behavioral

Describing a data project and its challenges

Hurdles In Data Projects

Strategically resolving misaligned expectations with stakeholders for a successful project outcome

Stakeholder Communication

How would you answer when an Interviewer asks why you applied to their company?

Why Do You Want to Work With Us

How would you negotiate and resolve disagreements when a client rejects your proposed solution?

Client Solution Pushback

Making data-driven insights actionable for those without technical expertise

Simple Explanations

Let’s say a lottery-winning donor wants to open a network of kindergartens in her rural Turkish hometown, aiming to make them affordable for local families and self-sustaining after her initial investment. She hired you as a Business Analyst consultant to lead the strategy for her mission.

How would you approach evaluating the feasibility of this plan, and what key factors or analyses would you consider to ensure both affordability and long-term sustainability?

How would you as a consultant develop a strategy for a client's mission of building an affordable, self-sustaining kindergartens in a rural Turkish town?

Kindergarten Feasibility

Estimation

Let’s say we want to launch a re-design of a landing page to improve the click-through rate. We can do this by implementing an AB test.
Given that we launch an AB test, how would you infer if the results of the click-through rate were statistically significant or not?

How can you accurately conclude if the results of an A/B test, conducted to evaluate the effectiveness of a landing page redesign, are statistically significant?

How can you accurately conclude if the results of an A/B test, conducted to evaluate the effectiveness of a landing page redesign, are statistically significant?

Statistically Significant Test

Precisely ascertain whether the outcomes of an A/B test, executed to assess the impact of a landing page redesign, exhibit statistical significance.

A/B Testing

Given the <code>employees</code> and <code>departments</code> table, write a query to get the top 3 highest employee salaries by department. If the department contains less that 3 employees, the top 2 or the top 1 highest salaries should be listed (assume that each department has at least 1 employee). 

Note: The output should include the full name of the employee in one column, the department name, and the salary. The output should be sorted by department name in ascending order and salary in descending order. 

Example:

Input:

<code>employees</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>first_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>last_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>salary</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>department_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<code>departments</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>employee_nam</code>e</td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>department_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>salary</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>

Given two sorted lists, write a function to merge them into one sorted list.

Bonus: What’s the time complexity?

Example:

Input:

<pre tabindex="0" class="chroma"><code>list1 = [1,2,5]
list2 = [2,4,6]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def merge_list(list1,list2) -&gt; [1,2,2,4,5,6]
</code></pre>

You have two sorted lists, one with m elements and one with n elements. What would be the time complexity of merging the two lists via merge sort?

You have two sorted lists, one with m elements and one with n elements. What would be the time complexity of merging the two lists via merge sort? 

Merging Lists Complexity

Write a SQL query to select the 2nd highest salary in the engineering department.

Note: If more than one person shares the highest salary, the query should select the next highest salary.

Example:

Input:

<code>employees</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>first_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>last_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>salary</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>department_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<code>departments</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>salary</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>

We’re given a table of bank transactions with three columns, <code>user_id</code>, a deposit or withdrawal value (determined if the value is positive or negative), and <code>created_at</code> time for each transaction.

Write a query to get the total three-day rolling average for deposits by day.

Note: Please use the format <code>&#39;%Y-%m-%d&#39;</code> for the date in the outout

Example:

Input:

<code>bank_transactions</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>transaction_value</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>dt</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>rolling_three_day</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>

Given an integer <code>N</code>, write a function that returns a list of all of the prime numbers up to <code>N</code>.

Note: Return an empty list there are no prime numbers less than or equal to <code>N</code>.

Example:

Input:

<pre tabindex="0" class="chroma"><code>N = 3
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def prime_numbers(N) -&gt; [2,3]
</code></pre>

Given a table called <code>employees</code>, get the largest salary of any employee by department

Example:

Input:

<code>employees</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>department</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>salary</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>department</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>largest_salary</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>

Write a query to get the largest salary of any employee by department

Largest Salary by Department

You are about to get on a plane to Seattle. You want to know if you should bring an umbrella. You call 3 random friends of yours who live there and ask each independently if it’s raining. Each of your friends has a 2⁄3 chance of telling you the truth and a 1⁄3 chance of messing with you by lying. All 3 friends tell you that “Yes” it is raining.

What is the probability that it’s actually raining in Seattle?

What is the main difference between the Frequentist and Bayesian methods of probabilistic analysis?

What is the **main** difference between the Frequentist and Bayesian methods of probabilistic analysis?

Frequentist Vs Bayesian Analysis

Amy and Brad take turns in rolling a fair six-sided die. Whoever rolls a “6” first wins the game. Amy starts by rolling first.

What’s the probability that Amy wins?

Amy and Brad take turns in rolling a fair six-sided die. Whoever rolls a “6” first wins the game. Amy starts by rolling first.

What’s the probability that Amy wins?

Let’s say you have a categorical variable with thousands of distinct values, how would you encode it?

Which method can be used to extract communities from large networks, that does not require a pre-determined number of clusters like K-means?

Which method can be used to extract communities from large networks, that does not require a pre-determined number of clusters like K-means?

Imagine a deck of 500 cards numbered from 1 to 500. If all the cards are shuffled randomly and you are asked to pick three cards, one at a time, what’s the probability of each subsequent card being larger than the previous drawn card?

Imagine a deck of 2000 cards numbered from 1 to 2000. If all the cards are shuffled randomly and you are asked to pick three cards, one at a time, what’s the probability of each subsequent card being larger than the previously drawn card?

Imagine a deck of 2000 cards numbered from 1 to 2000. If all the cards are shuffled randomly and you are asked to pick three cards, one at a time, what's the probability of each subsequent card being larger than the previously drawn card?

2000 cards

Let’s say we’re comparing two machine learning algorithms. In which case would you use a bagging algorithm versus a boosting algorithm? 

Give an example of the tradeoffs between the two.

In machine learning, when would you use a bagging algorithm over a boosting algorithm?

In machine learning, when would you use a bagging algorithm over a boosting algorithm?

Bagging vs. Boosting

Let’s say you’re working in HR at a major tech company, and they ask you to find employees with a high probability of leaving the company. They tell you these employees perform well but have the lowest pay.

Given two tables, <code>employees</code> and <code>projects</code>, find the five lowest-paid employees who have completed at least three projects.

Note: We consider projects to be completed when they have an end date, which is the same as saying their <code>End_dt</code> is not <code>NULL</code>.

Example:

Input:

<code>employees</code> table

<table>
<thead>
<tr>
<th>id</th>
<th>salary</th>
</tr>
</thead>

<tbody>
<tr>
<td>INTEGER</td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<code>projects</code> table

<table>
<thead>
<tr>
<th>employee_id</th>
<th>project_id</th>
<th>Start_dt</th>
<th>End_dt</th>
</tr>
</thead>

<tbody>
<tr>
<td>INTEGER</td>
<td>INTEGER</td>
<td>DATETIME</td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>employee_id</th>
</tr>
</thead>

<tbody>
<tr>
<td>INTEGER</td>
</tr>
</tbody>
</table>

Find the five employees with the hightest probability of leaving the company

Top 5 Turnover Risk

Write a query to identify the manager with the biggest team size.

You may assume there is only one manager with the largest team size.

Example:

Input:

<code>employees</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>manager_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<code>managers</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>team</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>manager</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>team_size</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>

Let’s say we have two tables, <code>transactions</code> and <code>products</code>. Hypothetically the <code>transactions</code> table consists of over a billion rows of purchases bought by users.

We are trying to find paired products that are often purchased together by the same user, such as wine and bottle openers, chips and beer, etc..

Write a query to find the top five paired products and their names.

Notes:
 For the purposes of satisfying the test case, p2 should be the item that comes first in the alphabet.
 The <code>qty</code> column represents paired products count

Example:

Input:

<code>transactions</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>product_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>quantity</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<code>products</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>price</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>p1</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>p2</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>qty</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>

Tell me about a data project that didn’t go the way you expected. What did you set out to do, what surprised you, and how did you handle it?

We want to build a model to predict housing prices in the city of Seattle. We’ve scraped 100K sold listings over the past three years but found that around 20% of the listings are missing square footage data.

How do we deal with the missing data to construct our model?

We want to build a model to predict housing prices in the city of Seattle. We’ve scraped 100K sold listings over the past three years, but have discovered that around 20% of the listings are missing square footage data.

How would you approach dealing with this missing data, in order to construct the most useful predictive model possible?

We want to build a model to predict housing prices in the city of Seattle. We’ve scraped 100K sold listings over the past three years, but have discovered that around 20% of the listings are missing square footage data.

How would you approach dealing with this missing data, in order to construct the most useful predictive model possible?



Let’s say you work at a file-hosting website. You have information on user’s daily downloads in the <code>download_facts</code> table

Use the window function <code>RANK</code> to display the top three users by downloads each day. Order your data by <code>date</code>, and then by <code>daily_rank</code>

Example:

Input:

<code>download_facts</code> table

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>date</code></td>
<td>DATE</td>
</tr>

<tr>
<td><code>downloads</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>daily_rank</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>date</code></td>
<td>DATE</td>
</tr>

<tr>
<td><code>downloads</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>

Write a query to display the top three users by downloads each day

Top 3 Users

You have an array of integers, <code>nums</code> of length <code>n</code> spanning <code>0</code> to <code>n</code> with one missing. Write a function <code>missing_number</code> that returns the missing number in the array.

Note: Complexity of \(O(n)\) required.

Example:

Input:

<pre tabindex="0" class="chroma"><code>nums = [0,1,2,4,5] 
missing_number(nums) -&gt; 3
</code></pre>

Let’s say we have a very naive advertising platform. There is an audience of size A and a limited amount of impressions B.

Each of the impressions goes to just one user, defined at random. Every user in the audience has the same random chance of receiving each impression. 

<ol>
<li>Compute the probability that a user has exactly 0 impressions. </li>

<li>What’s the probability that every person has at least 1 impression?</li>
</ol>

Compute the probability a user gets exactly 0 impressions, and the probability everyone gets at least 1 impression

Impression Reach

Let’s say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service in addition that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

How would we build this model?

Let’s say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

Which statement is true?

Let's say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

Which statement is true?

Netflix has hired people to rate movies.

Out of all of the raters, 80% of the raters carefully rate movies and rate 60% of the movies as good and 40% as bad. The other 20% are lazy raters and rate 100% of the movies as good.

Assuming all raters rate the same amount of movies, what is the probability that a movie is rated good?

There are two categories of movie raters: careful and lazy. Careful raters make up 80% of the total. They rate 60% of the movies as good and 40% as bad. Lazy raters, constitute 20% of the raters and 100% of the movies as good. Given these parameters, calculate the overall probability of a movie being rated good.

There are two categories of movie raters: careful and lazy. Careful raters make up 80% of the total. They rate 60% of the movies as good and 40% as bad. Lazy raters, constitute 20% of the raters and 100% of the movies as good. Given these parameters, calculate the overall probability of a movie being rated good.

Lazy Raters

What is the probability that a movie is rated good

Say you are tasked with designing a data mart or data warehouse for a new online retailer.

How would you design the system?

Note: Sketch a star schema to explain your design.

Design a data warehouse for a new online retailer

Retailer Data Warehouse

Data Modeling

Given a string, find the first non-repeating character in it and return its index. If it doesn’t exist, return -1.

Example questions:

Consider a string where all characters are lowercase alphabets.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>s = &#39;helloworld&#39;
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>first_uniq_char(s) -&gt; 0
</code></pre>

In the string ‘helloworld’, the first non-repeating character is ‘h’, which is at index 0.

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>s = &#39;concussion&#39;
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>first_uniq_char(s) -&gt; 4
</code></pre>

In the string ‘concussion’, the first non-repeating character is ‘u’, which is at index 4.

This problem involves finding the first non-repeating character in a given string. The solution involves iterating over the string and keeping track of the frequency of each character. The first character that has a frequency of 1 is the first non-repeating character.

Find the First Non-Repeating Character in a String

Given a list of integers, identify all the duplicate values in the list. Assume that the list can contain both positive and negative numbers, and the order of the list does not matter. A number is considered a duplicate if it appears more than once in the list. Return a list of the duplicate numbers.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>nums = [1, 2, 3, 1, 2, 3]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>find_duplicates(nums) -&gt; [1, 2, 3]
</code></pre>

The numbers 1, 2, and 3 all appear more than once in the list, so they are considered duplicates.

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>nums = [1, -1, 2, 3, 3, -1]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>find_duplicates(nums) -&gt; [-1, 3]
</code></pre>

The numbers -1 and 3 both appear more than once in the list, so they are considered duplicates. Note that the order of the output does not matter.

Example 3:

Input:

<pre tabindex="0" class="chroma"><code>nums = [1, 2, 3, 4, 5]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>find_duplicates(nums) -&gt; []
</code></pre>

None of the numbers in the list appear more than once, so there are no duplicates.

This problem involves identifying duplicate numbers in a list of integers. The function should return a list of the duplicate numbers.

Find Duplicate Numbers in a List

You’re given a string that may contain the characters <code>{</code>, <code>}</code>, <code>[</code>, <code>]</code>, <code>(</code>, and <code>)</code>.

Task: Verify that the string is balanced. A balanced string is one where every opening character, <code>{</code>, <code>[</code>, or <code>(</code>, has a corresponding closing character, <code>}</code>, <code>]</code>, or <code>)</code>.

Write a function called <code>is_balanced(string: str) -&gt; bool</code> which verifies the balance of a string.

Example:

<pre tabindex="0" class="chroma"><code>is_balanced(&#39;(())[]{}&#39;) -&gt; True
</code></pre>

<pre tabindex="0" class="chroma"><code>is_balanced(&#39;{([(){}])()}&#39;) -&gt; True
</code></pre>

<pre tabindex="0" class="chroma"><code>is_balanced(&#39;{}[]())&#39;) -&gt; False
</code></pre>

<hr/>

Write a function that tests whether a string of brackets is balanced.

The Brackets Problem

Given an array and a target integer, write a function <code>sum_pair_indices</code> that returns the indices of two integers in the array that add up to the target integer. If not found, just return an empty list.

Note: Can you do it on \(O(n)\) time?

Note: Even though there could be many solutions, only one needs to be returned.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>array = [1 2 3 4] 
target = 5 
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def sum_pair_indices(array, target) -&gt; [0 3] or [1 2]
</code></pre>

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>array = [3]
target = 6 
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>Do NOT return [0 0] as you can&#39;t use an index twice.
</code></pre>

Given an array and a target integer, write a function that returns the indices of two integers in the array that add up to the target integer.

Target Indices

Let’s say you work at Allstate. Allstate is running <code>N</code> online ads right now. The table <code>ads</code> contains all those ads, ranked by popularity via the <code>id</code> column (e.g., the entry with <code>id = 1</code> is the most popular, etc.).

Create a subquery or common table expression named <code>top_ads</code> containing the top 3 ads (by popularity) and return the number of rows that would result from the following operations

<ol>
<li><code>ads INNER JOIN top_ads</code></li>
<li><code>ads LEFT JOIN top_ads</code></li>
<li><code>ads RIGHT JOIN top_ads</code></li>
<li><code>ads CROSS JOIN top_ads</code></li>
</ol>

Note: Please make the <code>join_type</code> column in your output have the values <code>inner_join</code>, <code>left_join</code>, etc. for each of their respective join types

Note: Please return only one query with each number in a different row

Example:

Input:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>join_type</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>number_of_rows</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>

Create top_ads with the top 3 ads and return the row counts for inner, left, right, and cross joins with ads

Size of Joins

How would you explain what a p-value is to someone who is not technical?

What does a p-value in a statistical test represent?

What does a p-value in a statistical test represent?

P-value to a Layman I

In a statistical test, how does a low p-value (less than 0.05) influence our decision about the null hypothesis?

In a statistical test, how does a low p-value (less than 0.05) influence our decision about the null hypothesis?

Calculate Moving Average	SQL	Easy
Predict Customer Churn	Machine Learning	Medium
A/B Test Significance	Statistics	Medium
Optimize Query Performance	SQL	Hard
Feature Importance Analysis	Machine Learning	Medium
Clean Missing Data	Python	Easy
Neural Network Architecture	Deep Learning	Hard
Calculate Cohort Retention	SQL	Medium
Bayesian Probability	Statistics	Easy
Recommend Similar Products	Machine Learning	Hard

AECOM Interview Questions

AECOM Interview Guides

AECOM Interview Questions

Challenge

Discussion & Interview Experiences

Discussion & Interview Experiences