Prepare for and practice interview questions from Loanpal.

Loanpal Interview Questions

Loanpal 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 can_shift to return whether or not A can be shifted some number of places to get B

String Shift

Given a list of integers, and an integer N, write a function to find all combinations that sum to the value N.

Sum to N

Minimum Change

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 we build a model to detect fraud and text customers to approve or deny fraudulent transactions

Bank Fraud Model

Rebalancing outcome probabilities for a classifier on imbalanced data.

Rebalance Probabilities

Lasso vs Ridge

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Write a SQL query to create a histogram of the number of comments per user in the month of January 2020.

Comments Histogram

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

Empty Neighborhoods

Write a query to return the two students with the closest test scores and the score difference

Closest SAT Scores

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

Rolling Bank Transactions

Product Sense & Metrics

How can we measure the success of a 30-day free trial in acquiring and retaining new Netflix users

Netflix Retention

How would you measure the success of Facebook Groups

Group Success

How would you measure the success of the Instagram TV product

Instagram TV Success

How do we measure the launch of Robinhood’s fractional shares program

Fractional Shares

What’s the expected churn rate in March for all customers that bought the product since January 1st?

Expected Churn

Business Case

How would you determine what our next partner card should be

New Partner Card

Why would the number of applicants be steadily decreasing while job postings remain the same

Declining Applicants

How would we decide which Dashers do these deliveries and whether the criteria should be the same for both cities

Delivery Assignments

How would you determine the success of this new payment structure

Dasher Payment Structure

What would you investigate to find out why the app isn't performing as well as expected in a new country

New Geography

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

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

<p><em>Note: Please use the format <code>&#39;%Y-%m-%d&#39;</code> for the date in the outout</em></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>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>
<p><strong>Output:</strong></p>

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


<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 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>Write a SQL query to create a histogram of the number of comments per user in the month of January 2020.</p>

<p><em>Note: Assume bin buckets class intervals of one.</em></p>

<p><em>Note: Comments that were created outside of January 2020 should be counted in a “0” bucket</em></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>created_at</code></td>
<td>DATETIME</td>
</tr>

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

<tr>
<td><code>mail</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><code>comments</code> table</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>body</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


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


How would you assess the validity of the result?

Experiment Validity

A/B Testing

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


Last Transaction

<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 the two tables, write a SQL query that creates a cumulative distribution of the number of comments per user. Assume bin buckets class intervals of one.</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>created_at</code></td>
<td>DATETIME</td>
</tr>

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

<tr>
<td><code>sex</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><code>comments</code> table</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>body</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


Write a SQL query that creates a cumulative distribution of the number of comments per user with bin buckets of one

Cumulative Distribution

<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

Button AB Test

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


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

Statistics

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

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

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

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

<p><code>download_facts</code> table</p>

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

<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

<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

Third Purchase

<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>The <code>events</code> table tracks every time a user performs a certain action (like, post_enter, etc.) on a platform. An example of this table would look like this:</p>

<table>
<thead>
<tr>
<th>user_id</th>
<th>created_at</th>
<th>action</th>
<th>platform</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>2021-01-01 12:00:00</td>
<td>email_opened</td>
<td>Android</td>
</tr>

<tr>
<td>2</td>
<td>2021-01-01 12:05:00</td>
<td>like</td>
<td>Outlook</td>
</tr>

<tr>
<td>3</td>
<td>2021-01-01 12:10:00</td>
<td>post_enter</td>
<td>Gmail</td>
</tr>

<tr>
<td>4</td>
<td>2021-01-01 12:15:00</td>
<td>post_cancel</td>
<td>Outlook</td>
</tr>

<tr>
<td>5</td>
<td>2021-01-01 12:20:00</td>
<td>like</td>
<td>Gmail</td>
</tr>
</tbody>
</table>
<p>What query would you write to determine how many different users gave a like on June 6, 2020?</p>

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

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

<p><code>events</code> table</p>

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

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

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

<tr>
<td>action</td>
<td>VARCHAR</td>
</tr>

<tr>
<td>platform</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>num_users_gave_like</td>
<td>INTEGER</td>
</tr>
</tbody>
</table>


Find out the number of users that gave a like on a certain date

Like Tracker

<p>Assume you have a logistic model that is heavily weighted on one variable and that one variable has sample data like 50.00, 100.00, 40.00, etc….</p>

<p>Next let’s assume that there was a data quality issue with that variable and an unknown number of values removed the decimal point. For example 100.00 turned into 10000.</p>

<p>Would the model still be valid? Why or why not? How would you fix the model?</p>


<p>In predicting Airbnb booking prices with a dataset containing thousands of diverse features, which regression model could potentially perform better?</p>

In predicting Airbnb booking prices with a dataset containing thousands of diverse features, which regression model could potentially perform better?

Variable Error

Would a logistic model remain valid if one variable had decimal points accidentally removed, and how would you fix it

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

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

<p>Example questions:</p>

<p>Consider a string where all characters are lowercase alphabets.</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">s</span> <span class="o">=</span> <span class="s1">&#39;helloworld&#39;</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">first_uniq_char</span><span class="p">(</span><span class="n">s</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mi">0</span>
</span></span></code></pre>

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

<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">s</span> <span class="o">=</span> <span class="s1">&#39;concussion&#39;</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">first_uniq_char</span><span class="p">(</span><span class="n">s</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mi">4</span>
</span></span></code></pre>

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


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

<p>Let’s say we are given a table <code>user_logins</code>.</p>

<p>Using this table, calculate how many users logged in an identical number of times on January 1st, 2022.</p>

<p>For example, it may be the case that on that date three users logged in seven times, four users logged in five times, two users logged in ten times, etc.</p>

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

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

<p><code>user_logins</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>login_date</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


Find how many users logged in a certain number of times on a given day

Daily Logins

<p>Given a table of <code>transactions</code> and <code>products</code>, write a function to get the <code>month_over_month</code> change in revenue for the year 2019. Make sure to round <code>month_over_month</code> to 2 decimal places.</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>month</code></td>
<td>INTEGER</td>
</tr>

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


Write a function to get the month_over_month change in revenue for 2019 rounded to 2 decimal places

Month Over Month

<p>Given a list of strings of letters from <code>a</code> to <code>z</code>, create a function, <code>sum_alphabet</code>, that returns a list of the <strong>alphabet sum</strong> of each word in the string.</p>

<p>The <strong>alphabet sum</strong> is the sum of the ordinal position of each of the string’s letters in the standard English alphabet ordering. So, the letter <code>a</code> will have a value of <code>1</code>, <code>z</code> will have a value of <code>26</code>, and so on.</p>

<p>As an example of the alphabet sum of a string, the string <code>sport</code> will have an <code>alphabet sum of 19 + 16 + 15 + 18 + 20 = 88</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">words</span> <span class="o">=</span> <span class="p">[</span><span class="s2">&#34;sport&#34;</span> <span class="p">,</span> <span class="s2">&#34;good&#34;</span> <span class="p">,</span> <span class="s2">&#34;bad&#34;</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">sum_alphabet</span><span class="p">(</span><span class="n">words</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="p">[</span><span class="mi">88</span> <span class="p">,</span> <span class="mi">41</span> <span class="p">,</span> <span class="mi">7</span><span class="p">]</span>
</span></span></code></pre>


Write a function sum_alphabet that returns the alphabet sum of each word in a list of strings

Alphabet Sum

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


Paired Products

<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>You have a table containing information about the time that employees have worked on a project.</p>

<p>Write a query to find how many unique calendar days each employee worked. Order your query by the <code>employee_id</code>.</p>

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

<p>An employee worked on two projects, and recorded their time like this:</p>

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

<tbody>
<tr>
<td>1	</td>
<td>1</td>
<td>2024-01-01</td>
<td>2024-01-06</td>
</tr>

<tr>
<td>1	</td>
<td>2</td>
<td>2024-01-03</td>
<td>2024-01-07</td>
</tr>
</tbody>
</table>
<p>The number of unique calendar days is therefore 7.</p>

<h3>Schema</h3>

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

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

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

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

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

<tr>
<td><code>start_date</code>	</td>
<td>	DATE	</td>
</tr>

<tr>
<td><code>end_date</code>	</td>
<td>	DATE	</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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

<h3>Example</h3>

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

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

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

<tbody>
<tr>
<td>1</td>
<td>1</td>
<td>2024-01-01</td>
<td>2024-01-15</td>
</tr>

<tr>
<td>1</td>
<td>1</td>
<td>2024-01-21</td>
<td>2024-01-31</td>
</tr>

<tr>
<td>1</td>
<td>2</td>
<td>2024-01-25</td>
<td>2024-02-28</td>
</tr>

<tr>
<td>2</td>
<td>3</td>
<td>2024-01-01</td>
<td>2024-01-31</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

<tbody>
<tr>
<td>1</td>
<td>54</td>
</tr>

<tr>
<td>2</td>
<td>31</td>
</tr>
</tbody>
</table>


Unique Work Days

<p>Let’s say you work at Costco. Costco has a database with two tables. The first is <code>users</code> composed of user information, including their registration date, and the second is <code>purchases</code> that has the entire item purchase history (if any) for those users.</p>

<p>Write a query to get the total amount spent on each item in the ‘purchases’ table by users that registered in 2022.</p>

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

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

<p><code>users</code> table</p>

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

<tr>
<td><code>registration_date</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><code>purchases</code> table</p>

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

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

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

<tr>
<td><code>item</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>price</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>purchase_date</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


Total Spent on Products

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

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

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

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

<p><em>Note: Please return only one query with each number in a different row</em></p>

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

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

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

Loanpal Interview Questions

Loanpal Interview Guides

Loanpal Interview Questions

Challenge

Discussion & Interview Experiences

Discussion & Interview Experiences