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

Janssen Interview Questions

Janssen 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

What kind of model did the co-worker develop, how would you measure the difference between the two credit risk models within a timeframe, and what metrics would you track to measure the success of the new model

Loan Model

Which model do you pick given 85% and 82% accuracy

85% vs 82%

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

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

Monthly Customer Report

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

Prioritized debt reduction, process improvement, and a focus on maintainability for fintech efficiency

Decreasing Tech Debt

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

Presentations and Insights

How would you negotiate and resolve disagreements when a client rejects your proposed solution?

Client Solution Pushback

Product Sense & Metrics

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

How would you measure the success of Linkedin’s newsfeed ranking algorithm and approach conflicting success metrics

Ranking Metrics

Pitching a feature idea that could drive success for Instagram Stories

Boosting Instagram Stories

Delivering an exceptional customer experience by focusing on key customer-centric parameters

Uber Eats Customer Experience

Design a dashboard that provides personalized insights, sales forecasts, and inventory recommendations for shop owners based on their transaction history, seasonal trends, and customer behavior.

Merchant Dashboard Design

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

Example:

Input:

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

<tr>
<td><code>price</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<code>users</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>

<tr>
<td><code>sex</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
Output:

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

Let’s say you work at Shopify. Your team is building a dashboard for merchants that provides personalized insights on sales trends, inventory status (e.g., overstocked or low-stock items), and customer behavior (e.g., new vs. returning customers) based on transaction history and seasonal trends. The goal is to help shop owners make better day-to-day decisions through clear and actionable data.

What key metrics and visualizations would you include in the dashboard, and how would you ensure they remain useful across different types of merchants?

How would you optimize a deep learning model to improve its performance without overfitting?

How would you optimize a deep learning model to improve performance without overfitting

Optimize Model Performance

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>

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.

How would you assess the validity of the result?

How would you assess the validity of the result?

Experiment Validity

A/B Testing

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>

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?

Given a dictionary with weights, write a function <code>random_key</code> that returns a key at random with a probability proportional to the weights.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>weights = {&#39;A&#39;: 1, &#39;B&#39;: 2}
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>random_key(weights) -&gt; return A 1/3 of the time, B 2/3 of the time
</code></pre>

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>weights = {&#39;A&#39;: 1, &#39;B&#39;: 1}
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>random_key(weights) -&gt; return A 1/2 of the time, B 1/2 of the time
</code></pre>

Weighted Keys

Probability

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

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

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

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

P-value to a Layman

Statistics

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

How would you design the system?

Note: Sketch a star schema to explain your design.

Design a data warehouse for a new online retailer

Retailer Data Warehouse

Data Modeling

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

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

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?

Reducing Error Margin

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

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>

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.

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 .

Write a SQL query to identify every valid tachycardia run and return:

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

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

Definition of a Tachycardia Run

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

Note: A patient may have multiple separate runs.

Schema

Input

<code>ecg_readings</code> table

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

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

Input

<code>ecg_readings</code> table

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

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

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.

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

Let’s say you work at Allstate. Allstate is running <code>N</code> online ads right now. The table <code>ads</code> contains all those ads, ranked by popularity via the <code>id</code> column (e.g., the entry with <code>id = 1</code> is the most popular, etc.).

Create a subquery or common table expression named <code>top_ads</code> containing the top 3 ads (by popularity) and return the number of rows that would result from the following operations

<ol>
<li><code>ads INNER JOIN top_ads</code></li>
<li><code>ads LEFT JOIN top_ads</code></li>
<li><code>ads RIGHT JOIN top_ads</code></li>
<li><code>ads CROSS JOIN top_ads</code></li>
</ol>

Note: Please make the <code>join_type</code> column in your output have the values <code>inner_join</code>, <code>left_join</code>, etc. for each of their respective join types

Note: Please return only one query with each number in a different row

Example:

Input:

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

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

Create top_ads with the top 3 ads and return the row counts for inner, left, right, and cross joins with ads

Size of Joins

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 that you work for an E-commerce store. A new marketing manager joins the company and redesigns the existing new-user email journey to try and boost customer conversion rates. A few weeks after launching the new email journey, the new-user to customer conversion rate goes up from 40% to 43%.

Before you can celebrate, the company’s CMO points out a troubling fact. Just a few months prior to the new manager starting the conversion rate already stood at 45%, before slowly dropping down to the “starting” 40%.

How would you investigate if the redesigned email campaign actually led to the increase in the conversion rate, and that the increase wasn’t instead the result of other factors?

What would be an appropriate method to investigate whether the redesigned email campaign led to an increase in the conversion rate of an E-commerce store, in the absence of infrastructure for an AB test?

What would be an appropriate method to investigate whether the redesigned email campaign led to an increase in the conversion rate of an E-commerce store, in the absence of infrastructure for an AB test?

Causal Email Journey

How would you find out if an increase in user conversion rates after a new email journey is casual or just part of a wider trend?

Causal Email Journey 

Analytics

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

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

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

Random Forest Explanation

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

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

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

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.

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>

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

Janssen Interview Questions

Janssen Interview Guides

Janssen Interview Questions

Challenge

Discussion & Interview Experiences

Discussion & Interview Experiences