Prepare for and practice interview questions from Loram Maintenance Of Way, Inc..

Loram Maintenance Of Way, Inc. Interview Questions

Loram Maintenance Of Way, Inc. Interview Guides

Machine Learning

How would you encode a categorical variable with thousands of distinct values

Encoding Categorical Features

Interpret coefficients of logistic regression for categorical and boolean variables

Coefficients of Logistic Regression

How would we build a model to detect fraud and text customers to approve or deny fraudulent transactions

Bank Fraud Model

How would we know if we have enough data to create an accurate enough model?

One Million Rides

Rebalancing outcome probabilities for a classifier on imbalanced data.

Rebalance Probabilities

Data Structures & Algorithms

Given two sorted lists, write a function to merge them into one sorted list.

Merge Sorted Lists

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

Sum to N

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

Prime to N

Write a function `sorting` from scratch to sort a list of strings in ascending alphabetical order

Sort Strings

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

Probability

What is the probability that each subsequent card is larger than the previous drawn card when picking three cards from a shuffled deck of 500 cards

500 Cards

First to Six

What is the probability that it's actually raining in Seattle?

Raining in Seattle

What is the probability that item X would be found on Amazon's website?

Found Item

Given three uniform(0,4) random variables, what is the probability that the median of them is greater than 3?

Median Probability

Product Sense & Metrics

What would you investigate to diagnose what's happening?

WAU vs Open Rates

Calculate the formula for the average lifetime value

Revenue Retention

How would you measure the success of the Instagram TV product

Instagram TV Success

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

Expected Churn

Google Maps Improvement

A/B Testing

How would you assess the validity of the result?

Experiment Validity

How do we go about selecting the best 10,000 customers for the pre-launch?

Pre-Launching Shows

Button AB Test

A/B Test Power Size

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

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Given two sorted lists, write a function to merge them into one sorted list.

Bonus: What’s the time complexity?

Example:

Input:

<pre tabindex="0" class="chroma"><code>list1 = [1,2,5]
list2 = [2,4,6]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def merge_list(list1,list2) -&gt; [1,2,2,4,5,6]
</code></pre>

You have two sorted lists, one with m elements and one with n elements. What would be the time complexity of merging the two lists via merge sort?

You have two sorted lists, one with m elements and one with n elements. What would be the time complexity of merging the two lists via merge sort? 

Merging Lists Complexity

Given a <code>employees</code> and <code>departments</code> table, select the top 3 departments with at least ten employees and rank them according to the percentage of their employees making over 100K in salary.

Example:

Input:

<code>employees</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>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>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>
</tbody>
</table>
Output:

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

<tbody>
<tr>
<td><code>percentage_over_100k</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>department_name</code></td>
<td>VARCHAR</td>
</tr>

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

Write a query to select the top 3 departments with at least ten employees and rank them according to the percentage of their employees making over 100K in salary.

Employee Salaries

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?

A team wants to A/B test multiple different changes through a sign-up funnel.

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.

How would you set up this test?

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.

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

Amy and Brad take turns in rolling a fair six-sided die. Whoever rolls a “6” first wins the game. Amy starts by rolling first.

What’s the probability that Amy wins?

Amy and Brad take turns in rolling a fair six-sided die. Whoever rolls a “6” first wins the game. Amy starts by rolling first.

What’s the probability that Amy wins?

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

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

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

Example:

Input:

<code>accounts</code> table

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

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

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

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

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

<tr>
<td><code>download_date</code></td>
<td>DATETIME</td>
</tr>

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

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

<tbody>
<tr>
<td><code>download_date</code></td>
<td>DATETIME</td>
</tr>

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

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

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

Download Facts

Given a table called <code>user_experiences</code>, write a query to determine the percentage of users that held the title of <code>&#34;Data Analyst&#34;</code> immediately before holding the title <code>&#34;Data Scientist&#34;</code>.

Immediate is defined as the user holding no other titles between the <code>&#34;Data Analyst&#34;</code> and <code>&#34;Data Scientist&#34;</code> roles.

Example:

Input:

<code>user_experiences</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>position_name</code></td>
<td>VARCHAR</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>user_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
Output:

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

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

User Experience Percentage

Given the tables <code>users</code> and <code>rides</code>, write a query to report the distance traveled by each user in descending order.

Example:

Input:

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

<tr>
<td><code>distance</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
Output:

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

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

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

Distance Traveled

Imagine a deck of 500 cards numbered from 1 to 500. If all the cards are shuffled randomly and you are asked to pick three cards, one at a time, what’s the probability of each subsequent card being larger than the previous drawn card?

Imagine a deck of 2000 cards numbered from 1 to 2000. If all the cards are shuffled randomly and you are asked to pick three cards, one at a time, what’s the probability of each subsequent card being larger than the previously drawn card?

Imagine a deck of 2000 cards numbered from 1 to 2000. If all the cards are shuffled randomly and you are asked to pick three cards, one at a time, what's the probability of each subsequent card being larger than the previously drawn card?

2000 cards

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?

Describing a data project and its challenges

Hurdles In Data Projects

Behavioral

Let’s say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service in addition that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

How would we build this model?

Let’s say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

Which statement is true?

Let's say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

Which statement is true?

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

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

What would you investigate to diagnose what’s happening?

Email Open Rate

Given an integer <code>N</code>, write a function that returns a list of all of the prime numbers up to <code>N</code>.

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

Example:

Input:

<pre tabindex="0" class="chroma"><code>N = 3
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def prime_numbers(N) -&gt; [2,3]
</code></pre>

Let’s say you want to test the close friends feature on Instagram Stories.

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

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

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

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?

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

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

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

Network Experiment Design III

Let’s say we’re comparing two machine learning algorithms. In which case would you use a bagging algorithm versus a boosting algorithm? 

Give an example of the tradeoffs between the two.

In machine learning, when would you use a bagging algorithm over a boosting algorithm?

In machine learning, when would you use a bagging algorithm over a boosting algorithm?

Bagging vs. Boosting

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

Bagging vs Boosting

Let’s say you work at a file-hosting website. You have information on user’s daily downloads in the <code>download_facts</code> table

Use the window function <code>RANK</code> to display the top three users by downloads each day. Order your data by <code>date</code>, and then by <code>daily_rank</code>

Example:

Input:

<code>download_facts</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>date</code></td>
<td>DATE</td>
</tr>

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

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

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

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

<tr>
<td><code>date</code></td>
<td>DATE</td>
</tr>

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

Write a query to display the top three users by downloads each day

Top 3 Users

You are about to get on a plane to Seattle. You want to know if you should bring an umbrella. You call 3 random friends of yours who live there and ask each independently if it’s raining. Each of your friends has a 2⁄3 chance of telling you the truth and a 1⁄3 chance of messing with you by lying. All 3 friends tell you that “Yes” it is raining.

What is the probability that it’s actually raining in Seattle?

What is the main difference between the Frequentist and Bayesian methods of probabilistic analysis?

What is the **main** difference between the Frequentist and Bayesian methods of probabilistic analysis?

Frequentist Vs Bayesian Analysis

Given the <code>transactions</code> table below, write a query that finds the third purchase of every user.

Note: Sort the results by the <code>user_id</code> in ascending order. If a user purchases two products at the same time, the lower <code>id</code> field is used to determine which is the first purchase.

Example:

Input:

<code>transactions</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>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>
Output:

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

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

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

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

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

Write a query to find the third purchase of every user sorted by user_id ascending

Third Purchase

Let’s say that you work for a software as a subscription (SAAS) company that has existed for just over a year. The chief revenue officer wants to know the average lifetime value.

We know that the product costs $100 dollars per month, averages 10% in monthly churn, and the average customer sticks around for around 3.5 months.

Calculate the formula for the average lifetime value.

If a software-as-a-subscription company has a monthly churn rate of 20% for a product that costs $100, what would be the average lifetime value of a single customer?

If a software-as-a-subscription company has a monthly churn rate of 20% for a product that costs $100, what would be the average lifetime value of a single customer?

Average Lifetime Value

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

Let’s say we have a very naive advertising platform. There is an audience of size A and a limited amount of impressions B.

Each of the impressions goes to just one user, defined at random. Every user in the audience has the same random chance of receiving each impression. 

<ol>
<li>Compute the probability that a user has exactly 0 impressions. </li>

<li>What’s the probability that every person has at least 1 impression?</li>
</ol>

Compute the probability a user gets exactly 0 impressions, and the probability everyone gets at least 1 impression

Impression Reach

Write a Python function called <code>max_profit</code> that takes a list of integers, where the i-th integer represents the price of a given stock on day i, and returns the maximum profit you can achieve by buying and selling the stock.

You may complete, at most, two complete buy/sell transactions to maximize profits on a stock.

Input:

<pre tabindex="0" class="chroma"><code>prices = [3, 3, 5, 0, 0, 3, 1, 4]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def max_profit(prices) -&gt; 6
</code></pre>

Explanation:

<pre><code>Buy on day 4 (price = 0) and sell on day 6 (price = 3): profit = 3 - 0 = 3.
Then buy again on day 7 (price = 1) and sell on day 8 (price = 4): profit = 4 - 1 = 3.
Total profit: 3 + 3 = 6.
</code></pre>

Find the maximum profit from at most two stock buy/sell transactions

Maximum Profit

Netflix has hired people to rate movies.

Out of all of the raters, 80% of the raters carefully rate movies and rate 60% of the movies as good and 40% as bad. The other 20% are lazy raters and rate 100% of the movies as good.

Assuming all raters rate the same amount of movies, what is the probability that a movie is rated good?

There are two categories of movie raters: careful and lazy. Careful raters make up 80% of the total. They rate 60% of the movies as good and 40% as bad. Lazy raters, constitute 20% of the raters and 100% of the movies as good. Given these parameters, calculate the overall probability of a movie being rated good.

There are two categories of movie raters: careful and lazy. Careful raters make up 80% of the total. They rate 60% of the movies as good and 40% as bad. Lazy raters, constitute 20% of the raters and 100% of the movies as good. Given these parameters, calculate the overall probability of a movie being rated good.

Lazy Raters

What is the probability that a movie is rated good

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 have a categorical variable with thousands of distinct values, how would you encode it?

Which method can be used to extract communities from large networks, that does not require a pre-determined number of clusters like K-means?

Which method can be used to extract communities from large networks, that does not require a pre-determined number of clusters like K-means?

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

Example: 

Input:

<pre tabindex="0" class="chroma"><code>integers = [2,3,5]
N = 8
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def sum_to_n(integers, N) -&gt;
 [
 [2,2,2,2],
 [2,3,3],
 [3,5]
]
</code></pre>

Lime’s consumer app team wants to measure the number of users who return to the app after their initial login. Given a user_logins table that records each user’s login by date, write a query to produce a daily summary with retention and engagement metrics.

For each date that appears in the user_logins table, calculate daily retention metrics including cohort size, 7-day retention, retention rate, and rolling 7-day active users. (A cohort for a given date consists of all users who logged in on that specific date (regardless of whether it was their first login or a subsequent login).

Notes:

<ul>
<li>A user is considered retained if they log in again within the next 7 days after their cohort date (days 2-8), excluding the cohort day itself.</li>
<li>The rolling 7-day users includes all distinct users who logged in during the 7-day window ending on the current date (from login_date - 6 days to login_date, inclusive).</li>
<li>Each (user_id, login_date) pair is unique - users can only log in once per day.
The output should be ordered by login_date ascending.</li>
<li>Retention rate should be calculated as: (retained_within_7d / cohort_users) * 100, rounded to 1 decimal place.</li>
</ul>

<h3>Schema:</h3>

Input:

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

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

<tbody>
<tr>
<td><code>login_date</code></td>
<td>DATE</td>
</tr>

<tr>
<td><code>cohort_users</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>retained_within_7d</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>retention_rate</code></td>
<td>DECIMAL(3, 2)</td>
</tr>

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

<h3>Example:</h3>

Input:

<table>
<thead>
<tr>
<th>user_id</th>
<th>login_date</th>
</tr>
</thead>

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

<tr>
<td>2</td>
<td>2024-06-01</td>
</tr>

<tr>
<td>1</td>
<td>2024-06-02</td>
</tr>

<tr>
<td>2</td>
<td>2024-06-08</td>
</tr>

<tr>
<td>3</td>
<td>2024-06-08</td>
</tr>

<tr>
<td>1</td>
<td>2024-06-09</td>
</tr>

<tr>
<td>3</td>
<td>2024-06-10</td>
</tr>
</tbody>
</table>
Output:

<table>
<thead>
<tr>
<th>login_date</th>
<th>cohort_users</th>
<th>retained_within_7d</th>
<th>retention_rate</th>
<th>rolling_7d_users</th>
</tr>
</thead>

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

<tr>
<td>2024-06-02</td>
<td>1</td>
<td>1</td>
<td>100.0</td>
<td>2</td>
</tr>

<tr>
<td>2024-06-08</td>
<td>2</td>
<td>1</td>
<td>50.0</td>
<td>2</td>
</tr>

<tr>
<td>2024-06-09</td>
<td>1</td>
<td>0</td>
<td>0.0</td>
<td>2</td>
</tr>

<tr>
<td>2024-06-10</td>
<td>1</td>
<td>0</td>
<td>0.0</td>
<td>2</td>
</tr>
</tbody>
</table>
Explanation:

Retention is calculated for users who return within 7 days after their first login on a given day. Rolling 7-day user counts unique users over the current and previous 6 days.*

Suppose the current <code>login_date</code> is October 10, 2027.

<ul>
<li>The cohort for this date includes all users who logged in on 2027-10-10.</li>
<li>A user is considered retained if they log in again between 2027-10-11 and 2027-10-17 (inclusive).</li>
<li>The cohort day itself (2027-10-10) is excluded from this retention window.</li>
</ul>

How would you calculate daily user retention and rolling activity for Lime's app using login data?

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

Loram Maintenance Of Way, Inc. Interview Questions

Loram Maintenance Of Way, Inc. Interview Guides

Loram Maintenance Of Way, Inc. Interview Questions

Challenge

Discussion & Interview Experiences

Discussion & Interview Experiences