Prepare for and practice interview questions from Novant Health across topics like Product Metrics, Statistics, SQL and more.

Novant Health Interview Questions

Novant Health Interview Guides

Machine Learning

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

Lasso vs Ridge

What are some machine learning methods for building this system

FAQ Matching

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

Bagging vs Boosting

Data Structures & Algorithms

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

Prime to N

Write a function moving_window to find the moving window average

Moving Window

Given a json string with nested objects, write a function that flattens all the objects to a single key-value dictionary.

Flatten JSON

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

Find Duplicate Numbers in a List

Given a list of strings, write a function that returns the longest common prefix

Common Prefix

Last Transaction

Flight Records

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

Liked Pages

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

Cumulative Distribution

Given a users table, write a query to get the cumulative number of new users added by the day, with the total reset every month. 

Cumulative Reset

Product Sense & Metrics

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

Decreasing Comments

Where and how could you promote Instagram through Facebook

Promoting Instagram

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 would you measure the success of an online marketplace introducing an audio chat feature given a dataset of their usage?

Audio Chat Success

Statistics

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

P-value to a Layman

How many more samples are needed to decrease the margin of error to 0.3

Reducing Error Margin

What are the assumptions of linear regression?

Assumptions of Linear Regression

What is the downside of only using the R-Squared (R^2) value to determine a relationship between two variables

Using R Squared

Explain the difference between covariance and correlation and provide an example

Covariance vs Correlation

<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 an integer <code>N</code>, write a function that returns a list of all of the prime numbers up to <code>N</code>.</p>

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

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

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

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

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

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


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


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


<p>Let’s say you work at an advertising firm. You have a table of users’ impressions of ad campaigns over time. Each <code>impression_id</code> consists of values of user engagement specified by <code>Excited</code>, <code>OK</code>, and <code>Bored</code>.</p>

<p>Write a query to find all users who were at some point “Excited” and have never been “Bored” with a campaign.</p>

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

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

<p><code>ad_impressions</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>dt</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>campaign_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>impression_id</code></td>
<td>TEXT</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>
</tbody>
</table>


Write a query to find all users that were at some point "Excited" and have never been "Bored" with a campaign.

Always Excited Users

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

<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>The probability that it will rain tomorrow is dependent on whether or not it is raining today and whether or not it rained yesterday.</p>

<p>If it rained yesterday and today, there is a 20% chance it will rain tomorrow. If it rained one of the days, there is a 60% chance it will rain tomorrow. If it rained neither today nor yesterday, there is a 20% chance it will rain tomorrow.</p>

<p>Given that it is raining today and that it rained yesterday, write a function <code>rain_days</code> to calculate the probability that it will rain on the nth day after today.</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">5</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">rain_days</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mf">0.39968</span>
</span></span></code></pre>


Given that it is raining today and that it rained yesterday, write a function to calculate the probability that it will rain on the nth day after today.

Rain in N Days

Probability

<p>Write a query to create a new table, named flight routes, that displays unique pairs of two locations.</p>

<p><em>Note: Duplicate pairs from the <code>flights</code> table, such as Dallas to Seattle and Seattle to Dallas, should have one entry in the flight routes table.</em></p>

<h3>Schema</h3>

<p><code>flights</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>source_location</code></td>
<td>VARCHAR</td>
</tr>

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

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

<h3>Example</h3>

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

<p><strong><code>flights</code> table</strong></p>

<table>
<thead>
<tr>
<th>id</th>
<th>source_location</th>
<th>destination_location</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>Seattle, WA</td>
<td>Dallas, TX</td>
</tr>

<tr>
<td>2</td>
<td>Dallas, TX</td>
<td>Seattle, WA</td>
</tr>

<tr>
<td>3</td>
<td>Seattle, WA</td>
<td>San Francisco, CA</td>
</tr>

<tr>
<td>4</td>
<td>San Francisco, CA</td>
<td>Dallas, TX</td>
</tr>

<tr>
<td>5</td>
<td>San Francisco, CA</td>
<td>Portland, OR</td>
</tr>

<tr>
<td>6</td>
<td>Seattle, WA</td>
<td>Dallas, TX</td>
</tr>

<tr>
<td>7</td>
<td>Portland, OR</td>
<td>San Francisco, CA</td>
</tr>

<tr>
<td>8</td>
<td>San Francisco, CA</td>
<td>Los Angeles, CA</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>destination_one</th>
<th>destination_two</th>
</tr>
</thead>

<tbody>
<tr>
<td>Dallas, TX</td>
<td>Seattle, WA</td>
</tr>

<tr>
<td>Portland, OR</td>
<td>San Francisco, CA</td>
</tr>

<tr>
<td>San Francisco, CA</td>
<td>Seattle, WA</td>
</tr>

<tr>
<td>Dallas, TX</td>
<td>San Francisco, CA</td>
</tr>

<tr>
<td>Los Angeles, CA</td>
<td>San Francisco, CA</td>
</tr>
</tbody>
</table>


<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 for a social media company that has just done a launch in a new city. Looking at weekly metrics, you see a slow decrease in the average number of comments per user from January to March in this city.</p>

<p>The company has been consistently growing new users in the city from January to March.</p>

<p>What are some reasons why the average number of comments per user would be decreasing and what metrics would you look into?</p>


<p>What metric could be used to measure if active users are still engaging on the platform despite an overall decrease in the average number of comments per user?</p>

What metric could be used to measure if active users are still engaging on the platform despite an overall decrease in the average number of comments per user?

<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

<p>Let’s say that you’re working on a job recommendation engine. You have access to all user Linkedin profiles, a list of jobs each user applied to, and answers to questions that the user filled in about their job search.</p>

<p>Using this information, how would you build a job recommendation feed?</p>


<p>What problem can arise in job recommendation systems with regard to initial recommendations?</p>

What problem can arise in job recommendation systems with regard to initial recommendations?

<p>Given a <code>users</code> table, write a query to get the cumulative number of new users added by the day, with the total reset every month. </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>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>DATE</th>
<th>INTEGER</th>
</tr>
</thead>

<tbody>
<tr>
<td>2020-01-01</td>
<td>5</td>
</tr>

<tr>
<td>2020-01-02</td>
<td>12</td>
</tr>

<tr>
<td>…</td>
<td>…</td>
</tr>

<tr>
<td>2020-02-01</td>
<td>8</td>
</tr>

<tr>
<td>2020-02-02</td>
<td>17</td>
</tr>

<tr>
<td>2020-02-03</td>
<td>23</td>
</tr>
</tbody>
</table>


<p>How does random forest generate the forest? Additionally, why would we use it over other algorithms such as logistic regression?</p>


<p>What happens when you average the output on multiple decision trees?</p>


What happens when you average the output on multiple decision trees?

Average Trees

How does random forest generate the forest and why use it over logistic regression

Random Forest Explanation

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

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

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


Design a data warehouse for a new online retailer

Retailer Data Warehouse

Data Modeling

<p>Let’s say you’re interning at a fintech startup that’s building a platform for online games of chance. During testing, one of your simulations flips a coin 10 times and gets 8 tails and 2 heads.</p>

<p>How would you determine if the coin is fair, and what statistical approach would you use to support your conclusion?</p>


How would you assess if a coin is fair after observing 8 tails in 10 flips?

Fair Coin

<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>Write a function <code>calculate_rmse</code> to calculate the root mean squared error of a regression model. The function should take in two lists, one that represents the predictions <code>y_pred</code> and another with the target values <code>y_true</code>.</p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">y_pred</span> <span class="o">=</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></span><span class="line"><span class="cl"><span class="n">y_true</span> <span class="o">=</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></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">calculate_rmse</span><span class="p">(</span><span class="n">y_pred</span><span class="p">,</span><span class="n">y_true</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mi">0</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">y_pred</span> <span class="o">=</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></span><span class="line"><span class="cl"><span class="n">y_true</span> <span class="o">=</span> <span class="p">[</span><span class="mi">5</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">3</span><span class="p">]</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">calculate_rmse</span><span class="p">(</span><span class="n">y_pred</span><span class="p">,</span><span class="n">y_true</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mf">1.63</span>
</span></span></code></pre>


<p>Given two strings, write a function to return <code>True</code> if the strings are anagrams of each other and <code>False</code> if they are not.</p>

<p><em>Note: A word is not an anagram of itself.</em></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">string_1</span> <span class="o">=</span> <span class="s2">&#34;listen&#34;</span>
</span></span><span class="line"><span class="cl"><span class="n">string_2</span> <span class="o">=</span> <span class="s2">&#34;silent&#34;</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="kc">True</span>
</span></span></code></pre>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">string_1</span> <span class="o">=</span> <span class="s2">&#34;banana&#34;</span>
</span></span><span class="line"><span class="cl"><span class="n">string_2</span> <span class="o">=</span> <span class="s2">&#34;bandana&#34;</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="kc">False</span>
</span></span></code></pre>


Write a function to return True if two strings are anagrams of each other and False if they are not

Valid Anagram

<p>Let’s say we have a sample size of $n$. The margin of error for our sample size is 3.</p>

<p>How many more samples would we need to decrease the margin of error to 0.3?</p>


<p>Let's say we have a sample size of N. The margin of error for our sample size is 3.</p>

How many more samples would we need to decrease the margin of error to 0.3?

Let's say we have a sample size of N. The margin of error for our sample size is 3.

How many more samples would we need to decrease the margin of error to 0.3?

<p>Let’s say you’re a Product Data Scientist at Instagram. How would you measure the success of the Instagram TV product?</p>


<p>What is the primary goal of Instagram TV as a product feature?</p>

What is the primary goal of Instagram TV as a product feature?

<p>Given a table of <code>subscriptions</code>, write a query to get the retention rate of each monthly cohort for each <code>plan_id</code> for the three months after sign-up.</p>

<p>Order your output by <code>start_month</code>, <code>plan_id</code>, then <code>num_month</code>.</p>

<p>If an <code>end_date</code> is in the same month as <code>start_date</code> we say the subscription was <strong>not retained</strong> in the first month.</p>

<p>If the <code>end_date</code> occurs in the month after the month of <code>start_date</code>, the subscription was not retained in the second month.  And so on for the third.</p>

<p>The <code>end_date</code> field is <code>NULL</code> if the user has not canceled.</p>

<h3>Schema:</h3>

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

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

<tr>
<td><code>num_month</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>plan_id</code></td>
<td>VARCHAR</td>
</tr>

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

<h3><strong>Example</strong></h3>

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

<p><code>subscriptions</code> table</p>

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

<tbody>
<tr>
<td>1</td>
<td>2020-01-10 00:00:00</td>
<td>2020-03-31 23:59:59</td>
<td>plan1</td>
</tr>

<tr>
<td>2</td>
<td>2020-01-10 00:00:00</td>
<td>2020-02-01 23:59:59</td>
<td>plan1</td>
</tr>

<tr>
<td>3</td>
<td>2020-01-10 00:00:00</td>
<td>2020-07-31 23:59:59</td>
<td>plan1</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>start_month</th>
<th>num_month</th>
<th>plan_id</th>
<th>retained</th>
</tr>
</thead>

<tbody>
<tr>
<td>2020-01-01</td>
<td>1</td>
<td>plan1</td>
<td>1.0</td>
</tr>

<tr>
<td>2020-01-01</td>
<td>2</td>
<td>plan1</td>
<td>0.67</td>
</tr>

<tr>
<td>2020-01-01</td>
<td>3</td>
<td>plan1</td>
<td>0.33</td>
</tr>
</tbody>
</table>
<p><strong>Explanation:</strong></p>

<ul>
<li><strong>Cohort:</strong> All subscriptions starting in January 2020.</li>
<li><strong>Month 1 retention:</strong> All 3 users were active in the first month → <sup>3</sup>⁄<sub>3</sub> = 1.0</li>
<li><strong>Month 2 retention:</strong> Only users 1 and 3 were active in Feb → <sup>2</sup>⁄<sub>3</sub> ≈ 0.67</li>
<li><strong>Month 3 retention:</strong> Only user 3 was active in Mar → <sup>1</sup>⁄<sub>3</sub> ≈ 0.33</li>
</ul>


Subscription Retention

<p>How would you measure the success of Facebook Groups?</p>


<p>Considering the purpose of Facebook Groups as a platform for connecting users through shared interests or real-life relationships, which metrics would be appropriate to measure the success of these groups?</p>


Considering the purpose of Facebook Groups as a platform for connecting users through shared interests or real-life relationships, which metrics would be appropriate to measure the success of these groups?

<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>A digital health startup is analyzing ECG sensor data captured from wearable heart monitors. Each row in the dataset represents one heartbeat, including its timestamp and calculated heart rate at that moment.</p>

<p>The clinical team wants to detect tachycardia runs, periods where a patient’s heart rate stays above 100 bpm continuously for at least 10 seconds .</p>

<p>Write a SQL query to identify every valid tachycardia run and return:</p>

<ol>
<li><code>patient_id</code></li>
<li><code>run_start</code>: timestamp of the first beat in the run</li>
<li><code>run_end</code>: timestamp of the last beat in the run</li>
<li><code>duration_seconds</code>: difference in seconds between start and end (rounded to 2 decimals)</li>
</ol>

<p>Sort results by <code>patient_id</code>, then <code>run_start</code>.</p>

<p><strong>Definition of a Tachycardia Run</strong></p>

<ul>
<li>All consecutive heartbeats must have <code>heart_rate &gt; 100</code></li>
<li>Total duration ≥ 10 seconds</li>
<li>Any gap ≥ 3 seconds between consecutive beats breaks the run</li>
</ul>

<p><strong><em>Note:</em></strong> <em>A patient may have multiple separate runs.</em></p>

<p><strong>Schema</strong></p>

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

<p><strong><code>ecg_readings</code> table</strong></p>

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

<tbody>
<tr>
<td>patient_id</td>
<td>INTEGER</td>
</tr>

<tr>
<td>timestamp</td>
<td>DATETIME</td>
</tr>

<tr>
<td>heart_rate</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>patient_id</td>
<td>INTEGER</td>
</tr>

<tr>
<td>run_start</td>
<td>DATETIME</td>
</tr>

<tr>
<td>run_end</td>
<td>DATETIME</td>
</tr>

<tr>
<td>duration_seconds</td>
<td>DECIMAL</td>
</tr>
</tbody>
</table>
<p><strong>Example</strong></p>

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

<p><strong><code>ecg_readings</code> table</strong></p>

<table>
<thead>
<tr>
<th>patient_id</th>
<th>timestamp</th>
<th>heart_rate</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>2025-03-01 10:00:00</td>
<td>98</td>
</tr>

<tr>
<td>1</td>
<td>2025-03-01 10:00:02</td>
<td>105</td>
</tr>

<tr>
<td>1</td>
<td>2025-03-01 10:00:04</td>
<td>110</td>
</tr>

<tr>
<td>1</td>
<td>2025-03-01 10:00:07</td>
<td>108</td>
</tr>

<tr>
<td>1</td>
<td>2025-03-01 10:00:12</td>
<td>107</td>
</tr>

<tr>
<td>1</td>
<td>2025-03-01 10:00:15</td>
<td>85</td>
</tr>

<tr>
<td>1</td>
<td>2025-03-01 10:00:20</td>
<td>120</td>
</tr>

<tr>
<td>1</td>
<td>2025-03-01 10:00:28</td>
<td>122</td>
</tr>
</tbody>
</table>
<p><strong>Output</strong></p>

<table>
<thead>
<tr>
<th>patient_id</th>
<th>run_start</th>
<th>run_end</th>
<th>duration_seconds</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>2025-03-01 10:00:02</td>
<td>2025-03-01 10:00:12</td>
<td>10.00</td>
</tr>
</tbody>
</table>

<h3><strong>Explanation</strong></h3>

<p>The high-rate streak from 10:00:02 to 10:00:12 forms one continuous sequence because each gap between timestamps (2 to 4, 4 to 7, and 7 to 12) is less than three seconds. This creates a valid 10-second run. The later high values at seconds 20 and 28 are separated by an eight-second gap, so they form a different sequence that is shorter than 10 seconds and is therefore ignored.</p>


Identify tachycardia runs where heart rate stays above 100 bpm for at least 10 seconds and return run start, end, and duration

Detecting ECG Tachycardia Runs

<p>A biomedical research lab is studying treatment effectiveness across different brain cancer categories. Each patient is associated with one cancer type and may undergo zero or more treatments. Write a query to perform an aggregated analysis across all cancer categories.</p>

<p>For each cancer type, compute:</p>

<ol>
<li>Total number of patients</li>
<li>Percentage of patients who survived ≥ 12 months

<ul>
<li>Round to 2 decimal places</li>
<li>A “1-year survivor” is defined as <code>survival_months &gt;= 12</code></li>
</ul></li>
<li>Average number of treatments per patient

<ul>
<li>Round to 2 decimal places</li>
<li>Include patients with zero treatments</li>
</ul></li>
</ol>

<p>Sort the results in descending order by survival rate.</p>

<h3><strong>Example 1</strong></h3>

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

<p><strong>Table <code>patients</code></strong></p>

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

<tbody>
<tr>
<td>patient_id</td>
<td>INTEGER</td>
</tr>

<tr>
<td>cancer_type</td>
<td>VARCHAR</td>
</tr>

<tr>
<td>survival_months</td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Table <code>treatments</code></strong></p>

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

<tbody>
<tr>
<td>treatment_id</td>
<td>INTEGER</td>
</tr>

<tr>
<td>patient_id</td>
<td>INTEGER</td>
</tr>

<tr>
<td>treatment_name</td>
<td>VARCHAR</td>
</tr>

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

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

<tbody>
<tr>
<td>cancer_type</td>
<td>VARCHAR</td>
</tr>

<tr>
<td>total_patients</td>
<td>INTEGER</td>
</tr>

<tr>
<td>pct_one_year_survivors</td>
<td>DECIMAL</td>
</tr>

<tr>
<td>avg_treatments_per_patient</td>
<td>DECIMAL</td>
</tr>
</tbody>
</table>

<h3><strong>Example 2</strong></h3>

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

<p><strong><code>patients</code> table</strong></p>

<table>
<thead>
<tr>
<th>patient_id</th>
<th>cancer_type</th>
<th>survival_months</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>Glioblastoma</td>
<td>10</td>
</tr>

<tr>
<td>2</td>
<td>Meningioma</td>
<td>24</td>
</tr>

<tr>
<td>3</td>
<td>Astrocytoma</td>
<td>15</td>
</tr>

<tr>
<td>4</td>
<td>Glioblastoma</td>
<td>14</td>
</tr>

<tr>
<td>5</td>
<td>Meningioma</td>
<td>7</td>
</tr>
</tbody>
</table>
<p><strong><code>treatments</code> table</strong></p>

<table>
<thead>
<tr>
<th>treatment_id</th>
<th>patient_id</th>
<th>treatment_name</th>
<th>treatment_date</th>
</tr>
</thead>

<tbody>
<tr>
<td>201</td>
<td>1</td>
<td>Radiation</td>
<td>2024-03-01</td>
</tr>

<tr>
<td>202</td>
<td>1</td>
<td>Chemotherapy</td>
<td>2024-06-01</td>
</tr>

<tr>
<td>203</td>
<td>2</td>
<td>Surgery</td>
<td>2024-02-01</td>
</tr>

<tr>
<td>204</td>
<td>4</td>
<td>Chemotherapy</td>
<td>2024-08-01</td>
</tr>
</tbody>
</table>

<h3><strong>Output:</strong></h3>

<table>
<thead>
<tr>
<th>cancer_type</th>
<th>total_patients</th>
<th>pct_one_year_survivors</th>
<th>avg_treatments_per_patient</th>
</tr>
</thead>

<tbody>
<tr>
<td>Astrocytoma</td>
<td>1</td>
<td>100.00</td>
<td>0.00</td>
</tr>

<tr>
<td>Meningioma</td>
<td>2</td>
<td>50.00</td>
<td>0.50</td>
</tr>

<tr>
<td>Glioblastoma</td>
<td>2</td>
<td>50.00</td>
<td>1.00</td>
</tr>
</tbody>
</table>

<h3><strong>Explanation:</strong></h3>

<p><strong>Astrocytoma</strong></p>

<ul>
<li>Total patients: 1</li>
<li>Survivors: <sup>1</sup>⁄<sub>1</sub> → 100.00%</li>
<li>Treatments: 0 total → 0.00 per patient</li>
</ul>

<p><strong>Meningioma</strong></p>

<ul>
<li>Total patients: 2</li>
<li>Survivors: patient 2 (24 months) → 50.00%</li>
<li>Treatments: 1 total (patient 2) → 0.50 per patient</li>
</ul>

<p><strong>Glioblastoma</strong></p>

<ul>
<li>Total patients: 2</li>
<li>Survivors: patient 4 (14 months) → 50.00%</li>
<li>Treatments: 3 total (2 for patient 1, 1 for patient 4) → 1.00 per patient</li>
</ul>


For each cancer type, compute total patients, percentage surviving at least 12 months, and average treatments per patient

Brain Cancer Treatment Outcomes

<p>Given that $X$ and $Y$ are independent random variables with normal distributions, what is the mean and variance of the distribution of $2X - Y$ when the corresponding distributions are $X\sim \mathcal{N}(3, 4)$ and $Y \sim \mathcal{N}(1, 4)$?</p>


<p>Given that $X$ and $Y$ are independent random variables with normal distributions, and the corresponding distributions are $X\sim \mathcal{N}(3, 4)$ and $Y \sim \mathcal{N}(1, 4)$, what is the mean of the distribution of $2X - Y$?</p>

Given that $X$ and $Y$ are independent random variables with normal distributions, and the corresponding distributions are $X\sim \mathcal{N}(3, 4)$ and $Y \sim \mathcal{N}(1, 4)$, what is the mean of the distribution of $2X - Y$?

Distribution of 2X - Y I

<p>Given that $X$ and $Y$ are independent random variables with normal distributions, and the corresponding distributions are $X\sim \mathcal{N}(3, 4)$ and $Y \sim \mathcal{N}(1, 4)$, what is the variance of the distribution of $2X - Y$?</p>

Given that $X$ and $Y$ are independent random variables with normal distributions, and the corresponding distributions are $X\sim \mathcal{N}(3, 4)$ and $Y \sim \mathcal{N}(1, 4)$, what is the variance of the distribution of $2X - Y$?

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

Novant Health Interview Questions

Novant Health Interview Guides

Novant Health Interview Questions

Challenge

Novant Health Salaries by Position

Discussion & Interview Experiences

Discussion & Interview Experiences