Prepare for and practice interview questions from Freeman Company.

Freeman Company Interview Questions

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

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

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

First Touch Attribution

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

Closest SAT Scores

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

Which model would perform better and why?

Booking 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

Product Sense & Metrics

How would you investigate a decline in the average number of comments per user?

Decreasing Comments

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

What would you investigate to diagnose what's happening?

WAU vs Open Rates

How would you verify the claim that Facebook is losing young users and what test metrics would you look at

Losing Users

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

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


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

Rolling Bank Transactions

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

A/B Testing

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

<p>Write a query to identify customers who placed more than three transactions each in both 2019 and 2020.</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>users</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>customer_name</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>


<p>Which of the following queries will return a list of customers and the total amount they have spent on orders?</p>


Which of the following queries will return a list of customers and the total amount they have spent on orders?

Customer Orders

Write a query to identify customers who placed more than three transactions each in both 2019 and 2020

<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>Given a table of product subscriptions with a subscription start date and end date for each user, write a query that returns true or false whether or not each user has a subscription date range that overlaps with any other completed subscription.</p>

<p>Completed subscriptions have <code>end_date</code> recorded.</p>

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

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

<p><code>subscriptions</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>start_date</code></td>
<td>DATETIME</td>
</tr>

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

<table>
<thead>
<tr>
<th><code>user_id</code></th>
<th><code>start_date</code></th>
<th><code>end_date</code></th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>2019-01-01</td>
<td>2019-01-31</td>
</tr>

<tr>
<td>2</td>
<td>2019-01-15</td>
<td>2019-01-17</td>
</tr>

<tr>
<td>3</td>
<td>2019-01-29</td>
<td>2019-02-04</td>
</tr>

<tr>
<td>4</td>
<td>2019-02-05</td>
<td>2019-02-10</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

<tr>
<td>2</td>
<td>1</td>
</tr>

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

<tr>
<td>4</td>
<td>0</td>
</tr>
</tbody>
</table>


Write a query to return whether each user's subscription date range overlaps with any other completed subscription

Subscription Overlap

<p>Write a function <code>compute_deviation</code> that takes in a list of dictionaries with a key and list of integers and returns a dictionary with the standard deviation of each list.</p>

<p><em>Note: This should be done without using the NumPy built-in functions.</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="nb">input</span> <span class="o">=</span> <span class="p">[</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span>
</span></span><span class="line"><span class="cl">        <span class="s1">&#39;key&#39;</span><span class="p">:</span> <span class="s1">&#39;list1&#39;</span><span class="p">,</span>
</span></span><span class="line"><span class="cl">        <span class="s1">&#39;values&#39;</span><span class="p">:</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">2</span><span class="p">,</span><span class="mi">3</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">2</span><span class="p">,</span><span class="mi">3</span><span class="p">],</span>
</span></span><span class="line"><span class="cl">    <span class="p">},</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span>
</span></span><span class="line"><span class="cl">        <span class="s1">&#39;key&#39;</span><span class="p">:</span> <span class="s1">&#39;list2&#39;</span><span class="p">,</span>
</span></span><span class="line"><span class="cl">        <span class="s1">&#39;values&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">,</span><span class="mi">34</span><span class="p">,</span><span class="mi">12</span><span class="p">,</span><span class="mi">40</span><span class="p">,</span><span class="mi">3</span><span class="p">,</span><span class="mi">9</span><span class="p">,</span><span class="mi">7</span><span class="p">],</span>
</span></span><span class="line"><span class="cl">    <span class="p">}</span>
</span></span><span class="line"><span class="cl"><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="n">output</span> <span class="o">=</span> <span class="p">{</span><span class="s1">&#39;list1&#39;</span><span class="p">:</span> <span class="mf">1.12</span><span class="p">,</span> <span class="s1">&#39;list2&#39;</span><span class="p">:</span> <span class="mf">14.19</span><span class="p">}</span>
</span></span></code></pre>


Write a function compute_deviation that returns the standard deviation of each list in a list of dictionaries

Compute Deviation

Statistics

<p>Given two tables:  <code>accounts</code>, and <code>downloads</code>, find the average number of downloads for free vs paying accounts, broken down by day.</p>

<p><em>Note: You only need to consider accounts that have had at least one download before when calculating the average.</em></p>

<p><em>Note: round <code>average_downloads</code> to 2 decimal places.</em></p>

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

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

<p><code>accounts</code> table</p>

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

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

<tr>
<td><code>paying_customer</code></td>
<td>BOOLEAN</td>
</tr>
</tbody>
</table>
<p><code>downloads</code> table</p>

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

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

<tr>
<td><code>download_date</code></td>
<td>DATETIME</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>download_date</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>paying_customer</code></td>
<td>BOOLEAN</td>
</tr>

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


Write a query to find the average downloads for free vs paying accounts by day, rounded to 2 decimals

Download Facts

<p>We’re given a table of product purchases. Each row in the table represents an individual user product purchase.</p>

<p>Write a query to get the number of customers that were upsold, or in other words, the number of users who bought additional products after their first purchase.</p>

<p><em>Note: If the customer purchased two things on the same day, that does not count as an upsell, as they were purchased within a similar timeframe.</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><strong>Output:</strong></p>

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

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


Write a query to get the number of customers that were upsold

Upsell Transactions

<p>Write a query to show the number of users, number of transactions placed, and total order amount per month in the year 2020. Assume that we are only interested in the monthly reports for a single year (January-December).</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><code>users</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>sex</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>month</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>num_customers</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>num_orders</code></td>
<td>INTEGER</td>
</tr>

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


Write a query to show the number of users, transactions, and total order amount per month in 2020

Monthly Customer Report

<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>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>Let’s say you want to test the close friends feature on Instagram Stories.</p>

<p>How would you make a control group and test group to account for network effects?</p>


<p>What could be a potential risk when Facebook segments the metrics of the user interface change by market or demographic groups?</p>

What could be a potential risk when Facebook segments the metrics of the user interface change by market or demographic groups?

Network Experiment Design I

<p>What is the primary metric that Facebook should monitor if it decides to make the user interface of its posting feature more like Instagram's?</p>

What is the primary metric that Facebook should monitor if it decides to make the user interface of its posting feature more like Instagram's?

Network Experiment Design II

<p>If Facebook changes its user interface to mimic Instagram's, which adverse effect should they anticipate and monitor?</p>

If Facebook changes its user interface to mimic Instagram's, which adverse effect should they anticipate and monitor?

Network Experiment Design III

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

Network Experiment Design

<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>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>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>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>Let’s say we want to build a naive recommender. We’re given two tables, one table called <code>friends</code> with a <code>user_id</code> and <code>friend_id</code> columns representing each user’s friends, and another table called <code>page_likes</code> with a <code>user_id</code> and a <code>page_id</code> representing the page each user liked.</p>

<p>Write an SQL query to create a metric to recommend pages for each user based on recommendations from their friend’s liked pages. </p>

<p><em>Note: It shouldn’t recommend pages that the user already likes.</em></p>

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

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

<p><code>friends</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>friend_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>page_likes</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>page_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>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>page_id</code></td>
<td>INTEGER</td>
</tr>

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


Write a query to get the number of friends of a user that like a specific page

Liked Pages

<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 a string <code>str</code>, write a function <code>perm_palindrome</code> to determine whether there exists a permutation of <code>str</code> that is a palindrome.</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="nb">str</span> <span class="o">=</span> <span class="s1">&#39;cacerra&#39;</span>
</span></span><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">perm_palindrome</span><span class="p">(</span><span class="nb">str</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">True</span>
</span></span></code></pre>

<p><strong>“cacerra”</strong> returns True since it can be rearranged to form <strong>“racecar”</strong> which is a palindrome.</p>


Determine whether there exists a permutation of an input string that is a palindrome.

Permutation Palindrome

<p>Let’s say that you’re a data scientist on the engagement team. A product manager comes up to you and says that the weekly active users metric is up 5% but email notification open rates are down 2%.</p>

<p>What would you investigate to diagnose what’s happening?</p>


Email Open Rate

<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 a table of <code>transactions</code>, write a query to find the average quantity of each product purchased per transaction each year.</p>

<p>The output should include the year, product_id, and average quantity of the product purchased per transaction that year. The output table should be sorted by year and product_id in ascending order. The value for avg_quantity should be rounded to two decimal places.</p>

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

<p><strong>Input:</strong>
<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>year</code></td>
<td>INTEGER</td>
</tr>

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

<tr>
<td><code>avg_quantity</code></td>
<td>DECIMAL</td>
</tr>
</tbody>
</table>


Find the average yearly purchases for each product

Average Quantity

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

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

Freeman Company Interview Questions

Freeman Company Interview Guides

Freeman Company Interview Questions

Challenge

Discussion & Interview Experiences

Discussion & Interview Experiences