Prepare for and practice interview questions from Cepheid across topics like Statistics, SQL, Machine Learning and more.

Cepheid Interview Questions

Cepheid Interview Guides

Machine Learning

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

RMS Error

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

Missing Housing Data

What are the logistic and softmax functions? What is the difference between the two?

Softmax vs Logistic

How to model merchant acquisition in a new market?

Merchant Acquisition

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

Random Forest Explanation

Data Structures & Algorithms

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

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

Common Prefix

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

Valid Anagram

Write a function digit_accumulator that returns the sum of every digit in a string

Digit Accumulator

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Last Transaction

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

Find the second longest flight between each pair of cities.

Second Longest Flight

Behavioral

Describing a data project and its challenges

Hurdles In Data Projects

Strategically resolving misaligned expectations with stakeholders for a successful project outcome

Stakeholder Communication

How to present complex data insights with clarity and adaptability tailored to a specific audience

Presentations and Insights

What do you tell an interviewer when they ask you what your strengths and weaknesses are?

Your Strengths and Weaknesses

How would you answer when an Interviewer asks why you applied to their company?

Why Do You Want to Work With Us

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

Explain what an unbiased estimator is and provide a layman-friendly example

Unbiased Estimator

Write a function to calculate the cdf from a list pdf and round numbers to 3 digits

Density to Cumulative

Write a SQL query to select the 2nd highest salary in the engineering department.

Note: If more than one person shares the highest salary, the query should select the next highest salary.

Example:

Input:

<code>employees</code> table

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

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

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

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

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.

Example:

Input:

<code>users</code> table

<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>
<code>comments</code> table

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

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

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.

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

Example:

Input:

<code>bank_transactions</code> table

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

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

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

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

Example:

Input:

<code>ad_impressions</code> table:

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

<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

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.

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

Example:

Input:

<code>users</code> table

<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>
<code>purchases</code> table

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

<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

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.

For each cancer type, compute:

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

Sort the results in descending order by survival rate.

<h3>Example 1</h3>

Input :

Table <code>patients</code>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</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>
Table <code>treatments</code>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</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>
Output :

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</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>Example 2</h3>

Input:

<code>patients</code> table

<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>
<code>treatments</code> table

<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>Output:</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>Explanation:</h3>

Astrocytoma

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

Meningioma

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

Glioblastoma

<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

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

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

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

P-value to a Layman I

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

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

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?

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

Input:

Example 1: 

<pre tabindex="0" class="chroma"><code>y_pred = [3,4,5]
y_true = [3,4,5]

def calculate_rmse(y_pred,y_true) -&gt; 0
</code></pre>

Example 2:

<pre tabindex="0" class="chroma"><code>y_pred = [3,4,5]
y_true = [5,4,3]

def calculate_rmse(y_pred,y_true) -&gt; 1.63
</code></pre>

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?

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?

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?

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. 

Example:

Input:

<code>users</code> table

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

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

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.

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

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

Fair Coin

Probability

You work at a ride-sharing company and are given a table containing information about the rides of users.

Write a query to get the duration (in minutes) of each ride longer than two hours. Sort the results by duration in descending order.

<code>rides</code> table:

<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>passenger_user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>start_dt</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>end_dt</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>distance</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>ride_region</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>is_completed</code>	</td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
Output:

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

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

Write a query to get the duration in minutes of each ride longer than two hours, sorted descending

Time Difference

Given a list of integers, write a function <code>gcd</code> to find the greatest common denominator between them.

Example:

Input:

<pre tabindex="0" class="chroma"><code>int_list = [8, 16, 24]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def gcd(int_list) -&gt; 8
</code></pre>

Which of the following is true about the greatest common division (gcd) between three integers a, b, and c?

Which of the following is true about the greatest common division (gcd) between three integers a, b, and c?

Greatest Common Denominator Properties

Write a function gcd to find the greatest common denominator of a list of integers

Greatest Common Denominator

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.

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

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

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.

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

<h3>Schema:</h3>

Input:

<code>subscriptions</code> table

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

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

Input:

<code>subscriptions</code> table

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

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

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

Subscription Retention

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

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

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

Average Trees

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.

Note: A word is not an anagram of itself.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>string_1 = &#34;listen&#34;
string_2 = &#34;silent&#34;
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>True
</code></pre>

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>string_1 = &#34;banana&#34;
string_2 = &#34;bandana&#34;
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>False
</code></pre>

A group of 100 students are participating in a coin-tossing challenge at a school event. Each student flips a fair coin once:
- If the coin lands on heads, the student wins and truthfully reports their win.
- If the coin lands on tails, the student flips again:
 - If the second flip is heads, the student lies and claims they won, even though they lost.
 - If the second flip is tails, the student truthfully reports their loss.

At the end of the game, 30 students report that they won. As an analyst, you are tasked with determining the expected number of students who actually won the game (i.e., got heads on their first flip).

Example:

Suppose only 8 students report winning. How many students would you expect to have truly won based on the rules above?

Note: You may assume each coin flip is independent and fair (probability of heads = 0.5).

Given win reports in a coin toss game with lying, estimate the true number of winners.

Secret Wins

You are given a list of integers called <code>numbers</code>. Write a function to return any subset of <code>numbers</code> where the elements <code>sum</code> to <code>zero</code> and that does not contain the number <code>0</code>.

If there are no combinations of elements that sum to zero, return an empty list.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>numbers = [1,-2,6,7,1]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def zero_sum(numbers) -&gt; [1,-2,1] # only possible combination
</code></pre>

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>numbers = [0,0,1,3,6,-4,-1]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def zero_sum(numbers) -&gt; [1,3,-4]
</code></pre>

or

<pre tabindex="0" class="chroma"><code>def zero_sum(numbers) -&gt; [1,-1]
</code></pre>

or any other subset that sums to zero and doesn’t include <code>0</code>.

Write a function to return any subset of the input list where the elements sum to zero and that does not contain the number 0.

Sum to Zero

We want to build a model to predict housing prices in the city of Seattle. We’ve scraped 100K sold listings over the past three years but found that around 20% of the listings are missing square footage data.

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

We want to build a model to predict housing prices in the city of Seattle. We’ve scraped 100K sold listings over the past three years, but have discovered that around 20% of the listings are missing square footage data.

How would you approach dealing with this missing data, in order to construct the most useful predictive model possible?

We want to build a model to predict housing prices in the city of Seattle. We’ve scraped 100K sold listings over the past three years, but have discovered that around 20% of the listings are missing square footage data.

How would you approach dealing with this missing data, in order to construct the most useful predictive model possible?



Given a <code>JSON</code> string with nested objects, write a function <code>flatten_json</code> that flattens all the objects to a single key-value dictionary. Do not use the library that actually performs this function.

Example:

Input:

<pre tabindex="0" class="chroma"><code>import json
json_str = json.dumps({&#39;a&#39;:{&#39;b&#39;:&#39;c&#39;, &#39;d&#39;:&#39;e&#39;}})
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def flatten_json(json_str) -&gt; json.dumps({&#39;a_b&#39;:&#39;c&#39;, &#39;a_d&#39;:&#39;e&#39;})
</code></pre>

Note: Input and output are in <code>string</code> format : use <code>json.dumps()</code> to convert python dictionary to string.

Let’s say that you’re working at Netflix.

The company executives are working to renew a deal with another TV network that grants Netflix exclusive licensing to stream their hit TV series (think something like Friends or The Office). One of the executives wants to know how to approach this deal.

We know that the TV show has been on Netflix for a year already. 

How would you approach valuing the benefit of keeping this show on Netflix?

How would you approach valuing the benefit of keeping this show on Netflix

Licensing Valuation

Business Case

You work as a data scientist for ride-sharing company.

An executive asks how you would evaluate whether a 50% rider discount promotion is a good or bad idea?

How would you implement it? What metrics would you track?

You work as a data scientist for ride-sharing company.  An executive asks how you would evaluate whether a 50% rider discount promotion is a good or bad idea?  How would you implement it? What metrics would you track?

Rider Discount

Product Sense & Metrics

Given a table of <code>flights</code>, extract the 2nd flight with the most duration between each pair of cities. Order the flights by the flight <code>id</code> ascending.

Note: For any cities <code>X</code> and <code>Y</code>, <code>(source_location=X, destination_location=Y)</code> and <code>(source_location=Y, destination_location=X)</code> are counted as the same pair of cities.

Note: If there are fewer than two flights between two cities, there is no 2nd longest flight.

Example:

Input:

<code>flights</code> table

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

<tr>
<td><code>source_location</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>plane_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>flight_start</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>flight_end</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
Output:

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

<tr>
<td><code>source_location</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>flight_start</code></td>
<td>DATETIME</td>
</tr>

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

You are given a <code>string</code> that represents some floating-point number. Write a function, <code>digit_accumulator</code>, that returns the sum of every digit in the <code>string</code>.

Example:

Input:

<pre tabindex="0" class="chroma"><code>s = &#34;123.0045&#34;
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def digit_accumulator(s) -&gt; 15

Since 
1 + 2 + 3 + 0 + 0 + 4 + 5 = 15
</code></pre>

Write a function, <code>search_list</code>, that returns a boolean indicating if the <code>target</code> value is in the <code>linked_list</code> or not.

You receive the head of the linked list, which is a dictionary with the following keys:
- <code>value</code>: contains the value of the node.
- <code>next</code>: contains the next node in the list, or <code>None</code>.

If the linked list is empty, you’ll receive <code>None</code> since there is no head node for an empty list.

Example:

Input:

<pre tabindex="0" class="chroma"><code>target = 2
linked_list = 3 -&gt; 2 -&gt; 5 -&gt; 6 -&gt; 8 -&gt; None
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>search_list(target, linked_list) -&gt; True
</code></pre>

Write a function that returns a boolean indicating if a value is in the linked list.

Search Linked List

Given a list of strings, write a function that returns their longest common prefix. For example, if you were given the strings <code>&#34;flowers&#34;</code>, <code>&#34;flow&#34;</code>, and <code>&#34;flight&#34;</code>, your function should return the string <code>&#34;fl&#34;</code>.

If the list of strings has no common prefix, return an empty string.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>strings = [&#34;flowers&#34;, &#34;flow&#34;, &#34;flight&#34;] 
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>&#34;fl&#34;
</code></pre>

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>strings = [&#34;showboat&#34;, &#34;showcase&#34;, &#34;shower&#34;]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>&#34;show&#34;
</code></pre>

How would you handle the data preparation for building a machine learning model using imbalanced data?

Addressing imbalanced data in machine learning through carefully prepared techniques.

Data Preparation for Imbalanced Data

Let’s say we have an <code>annual_payments</code> table. Answer the following three questions via SQL queries and output them as a table with the answers to each question.

<ol>
<li>How many total transactions are in this table?</li>
<li>How many different users made transactions?</li>
<li>How many transactions listed as <code>&#34;paid&#34;</code> have an amount greater or equal to 100?</li>
<li>Which product made the highest revenue? (use only transactions with a <code>&#34;paid&#34;</code> status)</li>
</ol>

Example:

Input:

<code>annual_payments</code> table

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

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

<tr>
<td><code>status</code></td>
<td>VARCHAR</td>
</tr>

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

<tr>
<td><code>amount_refunded</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>product</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>last_updated</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
Output:

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

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

<tr>
<td><code>answer</code></td>
<td>TEXT</td>
</tr>
</tbody>
</table>

Write a SQL query to count transactions filtered by several criterias.

Count Transactions

Let’s say you work as a data architect at Facebook.

Facebook is trying to transition their product from just doing likes on posts and comments to adding the multi-reaction under the like button.

How would you approach this problem? What would the database modifications look like?

How would you approach adding multi-reactions under the like button and what database modifications would you make

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

Cepheid Interview Questions

Cepheid Interview Guides

Cepheid Interview Questions

Challenge

Cepheid Salaries by Position

Discussion & Interview Experiences

Discussion & Interview Experiences