Prepare for and practice interview questions from Ocrolus across topics like Statistics, A/B Testing, Machine Learning and more.

Ocrolus Interview Questions

Ocrolus 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

Find Bigrams

Write a function to create a single dataframe with complete addresses in the format of street, city, state, zip code.

Complete Addresses

Write a function friendship_timeline to generate an output that lists the pairs of friends with their corresponding timestamps of the friendship beginning and then the timestamp of the friendship ending. 

Friendship Timeline

Machine Learning

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

Encoding Categorical Features

Interpret coefficients of logistic regression for categorical and boolean variables

Coefficients of Logistic Regression

How would you build a job recommendation feed?

Job Recommendation

Write a function calculate_rmse to calculate the root mean squared error from y_pred and y_true

RMS Error

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

Missing Housing Data

Probability

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

First to Six

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

Compute the expected number of good ads in each scenario and the probability a bad rating came from a lazy rater

Ad Raters

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Write a query that returns all neighborhoods that have 0 users.

Empty Neighborhoods

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

First Touch Attribution

Paired Products

Last Transaction

A/B Testing

How would you assess the validity of the result?

Experiment Validity

How would you determine if the new model predicts delivery times better than the old model

Delivery Estimate Model

Button AB Test

A/B Test Power Size

How would you make a control group and test group to account for network effects

Network Experiment Design

<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

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

Bagging vs Boosting

<p>How would you tackle multicollinearity in multiple linear regression?</p>


How would you tackle multicollinearity in multiple linear regression

Multicollinearity in Regression

Statistics

<p>What’s the relationship between PCA and K-means clustering?</p>


<p>What does the variable “k” in k-means clustering refer to?</p>


What does the variable "k" in k-means clustering refer to?

Input of K-means

Explain the relationship between PCA and K-means clustering

PCA and K-Means

<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>We’re given two tables, a <code>users</code> table with demographic information and the neighborhood they live in and a <code>neighborhoods</code> table.</p>

<p>Write a query that returns all neighborhoods that have 0 users. </p>

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

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

<p><code>users</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>

<tr>
<td><code>neighborhood_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><code>neighborhoods</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>

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

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

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


<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 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 two strings <code>A</code> and <code>B</code>, write a function <code>can_shift</code> to return whether or not <code>A</code> can be shifted some number of places to get <code>B</code>.</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">A</span> <span class="o">=</span> <span class="s1">&#39;abcde&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">B</span> <span class="o">=</span> <span class="s1">&#39;cdeab&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">can_shift</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">B</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="n">A</span> <span class="o">=</span> <span class="s1">&#39;abc&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">B</span> <span class="o">=</span> <span class="s1">&#39;acb&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">can_shift</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">B</span><span class="p">)</span> <span class="o">==</span> <span class="kc">False</span>
</span></span></code></pre>


Write a function can_shift to return whether or not A can be shifted some number of places to get B

String Shift

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


Get the top 3 highest employee salaries by department

Top Three Salaries

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

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

<p>Write a query to find the top five paired products and their names.</p>

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

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

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

<p><code>transactions</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>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>
<p><code>products</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>price</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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


<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>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>Let’s say that you work at a bank that wants to build a model to detect fraud on the platform.</p>

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

<p>How would we build this model?</p>


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

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

<p>Which statement is true?</p>


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?

Bank Fraud Model

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

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


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

The Brackets Problem

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

Explain what a p-value is to someone who is not technical

P-value to a Layman

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


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

Impression Reach

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


<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

What is the probability that a movie is rated good

<p>Given a table of bank transactions with columns <code>id</code>, <code>transaction_value</code>, and <code>created_at</code> representing the date and time for each transaction, write a query to get the last transaction for each day.</p>

<p>The output should include the id of the transaction, datetime of the transaction, and the transaction amount. Order the transactions by datetime.</p>

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

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

<p><code>bank_transactions</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>created_at</code></td>
<td>DATETIME</td>
</tr>

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

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

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

<tr>
<td><code>transaction_value</code></td>
<td>FLOAT</td>
</tr>

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


<p>Let’s say we want to build a new delivery time estimate model for consumers ordering food delivery.</p>

<p>How would you determine if the new model predicts delivery times better than the old model?</p>


<p>When rolling out a new delivery time estimate model in production, what is an important consideration?</p>

When rolling out a new delivery time estimate model in production, what is an important consideration?

Delivery Estimate Model I

<p>What is one way to measure the performance of a new delivery time estimate model for food delivery?</p>

What is one way to measure the performance of a new delivery time estimate model for food delivery?

Delivery Estimate Model II

<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>A team wants to A/B test multiple different changes through a sign-up funnel.</p>

<p>For example, on a page, a button is currently red and at the top of the page. They want to see if changing a button from red to blue and/or from the top of the page to the bottom of the page will increase click-through.</p>

<p>How would you set up this test?</p>


<p>Your team wants to run an AB test on the color (red or blue) and position (top/bottom of the page) of a button that links to a promotion.</p>

<p>How many different variants of the test should you run to see the effect that a red button has on the bottom of the page, if currently the button is blue and on the top of the page.</p>


Your team wants to run an AB test on the color (red or blue) and position (top/bottom of the page) of a button that links to a promotion.

How many different variants of the test should you run to see the effect that a red button has on the bottom of the page, if currently the button is blue and on the top of the page.

A/B Test Variants

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


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

Prime to N

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


Describing a data project and its challenges

Hurdles In Data Projects

Behavioral

<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>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>Over budget on a project is defined when the salaries, prorated to the day, exceed the budget of the project.</p>

<p>For example, if Alice and Bob both combined income make 200K and work on a project of a budget of 50K that takes half a year, then the project is over budget given 0.5 * 200K = 100K &gt; 50K.</p>

<p>Write a query to forecast the budget for all projects and return a label of <code>&#34;overbudget&#34;</code> if it is over budget and <code>&#34;within budget&#34;</code> otherwise.</p>

<p><em>Note: Assume that employees only work on one project at a time.</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>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><strong>departments</strong> 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><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>project_forecast</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>


Write a query to forecast each project's budget and label it overbudget or within budget

Over-Budget Projects

<p>Given the <code>transactions</code> table below, write a query that finds the third purchase of every user.</p>

<p><em>Note: Sort the results by the <code>user_id</code> in ascending order. If a user purchases two products at the same time, the lower <code>id</code> field is used to determine which is the first purchase.</em></p>

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

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

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

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

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


Write a query to find the third purchase of every user sorted by user_id ascending

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

Ocrolus Interview Questions

Ocrolus Interview Guides

Ocrolus Interview Questions

Challenge

Ocrolus Salaries by Position

Discussion & Interview Experiences

Discussion & Interview Experiences