Prepare for and practice interview questions from ConsultAdd across topics like Product Metrics, SQL, A/B Testing and more.

ConsultAdd Interview Questions

ConsultAdd Interview Guides

Machine Learning

Encoding Categorical Features

Missing Housing Data

Bank Fraud Model

One Million Rides

Why would one algorithm generate different success rates with the same dataset?

Same Algorithm Different Success

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

Get Top N Frequent Words

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

Prime to N

Move Zeros Back

Write a query to select the top 3 departments with at least ten employees and rank them according to the percentage of their employees making over 100K in salary.

Employee Salaries

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Calculate the first touch attribution for each `user_id` that converted.

First Touch Attribution

Closest SAT Scores

Get the top 3 highest employee salaries by department

Top Three Salaries

Probability

First to Six

500 Cards

Raining in Seattle

Found Item

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

Median Probability

Statistics

P-value to a Layman

Say you are given a dataset of perfectly linearly separable data. What would happen when you run logistic regression?

Perfectly Separable

What are the assumptions of linear regression?

Assumptions of Linear Regression

Using R Squared

What statistical test could you use to determine which of two parcel types is better to use, given how often they are damaged?

Different Parcel Effectiveness

<ol>
<li><p>Design a database for a stand-alone fast food restaurant. </p></li>

<li><p>Based on the above database schema, write a SQL query to find the top three highest revenue items sold yesterday. </p></li>

<li><p>Write a SQL query using the database schema to find the percentage of customers that order drinks with their meal.</p></li>
</ol>


Fast Food Database

Data Modeling

<p>Write a SQL query to select the 2nd highest salary in the engineering department.</p>

<p><strong>Note:</strong> If more than one person shares the highest salary, the query should select the next highest salary.</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>employees</code> table</p>

<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>
<p><code>departments</code> table</p>

<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>
<p><strong>Output:</strong></p>

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

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


<p>Given a <code>employees</code> and <code>departments</code> table, select the top 3 departments with at least ten employees and rank them according to the percentage of their employees making over 100K in salary.</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>employees</code> table</p>

<table>
<thead>
<tr>
<th>Columns</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>
<p><code>departments</code> table</p>

<table>
<thead>
<tr>
<th>Columns</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>
<p><strong>Output:</strong></p>

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

<tbody>
<tr>
<td><code>percentage_over_100k</code></td>
<td>FLOAT</td>
</tr>

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

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


<p>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). </p>

<p><em>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.</em> </p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>employees</code> table</p>

<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>
<p><code>departments</code> table</p>

<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>
<p><strong>Output</strong>:</p>

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


<p>The schema below is for a retail online shopping company consisting of two tables, <code>attribution</code> and <code>user_sessions</code>.</p>

<ul>
<li><p>The attribution table logs a session visit for each row.</p></li>

<li><p>If <code>conversion</code> is <code>true</code>, then the user converted to buying on that session.</p></li>

<li><p>The <code>channel</code> column represents which advertising platform the user was attributed to for that specific session.</p></li>

<li><p>Lastly the <code>user_sessions</code> table maps many to one session visits back to one user.</p></li>
</ul>

<p>First touch attribution is defined as the channel with which the converted user was associated when they first discovered the website.</p>

<p>Calculate the first touch attribution for each <code>user_id</code> that converted. </p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>attribution</code> table</p>

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

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

<tr>
<td><code>channel</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>conversion</code></td>
<td>BOOLEAN</td>
</tr>
</tbody>
</table>
<p><code>user_sessions</code> table</p>

<table>
<thead>
<tr>
<th>column</th>
<th>type</th>
</tr>
</thead>

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

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

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Example output:</strong></p>

<table>
<thead>
<tr>
<th><code>user_id</code></th>
<th><code>channel</code></th>
</tr>
</thead>

<tbody>
<tr>
<td>123</td>
<td>facebook</td>
</tr>

<tr>
<td>145</td>
<td>google</td>
</tr>

<tr>
<td>153</td>
<td>facebook</td>
</tr>

<tr>
<td>172</td>
<td>organic</td>
</tr>

<tr>
<td>173</td>
<td>email</td>
</tr>
</tbody>
</table>


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

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

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">N</span> <span class="o">=</span> <span class="mi">3</span>
</span></span></code></pre>

<p><strong>Output:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">prime_numbers</span><span class="p">(</span><span class="n">N</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="p">[</span><span class="mi">2</span><span class="p">,</span><span class="mi">3</span><span class="p">]</span>
</span></span></code></pre>


<p>Given two sorted lists, write a function to merge them into one sorted list.</p>

<p><em>Bonus: What’s the time complexity?</em></p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">list1</span> <span class="o">=</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">5</span><span class="p">]</span>
</span></span><span class="line"><span class="cl"><span class="n">list2</span> <span class="o">=</span> <span class="p">[</span><span class="mi">2</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">6</span><span class="p">]</span>
</span></span></code></pre>

<p><strong>Output:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">merge_list</span><span class="p">(</span><span class="n">list1</span><span class="p">,</span><span class="n">list2</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">5</span><span class="p">,</span><span class="mi">6</span><span class="p">]</span>
</span></span></code></pre>


<p>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?</p>


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

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

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>employees</code> table</p>

<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>
<p><strong>Output:</strong></p>

<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

<p>Let’s say that your company is running a standard control and variant AB test on a feature to increase conversion rates on the landing page. The PM checks the results and finds a .04 p-value.</p>

<p>How would you assess the validity of the result?</p>


Experiment Validity

A/B Testing

<p>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.</p>

<p>What’s the probability that Amy wins?</p>


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?

<p>Write a query to identify the manager with the biggest team size.</p>

<p>You may assume there is only one manager with the largest team size.</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>employees</code> table</p>

<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>
<p><code>managers</code> table</p>

<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>
<p><strong>Output:</strong></p>

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


Manager Team Sizes

<p>Given a table of students and their SAT test scores, write a query to return the two students with the closest test scores with the score difference.</p>

<p>If there are multiple students with the same minimum score difference, select the student name combination that is higher in the alphabet. </p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>scores</code> table</p>

<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>student</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>score</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

<tr>
<td><code>other_student</code></td>
<td>VARCHAR</td>
</tr>

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


<p>Let’s say you run a wine house. You have detailed information about the chemical composition of wines in a <code>wines</code> table.</p>

<p>One day, a customer comes asking specifically for a wine that has</p>

<ul>
<li>Greater or equal to than 13% alcohol content</li>
<li>Ash content less than 2.4</li>
<li>A color intensity less than 3</li>
</ul>

<p>Write a query to find the <code>id</code>s of suitable wines for this customer.</p>

<p><em>Note:</em> All percentages are reported with two numbers before the decimal point; for example, 13.55% is represented as <code>13.55</code> instead of <code>0.1355</code>.</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>wines</code> table</p>

<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>alcohol</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>malic_acid</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>ash</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>alcalinity_of_ash</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>magnesium</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>total_phenols</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>flavanoids</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>nonflavanoid_phenols</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>proanthocyanins</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>color_intensity</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>hue</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>od280_or_od315_of_diluted_wines</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>proline</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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


Let’s say you run a wine house. You have detailed information about the chemical composition of wines in a wines table.

SELECTive Wine Connoisseur

<p>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.</p>

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

<p>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>.</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>employees</code> table</p>

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

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

<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>
<p><strong>Output:</strong></p>

<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

<p>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.</p>

<p><em>Note: Complexity of $O(n)$ required.</em></p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">nums</span> <span class="o">=</span> <span class="p">[</span><span class="mi">0</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">5</span><span class="p">]</span> 
</span></span><span class="line"><span class="cl"><span class="n">missing_number</span><span class="p">(</span><span class="n">nums</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mi">3</span>
</span></span></code></pre>


<p>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?</p>


<p>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?</p>


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

<p>There are two tables. One table is called <code>swipes</code> that holds a row for every Tinder swipe and contains a boolean column that determines if the swipe was a right or left swipe called <code>is_right_swipe</code>. The second is a table named <code>variants</code> that determines which user has which variant of an AB test.</p>

<p>Write a SQL query to output the average number of right swipes for two different variants of a feed ranking algorithm by comparing users that have swiped 10, 50, and 100 swipes in a <code>feed_change</code> experiment.</p>

<p><em>Note: Users have to have swiped at least 10 times to be included in the subset of users to analyze the mean number of right swipes.</em></p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>variants</code> table</p>

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

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

<tr>
<td><code>experiment</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>variant</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>swipes</code> table</p>

<table>
<thead>
<tr>
<th>Columns</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>swiped_user_id</code></td>
<td>INTEGER</td>
</tr>

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

<tr>
<td><code>is_right_swipe</code></td>
<td>BOOLEAN</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

<tr>
<td><code>mean_right_swipes</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>swipe_threshold</code></td>
<td>INTEGER</td>
</tr>

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


Write a SQL query to find the average number of right swipes for different ranking algorithms.

Swipe Precision

<p>We’re given two tables. One is named <code>projects</code> and the other maps employees to the projects they’re working on. </p>

<p>We want to select the five most expensive projects by budget to employee count ratio. But let’s say that we’ve found a bug where there exist duplicate rows in the <code>employee_projects</code> table.</p>

<p>Write a query to account for the error and select the top five most expensive projects by budget to employee count ratio.</p>

<h3>Schema</h3>

<p><strong>Input:</strong></p>

<p><code>projects</code> table</p>

<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>title</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>state_date</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>end_date</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>budget</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>employee_projects</code> table</p>

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

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

<tr>
<td><code>employee_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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

<h3>Example</h3>

<p><strong>Input:</strong></p>

<p><strong><code>projects</code> table</strong></p>

<table>
<thead>
<tr>
<th>id</th>
<th>title</th>
<th>start_date</th>
<th>end_date</th>
<th>budget</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>party</td>
<td>2006-04-17 00:00:00</td>
<td>2006-05-19 00:00:00</td>
<td>25875</td>
</tr>

<tr>
<td>2</td>
<td>diversity</td>
<td>2017-08-09 00:00:00</td>
<td>2017-09-18 00:00:00</td>
<td>77867</td>
</tr>

<tr>
<td>3</td>
<td>integration</td>
<td>2005-06-29 00:00:00</td>
<td>2005-07-19 00:00:00</td>
<td>75987</td>
</tr>

<tr>
<td>4</td>
<td>testing</td>
<td>2009-01-12 00:00:00</td>
<td>2009-05-03 00:00:00</td>
<td>35946</td>
</tr>

<tr>
<td>5</td>
<td>launch</td>
<td>2005-05-02 00:00:00</td>
<td>2005-10-16 00:00:00</td>
<td>66292</td>
</tr>

<tr>
<td>6</td>
<td>meet</td>
<td>2001-09-09 00:00:00</td>
<td>2002-04-04 00:00:00</td>
<td>71243</td>
</tr>

<tr>
<td>7</td>
<td>payroll</td>
<td>2019-09-25 00:00:00</td>
<td>2020-05-05 00:00:00</td>
<td>97071</td>
</tr>

<tr>
<td>8</td>
<td>admin</td>
<td>2000-07-08 00:00:00</td>
<td>2000-09-10 00:00:00</td>
<td>24000</td>
</tr>

<tr>
<td>9</td>
<td>petronas</td>
<td>2016-07-01 00:00:00</td>
<td>2016-11-28 00:00:00</td>
<td>37088</td>
</tr>

<tr>
<td>10</td>
<td>bel</td>
<td>2005-08-03 00:00:00</td>
<td>2006-04-29 00:00:00</td>
<td>61937</td>
</tr>
</tbody>
</table>
<p><strong><code>employee_projects</code> table</strong></p>

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

<tbody>
<tr>
<td>1</td>
<td>7</td>
</tr>

<tr>
<td>1</td>
<td>9</td>
</tr>

<tr>
<td>2</td>
<td>6</td>
</tr>

<tr>
<td>3</td>
<td>1</td>
</tr>

<tr>
<td>3</td>
<td>2</td>
</tr>

<tr>
<td>4</td>
<td>5</td>
</tr>

<tr>
<td>5</td>
<td>3</td>
</tr>

<tr>
<td>5</td>
<td>4</td>
</tr>

<tr>
<td>6</td>
<td>8</td>
</tr>

<tr>
<td>8</td>
<td>10</td>
</tr>

<tr>
<td>9</td>
<td>11</td>
</tr>

<tr>
<td>10</td>
<td>12</td>
</tr>

<tr>
<td>5</td>
<td>5</td>
</tr>

<tr>
<td>9</td>
<td>11</td>
</tr>

<tr>
<td>6</td>
<td>8</td>
</tr>

<tr>
<td>4</td>
<td>5</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>title</th>
<th>budget_per_employee</th>
</tr>
</thead>

<tbody>
<tr>
<td>diversity</td>
<td>77867</td>
</tr>

<tr>
<td>meet</td>
<td>71243</td>
</tr>

<tr>
<td>bel</td>
<td>61937</td>
</tr>

<tr>
<td>integration</td>
<td>37993.5</td>
</tr>

<tr>
<td>petronas</td>
<td>37088</td>
</tr>
</tbody>
</table>


Project Budget Error

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


<p>What does a p-value in a statistical test represent?</p>

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

P-value to a Layman I

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

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

P-value to a Layman II

<p>Netflix has hired people to rate movies.</p>

<p>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.</p>

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


<p>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.</p>

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

<p>We’re given two tables. One is named <code>projects</code> and the other maps employees to the projects they’re working on.</p>

<p>Write a query to get the top five most expensive projects by budget to employee count ratio.</p>

<p><em>Note: Exclude projects with 0 employees. Assume each employee works on only one project.</em></p>

<p><code>projects</code> table</p>

<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>title</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>state_date</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>end_date</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>budget</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>employee_projects</code> table</p>

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

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

<tr>
<td><code>employee_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


Employee Project Budgets

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

<p>Give an example of the tradeoffs between the two.</p>


<p>In machine learning, when would you use a bagging algorithm over a boosting algorithm?</p>


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

Bagging vs. Boosting

Bagging vs Boosting

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

<p>How would you design the system?</p>

<p><em>Note: Sketch a star schema to explain your design.</em></p>


Design a data warehouse for a new online retailer

Retailer Data Warehouse

<p>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 <sup>2</sup>⁄<sub>3</sub> chance of telling you the truth and a <sup>1</sup>⁄<sub>3</sub> chance of messing with you by lying. All 3 friends tell you that “Yes” it is raining.</p>

<p>What is the probability that it’s actually raining in Seattle?</p>


<p>What is the <strong>main</strong> difference between the Frequentist and Bayesian methods of probabilistic analysis?</p>


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

Frequentist Vs Bayesian Analysis

<p>Given two strings, <code>string1</code> and <code>string2</code>, write a function <code>str_map</code> to determine if there exists a one-to-one correspondence (bijection) between the characters of <code>string1</code> and <code>string2</code>.</p>

<p>For the two strings, our correspondence must be between characters in the same position/index.</p>

<p><strong>Example 1:</strong></p>

<p><strong>Input:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">string1</span> <span class="o">=</span> <span class="s1">&#39;qwe&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">string2</span> <span class="o">=</span> <span class="s1">&#39;asd&#39;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">string_map</span><span class="p">(</span><span class="n">string1</span><span class="p">,</span> <span class="n">string2</span><span class="p">)</span> <span class="o">==</span> <span class="kc">True</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="c1"># q = a, w = s, and e = d</span>
</span></span></code></pre>

<p><strong>Example 2:</strong></p>

<p><strong>Input:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">string1</span> <span class="o">=</span> <span class="s1">&#39;donut&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">string2</span> <span class="o">=</span> <span class="s1">&#39;fatty&#39;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">string_map</span><span class="p">(</span><span class="n">string1</span><span class="p">,</span> <span class="n">string2</span><span class="p">)</span> <span class="o">==</span> <span class="kc">False</span>
</span></span><span class="line"><span class="cl"><span class="c1"># cannot map two distinct characters to two equal characters</span>
</span></span></code></pre>

<p><strong>Example 3:</strong></p>

<p><strong>Input:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">string1</span> <span class="o">=</span> <span class="s1">&#39;enemy&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">string2</span> <span class="o">=</span> <span class="s1">&#39;enemy&#39;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">string_map</span><span class="p">(</span><span class="n">string1</span><span class="p">,</span> <span class="n">string2</span><span class="p">)</span> <span class="o">==</span> <span class="kc">True</span>
</span></span><span class="line"><span class="cl"><span class="c1"># there exists a one-to-one correspondence between equivalent strings</span>
</span></span></code></pre>

<p><strong>Example 4:</strong></p>

<p><strong>Input:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">string1</span> <span class="o">=</span> <span class="s1">&#39;enemy&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">string2</span> <span class="o">=</span> <span class="s1">&#39;ymene&#39;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">string_map</span><span class="p">(</span><span class="n">string1</span><span class="p">,</span> <span class="n">string2</span><span class="p">)</span> <span class="o">==</span> <span class="kc">False</span>
</span></span><span class="line"><span class="cl"><span class="c1"># since our correspondence must be between characters of the same index, this case returns &#39;False&#39; as we must map e = y AND e = e</span>
</span></span></code></pre>


String Mapping

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

<p>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. </p>

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

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


Impression Reach

<p>What are the assumptions of linear regression?</p>


Which assumption of the residuals of the standard linear regression model can not be overcome by increasing the sample size?

Regression assumptions

<p>Say you are tasked with analyzing how well a model fits the data given. You want to determine a relationship between two variables.</p>

<p>What is the downside of only using the R-Squared $(R^2)$ value to do so?</p>


<p>In the context of regression analysis, why is it misleading to rely solely on a high R-squared ($R^2$) value to infer a causal relationship between variables?</p>

In the context of regression analysis, why is it misleading to rely solely on a high R-squared ($R^2$) value to infer a causal relationship between variables?

Using R Squared I

<p>Which of the following best describes the R-squared ($R^2$) value in the context of a regression model?</p>

Which of the following best describes the R-squared ($R^2$) value in the context of a regression model?

Using R Squared II

<p>What is a limitation of using R-squared ($R^2$) as the only metric to evaluate a regression model's fit?</p>

What is a limitation of using R-squared ($R^2$) as the only metric to evaluate a regression model's fit?

Using R Squared III

<p>Let’s say you are designing a marketplace for your website.</p>

<p>Selling firearms is prohibited by your website’s Terms of Service Agreement (not to mention the laws of your country). To this end, you want to create a system that can automatically detect if a listing on the marketplace is selling a gun.</p>

<p>How would you go about doing this?</p>


<p>While designing a system to detect firearms listings on a marketplace website, what metrics could be used to minimize both false positives and false negatives?</p>

While designing a system to detect firearms listings on a marketplace website, what metrics could be used to minimize both false positives and false negatives?

Detecting Firearm Sales

How would you build a model to detect if a post on a marketplace is talking about selling a gun?

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

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

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

<p><strong>Example:</strong></p>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">is_balanced</span><span class="p">(</span><span class="s1">&#39;(())[]</span><span class="si">{}</span><span class="s1">&#39;</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">True</span>
</span></span></code></pre>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">is_balanced</span><span class="p">(</span><span class="s1">&#39;{([()</span><span class="si">{}</span><span class="s1">])()}&#39;</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">True</span>
</span></span></code></pre>

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">is_balanced</span><span class="p">(</span><span class="s1">&#39;</span><span class="si">{}</span><span class="s1">[]())&#39;</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">False</span>
</span></span></code></pre>

<hr/>


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

ConsultAdd Interview Questions

ConsultAdd Interview Guides

ConsultAdd Interview Questions

Challenge

ConsultAdd Salaries by Position

Discussion & Interview Experiences

Discussion & Interview Experiences