Prepare for and practice interview questions from Publicis Groupe.

Publicis Groupe Interview Questions

Publicis Groupe Interview Guides

Data Structures & Algorithms

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

Merge Sorted Lists

Find Bigrams

Write a function to create a single dataframe with complete addresses in the format of street, city, state, zip code.

Complete Addresses

Write a function friendship_timeline to generate an output that lists the pairs of friends with their corresponding timestamps of the friendship beginning and then the timestamp of the friendship ending. 

Friendship Timeline

String Shift

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

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Write a SQL query to create a histogram of the number of comments per user in the month of January 2020.

Comments Histogram

Write a query that returns all neighborhoods that have 0 users.

Empty Neighborhoods

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

First Touch Attribution

Product Sense & Metrics

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

Decreasing Comments

Promoting Instagram

Threaded Comments

Group Success

WAU vs Open Rates

Machine Learning

Coefficients of Logistic Regression

Booking Regression

Job Recommendation

RMS Error

Bank Fraud Model

Probability

First to Six

500 Cards

Raining in Seattle

Found Item

Ad Raters

<p>We’re given two tables, a <code>users</code> table with demographic information and the neighborhood they live in and a <code>neighborhoods</code> table.</p>

<p>Write a query that returns all neighborhoods that have 0 users. </p>

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

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

<p><code>users</code> table</p>

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

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

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

<tr>
<td><code>neighborhood_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><code>neighborhoods</code> table</p>

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

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

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

<tr>
<td><code>city_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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


<p>Write a SQL query to select the 2nd highest salary in the engineering department.</p>

<p><strong>Note:</strong> If more than one person shares the highest salary, the query should select the next highest salary.</p>

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

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

<p><code>employees</code> table</p>

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

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

<tr>
<td><code>first_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>last_name</code></td>
<td>VARCHAR</td>
</tr>

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

<tr>
<td><code>department_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>departments</code> table</p>

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

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

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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


<p>Given two sorted lists, write a function to merge them into one sorted list.</p>

<p><em>Bonus: What’s the time complexity?</em></p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">list1</span> <span class="o">=</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">5</span><span class="p">]</span>
</span></span><span class="line"><span class="cl"><span class="n">list2</span> <span class="o">=</span> <span class="p">[</span><span class="mi">2</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">6</span><span class="p">]</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">merge_list</span><span class="p">(</span><span class="n">list1</span><span class="p">,</span><span class="n">list2</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">2</span><span class="p">,</span><span class="mi">4</span><span class="p">,</span><span class="mi">5</span><span class="p">,</span><span class="mi">6</span><span class="p">]</span>
</span></span></code></pre>


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


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

Merging Lists Complexity

<p>Write a SQL query to create a histogram of the number of comments per user in the month of January 2020.</p>

<p><em>Note: Assume bin buckets class intervals of one.</em></p>

<p><em>Note: Comments that were created outside of January 2020 should be counted in a “0” bucket</em></p>

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

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

<p><code>users</code> table</p>

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

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

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

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

<tr>
<td><code>neighborhood_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>mail</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><code>comments</code> table</p>

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

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

<tr>
<td><code>body</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>created_at</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


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

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

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

<p><code>employees</code> table</p>

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

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

<tr>
<td><code>first_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>last_name</code></td>
<td>VARCHAR</td>
</tr>

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

<tr>
<td><code>department_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>departments</code> table</p>

<table>
<thead>
<tr>
<th>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>
<p><strong>Output:</strong></p>

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


<p>Given the <code>employees</code> and <code>departments</code> table, write a query to get the top 3 highest employee salaries by department. If the department contains less that 3 employees, the top 2 or the top 1 highest salaries should be listed (assume that each department has at least 1 employee). </p>

<p><em>Note: The output should include the full name of the employee in one column, the department name, and the salary. The output should be sorted by department name in ascending order and salary in descending order.</em> </p>

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

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

<p><code>employees</code> table</p>

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

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

<tr>
<td><code>first_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>last_name</code></td>
<td>VARCHAR</td>
</tr>

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

<tr>
<td><code>department_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>departments</code> table</p>

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

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

<tr>
<td><code>name</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Output</strong>:</p>

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

<tbody>
<tr>
<td><code>employee_nam</code>e</td>
<td>VARCHAR</td>
</tr>

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

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


Get the top 3 highest employee salaries by department

Top Three Salaries

<p>Given a table of bank transactions with columns <code>id</code>, <code>transaction_value</code>, and <code>created_at</code> representing the date and time for each transaction, write a query to get the last transaction for each day.</p>

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

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

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

<p><code>bank_transactions</code> table</p>

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

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

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

<tr>
<td><code>transaction_value</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

<tr>
<td><code>transaction_value</code></td>
<td>FLOAT</td>
</tr>

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


Last Transaction

<p>Let’s say that your company is running a standard control and variant AB test on a feature to increase conversion rates on the landing page. The PM checks the results and finds a .04 p-value.</p>

<p>How would you assess the validity of the result?</p>


Experiment Validity

A/B Testing

<p>The schema below is for a retail online shopping company consisting of two tables, <code>attribution</code> and <code>user_sessions</code>.</p>

<ul>
<li><p>The attribution table logs a session visit for each row.</p></li>

<li><p>If <code>conversion</code> is <code>true</code>, then the user converted to buying on that session.</p></li>

<li><p>The <code>channel</code> column represents which advertising platform the user was attributed to for that specific session.</p></li>

<li><p>Lastly the <code>user_sessions</code> table maps many to one session visits back to one user.</p></li>
</ul>

<p>First touch attribution is defined as the channel with which the converted user was associated when they first discovered the website.</p>

<p>Calculate the first touch attribution for each <code>user_id</code> that converted. </p>

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

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

<p><code>attribution</code> table</p>

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

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

<tr>
<td><code>channel</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>conversion</code></td>
<td>BOOLEAN</td>
</tr>
</tbody>
</table>
<p><code>user_sessions</code> table</p>

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

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

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

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Example output:</strong></p>

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

<tbody>
<tr>
<td>123</td>
<td>facebook</td>
</tr>

<tr>
<td>145</td>
<td>google</td>
</tr>

<tr>
<td>153</td>
<td>facebook</td>
</tr>

<tr>
<td>172</td>
<td>organic</td>
</tr>

<tr>
<td>173</td>
<td>email</td>
</tr>
</tbody>
</table>


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

<p>What’s the probability that Amy wins?</p>


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

What’s the probability that Amy wins?

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

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

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

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

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

<p><code>friends</code> table</p>

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

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

<tr>
<td><code>friend_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>page_likes</code> table</p>

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

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

<tr>
<td><code>page_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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

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


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

Liked Pages

<p>Given a table of user sessions, write a query to get the number of days between each user’s first session and last session for the year 2020. </p>

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

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

<p><code>user_sessions</code> table</p>

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

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

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

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


Find the difference between users' first and last sessions in the year

Session Difference

<p>Let’s say we have a table with an <code>id</code> and <code>name</code> fields. The table holds over 100 million rows and we want to sample a random row in the table without throttling the database.</p>

<p>Write a query to randomly sample a row from this table.</p>

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

<p><code>big_table</code> table</p>

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

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

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


Write a query to randomly sample a row from a big table.

Random SQL Sample

<p>Netflix has hired people to rate movies.</p>

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

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


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

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

<p>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?</p>


<p>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?</p>


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

<p>The <code>events</code> table tracks every time a user performs a certain action (like, post_enter, etc.) on a platform. An example of this table would look like this:</p>

<table>
<thead>
<tr>
<th>user_id</th>
<th>created_at</th>
<th>action</th>
<th>platform</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>2021-01-01 12:00:00</td>
<td>email_opened</td>
<td>Android</td>
</tr>

<tr>
<td>2</td>
<td>2021-01-01 12:05:00</td>
<td>like</td>
<td>Outlook</td>
</tr>

<tr>
<td>3</td>
<td>2021-01-01 12:10:00</td>
<td>post_enter</td>
<td>Gmail</td>
</tr>

<tr>
<td>4</td>
<td>2021-01-01 12:15:00</td>
<td>post_cancel</td>
<td>Outlook</td>
</tr>

<tr>
<td>5</td>
<td>2021-01-01 12:20:00</td>
<td>like</td>
<td>Gmail</td>
</tr>
</tbody>
</table>
<p>What query would you write to determine how many different users gave a like on June 6, 2020?</p>

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

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

<p><code>events</code> table</p>

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

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

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

<tr>
<td>action</td>
<td>VARCHAR</td>
</tr>

<tr>
<td>platform</td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Output</strong></p>

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

<tbody>
<tr>
<td>num_users_gave_like</td>
<td>INTEGER</td>
</tr>
</tbody>
</table>


Find out the number of users that gave a like on a certain date

Like Tracker

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

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

<h3>Schema</h3>

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

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

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

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

<tr>
<td><code>destination_location</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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

<h3>Example</h3>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Flight Records

<p>Given a table called <code>employees</code>, get the largest salary of any employee by department</p>

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

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

<p><code>employees</code> table</p>

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

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

<tr>
<td><code>department</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>salary</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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


Write a query to get the largest salary of any employee by department

Largest Salary by Department

<p>Let’s say you have a table called <code>events</code> that keeps track of every user’s actions. A small example of the <code>events</code> table would look like this:</p>

<table>
<thead>
<tr>
<th>user_id</th>
<th>created_at</th>
<th>action</th>
<th>platform</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>2021-01-01 12:00:00</td>
<td>email_opened</td>
<td>Android</td>
</tr>

<tr>
<td>2</td>
<td>2021-01-01 12:05:00</td>
<td>like</td>
<td>Outlook</td>
</tr>

<tr>
<td>3</td>
<td>2021-01-01 12:10:00</td>
<td>post_enter</td>
<td>Gmail</td>
</tr>

<tr>
<td>4</td>
<td>2021-01-01 12:15:00</td>
<td>post_cancel</td>
<td>Outlook</td>
</tr>

<tr>
<td>5</td>
<td>2021-01-01 12:20:00</td>
<td>like</td>
<td>Gmail</td>
</tr>
</tbody>
</table>
<p>Using this information, you wish to find out how many users have opened an email.</p>

<p>What query would you use to find out?</p>

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

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

<p><code>events</code> table</p>

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

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

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

<tr>
<td>action</td>
<td>VARCHAR</td>
</tr>

<tr>
<td>platform</td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

<tbody>
<tr>
<td>num_users_open_email</td>
<td>INTEGER</td>
</tr>
</tbody>
</table>


Emails Opened

<p>Write a function called <code>find_bigrams</code> that takes a sentence or paragraph of strings and returns a list of all its bigrams in order.</p>

<p><strong>Note:</strong> A <em>bigram</em> is a pair of consecutive words.</p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">sentence</span> <span class="o">=</span> <span class="s2">&#34;&#34;&#34;
</span></span></span><span class="line"><span class="cl"><span class="s2">Have free hours and love children? 
</span></span></span><span class="line"><span class="cl"><span class="s2">Drive kids to school, soccer practice 
</span></span></span><span class="line"><span class="cl"><span class="s2">and other activities.
</span></span></span><span class="line"><span class="cl"><span class="s2">&#34;&#34;&#34;</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">find_bigrams</span><span class="p">(</span><span class="n">sentence</span><span class="p">)</span> <span class="o">-&gt;</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"> <span class="p">[(</span><span class="s1">&#39;have&#39;</span><span class="p">,</span> <span class="s1">&#39;free&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;free&#39;</span><span class="p">,</span> <span class="s1">&#39;hours&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;hours&#39;</span><span class="p">,</span> <span class="s1">&#39;and&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;and&#39;</span><span class="p">,</span> <span class="s1">&#39;love&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;love&#39;</span><span class="p">,</span> <span class="s1">&#39;children?&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;children?&#39;</span><span class="p">,</span> <span class="s1">&#39;drive&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;drive&#39;</span><span class="p">,</span> <span class="s1">&#39;kids&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;kids&#39;</span><span class="p">,</span> <span class="s1">&#39;to&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;to&#39;</span><span class="p">,</span> <span class="s1">&#39;school,&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;school,&#39;</span><span class="p">,</span> <span class="s1">&#39;soccer&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;soccer&#39;</span><span class="p">,</span> <span class="s1">&#39;practice&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;practice&#39;</span><span class="p">,</span> <span class="s1">&#39;and&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;and&#39;</span><span class="p">,</span> <span class="s1">&#39;other&#39;</span><span class="p">),</span>
</span></span><span class="line"><span class="cl"> <span class="p">(</span><span class="s1">&#39;other&#39;</span><span class="p">,</span> <span class="s1">&#39;activities.&#39;</span><span class="p">)]</span>
</span></span></code></pre>


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

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


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

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

Network Experiment Design I

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

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

Network Experiment Design II

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

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

Network Experiment Design III

Write a SQL query to find the average number of right swipes for different ranking algorithms.

Swipe Precision

<p>We’re given two tables. One is named <code>projects</code> and the other maps employees to the projects they’re working on. </p>

<p>We want to select the five most expensive projects by budget to employee count ratio. But let’s say that we’ve found a bug where there exist duplicate rows in the <code>employee_projects</code> table.</p>

<p>Write a query to account for the error and select the top five most expensive projects by budget to employee count ratio.</p>

<h3>Schema</h3>

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

<p><code>projects</code> table</p>

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

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

<tr>
<td><code>title</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>state_date</code></td>
<td>DATETIME</td>
</tr>

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

<tr>
<td><code>budget</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>employee_projects</code> table</p>

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

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

<tr>
<td><code>employee_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

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

<h3>Example</h3>

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

<p><strong><code>projects</code> table</strong></p>

<table>
<thead>
<tr>
<th>id</th>
<th>title</th>
<th>start_date</th>
<th>end_date</th>
<th>budget</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>party</td>
<td>2006-04-17 00:00:00</td>
<td>2006-05-19 00:00:00</td>
<td>25875</td>
</tr>

<tr>
<td>2</td>
<td>diversity</td>
<td>2017-08-09 00:00:00</td>
<td>2017-09-18 00:00:00</td>
<td>77867</td>
</tr>

<tr>
<td>3</td>
<td>integration</td>
<td>2005-06-29 00:00:00</td>
<td>2005-07-19 00:00:00</td>
<td>75987</td>
</tr>

<tr>
<td>4</td>
<td>testing</td>
<td>2009-01-12 00:00:00</td>
<td>2009-05-03 00:00:00</td>
<td>35946</td>
</tr>

<tr>
<td>5</td>
<td>launch</td>
<td>2005-05-02 00:00:00</td>
<td>2005-10-16 00:00:00</td>
<td>66292</td>
</tr>

<tr>
<td>6</td>
<td>meet</td>
<td>2001-09-09 00:00:00</td>
<td>2002-04-04 00:00:00</td>
<td>71243</td>
</tr>

<tr>
<td>7</td>
<td>payroll</td>
<td>2019-09-25 00:00:00</td>
<td>2020-05-05 00:00:00</td>
<td>97071</td>
</tr>

<tr>
<td>8</td>
<td>admin</td>
<td>2000-07-08 00:00:00</td>
<td>2000-09-10 00:00:00</td>
<td>24000</td>
</tr>

<tr>
<td>9</td>
<td>petronas</td>
<td>2016-07-01 00:00:00</td>
<td>2016-11-28 00:00:00</td>
<td>37088</td>
</tr>

<tr>
<td>10</td>
<td>bel</td>
<td>2005-08-03 00:00:00</td>
<td>2006-04-29 00:00:00</td>
<td>61937</td>
</tr>
</tbody>
</table>
<p><strong><code>employee_projects</code> table</strong></p>

<table>
<thead>
<tr>
<th>project_id</th>
<th>employee_id</th>
</tr>
</thead>

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

<tr>
<td>1</td>
<td>9</td>
</tr>

<tr>
<td>2</td>
<td>6</td>
</tr>

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

<tr>
<td>3</td>
<td>2</td>
</tr>

<tr>
<td>4</td>
<td>5</td>
</tr>

<tr>
<td>5</td>
<td>3</td>
</tr>

<tr>
<td>5</td>
<td>4</td>
</tr>

<tr>
<td>6</td>
<td>8</td>
</tr>

<tr>
<td>8</td>
<td>10</td>
</tr>

<tr>
<td>9</td>
<td>11</td>
</tr>

<tr>
<td>10</td>
<td>12</td>
</tr>

<tr>
<td>5</td>
<td>5</td>
</tr>

<tr>
<td>9</td>
<td>11</td>
</tr>

<tr>
<td>6</td>
<td>8</td>
</tr>

<tr>
<td>4</td>
<td>5</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>title</th>
<th>budget_per_employee</th>
</tr>
</thead>

<tbody>
<tr>
<td>diversity</td>
<td>77867</td>
</tr>

<tr>
<td>meet</td>
<td>71243</td>
</tr>

<tr>
<td>bel</td>
<td>61937</td>
</tr>

<tr>
<td>integration</td>
<td>37993.5</td>
</tr>

<tr>
<td>petronas</td>
<td>37088</td>
</tr>
</tbody>
</table>


Project Budget Error

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

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

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

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


Impression Reach

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<tr>
<td>heart_rate</td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output</strong></p>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Write a SQL query to identify every valid tachycardia run.

Detecting ECG Tachycardia Runs

<p>Given two strings <code>A</code> and <code>B</code>, write a function <code>can_shift</code> to return whether or not <code>A</code> can be shifted some number of places to get <code>B</code>.</p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">A</span> <span class="o">=</span> <span class="s1">&#39;abcde&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">B</span> <span class="o">=</span> <span class="s1">&#39;cdeab&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">can_shift</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">B</span><span class="p">)</span> <span class="o">==</span> <span class="kc">True</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">A</span> <span class="o">=</span> <span class="s1">&#39;abc&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">B</span> <span class="o">=</span> <span class="s1">&#39;acb&#39;</span>
</span></span><span class="line"><span class="cl"><span class="n">can_shift</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">B</span><span class="p">)</span> <span class="o">==</span> <span class="kc">False</span>
</span></span></code></pre>


<p>Let’s say you work for a social media company that has just done a launch in a new city. Looking at weekly metrics, you see a slow decrease in the average number of comments per user from January to March in this city.</p>

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

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


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

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

<p>There are two lists of dictionaries representing friendship beginnings and endings: <code>friends_added</code> and <code>friends_removed</code>. Each dictionary contains the <code>user_ids</code> and <code>created_at</code> time of the friendship beginning /ending.</p>

<p>Write a function <code>friendship_timeline</code> to generate an output that lists the pairs of friends with their corresponding timestamps of the friendship beginning and then the timestamp of the friendship ending.</p>

<p><em>Note: There can be multiple instances over time when two people became friends and unfriended; only output lists when a corresponding friendship was removed.</em></p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">friends_added</span> <span class="o">=</span> <span class="p">[</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span><span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="s1">&#39;created_at&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-01&#39;</span><span class="p">},</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span><span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">3</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="s1">&#39;created_at&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-02&#39;</span><span class="p">},</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span><span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">1</span><span class="p">],</span> <span class="s1">&#39;created_at&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-02-02&#39;</span><span class="p">},</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span><span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">4</span><span class="p">,</span> <span class="mi">1</span><span class="p">],</span> <span class="s1">&#39;created_at&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-02-02&#39;</span><span class="p">}]</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">friends_removed</span> <span class="o">=</span> <span class="p">[</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span><span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">1</span><span class="p">],</span> <span class="s1">&#39;created_at&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-03&#39;</span><span class="p">},</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span><span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">],</span> <span class="s1">&#39;created_at&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-05&#39;</span><span class="p">},</span>
</span></span><span class="line"><span class="cl">    <span class="p">{</span><span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span> <span class="s1">&#39;created_at&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-02-05&#39;</span><span class="p">}]</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">friendships</span> <span class="o">=</span> <span class="p">[{</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;start_date&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-01&#39;</span><span class="p">,</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;end_date&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-03&#39;</span>
</span></span><span class="line"><span class="cl">  <span class="p">},</span>
</span></span><span class="line"><span class="cl">  <span class="p">{</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">],</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;start_date&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-02-02&#39;</span><span class="p">,</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;end_date&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-02-05&#39;</span>
</span></span><span class="line"><span class="cl">  <span class="p">},</span>
</span></span><span class="line"><span class="cl">  <span class="p">{</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;user_ids&#39;</span><span class="p">:</span> <span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">3</span><span class="p">],</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;start_date&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-02&#39;</span><span class="p">,</span>
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;end_date&#39;</span><span class="p">:</span> <span class="s1">&#39;2020-01-05&#39;</span>
</span></span><span class="line"><span class="cl">  <span class="p">},</span>
</span></span><span class="line"><span class="cl"><span class="p">]</span>
</span></span></code></pre>


<p>You’re given a dataframe of students named <code>students_df</code>:</p>

<p><code>students_df</code> table</p>

<table>
<thead>
<tr>
<th><strong>name</strong></th>
<th><strong>age</strong></th>
<th><strong>favorite_color</strong></th>
<th><strong>grade</strong></th>
</tr>
</thead>

<tbody>
<tr>
<td>Tim Voss</td>
<td>19</td>
<td>red</td>
<td>91</td>
</tr>

<tr>
<td>Nicole Johnson</td>
<td>20</td>
<td>yellow</td>
<td>95</td>
</tr>

<tr>
<td>Elsa Williams</td>
<td>21</td>
<td>green</td>
<td>82</td>
</tr>

<tr>
<td>John James</td>
<td>20</td>
<td>blue</td>
<td>75</td>
</tr>

<tr>
<td>Catherine Jones</td>
<td>23</td>
<td>green</td>
<td>93</td>
</tr>
</tbody>
</table>
<p>Write a function named <code>grades_colors</code> to select only the rows where the student’s favorite color is green or red and their grade is above 90.</p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="kn">import</span> <span class="nn">pandas</span> <span class="k">as</span> <span class="nn">pd</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">students</span> <span class="o">=</span> <span class="p">{</span><span class="s2">&#34;name&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="s2">&#34;Tim Voss&#34;</span><span class="p">,</span> <span class="s2">&#34;Nicole Johnson&#34;</span><span class="p">,</span> <span class="s2">&#34;Elsa Williams&#34;</span><span class="p">,</span> <span class="s2">&#34;John James&#34;</span><span class="p">,</span> <span class="s2">&#34;Catherine Jones&#34;</span><span class="p">],</span> <span class="s2">&#34;age&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="mi">19</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="mi">21</span><span class="p">,</span> <span class="mi">20</span><span class="p">,</span> <span class="mi">23</span><span class="p">],</span> <span class="s2">&#34;favorite_color&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="s2">&#34;red&#34;</span><span class="p">,</span> <span class="s2">&#34;yellow&#34;</span><span class="p">,</span> <span class="s2">&#34;green&#34;</span><span class="p">,</span> <span class="s2">&#34;blue&#34;</span><span class="p">,</span> <span class="s2">&#34;green&#34;</span><span class="p">],</span> <span class="s2">&#34;grade&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="mi">91</span><span class="p">,</span> <span class="mi">95</span><span class="p">,</span> <span class="mi">82</span><span class="p">,</span> <span class="mi">75</span><span class="p">,</span> <span class="mi">93</span><span class="p">]}</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">students_df</span> <span class="o">=</span> <span class="n">pd</span><span class="o">.</span><span class="n">DataFrame</span><span class="p">(</span><span class="n">students</span><span class="p">)</span>
</span></span></code></pre>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">grades_colors</span><span class="p">(</span><span class="n">students_df</span><span class="p">)</span> <span class="o">-&gt;</span>
</span></span></code></pre>

<table>
<thead>
<tr>
<th><strong>name</strong></th>
<th><strong>age</strong></th>
<th><strong>favorite_color</strong></th>
<th><strong>grade</strong></th>
</tr>
</thead>

<tbody>
<tr>
<td>Tim Voss</td>
<td>19</td>
<td>red</td>
<td>91</td>
</tr>

<tr>
<td>Catherine Jones</td>
<td>23</td>
<td>green</td>
<td>93</td>
</tr>
</tbody>
</table>


Write a function to select only the rows where the student's favorite color is green or red and their grade is above 90.

Good Grades and Favorite Colors

<p>You are a data scientist at YouTube focused on creators.</p>

<p>A PM comes to you worried that amateur video creators could do well before but now it seems like only “superstars” do well.</p>

<p>What data points and metrics would you look at to decide if this is true or not?</p>


Amateur Performance

Challenge

<div><h2>Technical Product Manager For Search And Social Advertising</h2><p>The Technical Product Manager for Search and Social Advertising is a core member of the PMX PAS Product organization. This role bridges search and social media expertise, data/technology understanding, and product execution discipline to advance PAS' cross-channel analytics, reporting, and optimization capabilities.</p><p>You will be responsible for owning and advancing PAS capabilities that support search and social media activation, measurement, and optimization across channels and platforms such as Google Ads, Microsoft Ads, Search Ads 360, Facebook, Instagram, Tik Tok, Snap. Working closely with Engineering, Data Science, Enablement, Marketplace, and Search + Social Activation teams, you will translate real-world trading and measurement needs into scalable, high-quality product solutions.</p><p>This role is ideal for someone who brings hands-on search and social media advertising experience and wants to shape enterprise-level advertising products used by agencies and clients at scale.</p><p>The ideal candidate is a hybrid between part product manager, part ad-tech SME, part data-literate problem solver.</p><h2>Product Strategy And Roadmap</h2><ul><li>Contribute to PAS roadmap across search and social media measurement (SEM, Youtube, social media and more), benchmarks, quality/brand safety insights, marketplace integrations, and manual workflow automations.</li><li>Assess needs from agency activation teams, PMX Partnerships, and Analytics to prioritize features based on impact, feasibility, and user value.</li><li>Support long-term platform evolution including marketplace integrations, improved data quality, and automated reporting expansions.</li></ul><h2>Requirement Definition, Technical Execution, And Backlog Ownership</h2><ul><li>Act as day-to-day Product Owner and SME (Subject Matter Experts) for one or more PAS development pods for search and social media advertising services and deliverables.</li><li>Translate search and social media marketplace mechanics and product requirements into clear user stories, functional requirements, acceptance criteria, and technical dependencies.</li><li>Groom to refine and define stories and tasks that can be transformed into agile tickets and tasks/sub-tasks.</li><li>Bridge Product and Engineering on signal ingestion, business logic integration, taxonomy alignment, platform consistency and UX/UI.</li><li>Discover, identify and define QA methodologies, data backfill/reprocessing, rollback, disaster recovery, observability C monitoring</li><li>Discover, identify and prevent/prepare for errors and failures (known and unknown/assumed)</li><li>Work with internal technical, support and resource teams to enable PAS to deliver solutions. Collaborate closely with internal Datalake teams.</li><li>Partner with Engineering on: <ul><li>Data pipeline readiness</li><li>API integrations (search platforms, social media platforms, analytics vendors, marketplace partners)</li><li>Validation and QA for search and social media datasets</li><li>Troubleshooting discrepancies, mapping issues, and freshness problems</li></ul></li><li>Define requirements for multiple reports and features</li><li>Maintain a clean, prioritized backlog and ensure sprint readiness with Engineering.</li></ul><h2>Stakeholder Engagement</h2><ul><li>Work closely with search + social media and PAS Enablement and other internal teams to gather feedback and validate solutions.</li><li>Work with PAS Enablement to support GTM and adoption through training materials, documentation, sample analyses, playbooks, and FAQs.</li><li>Partner with external ad-tech vendors (analytics, measurement partners) as needed.</li></ul><h2>Search And Social Media Advertising Expertise</h2><ul><li>Serve as the internal SME on search and social media advertising mechanics involving platforms/resources such as Google Ads, Microsoft Ads, Search Ads 360, Google Analytics, Facebook, Instagram, Tik Tok, Snap, measurement partners, audience targeting partners.</li><li>Use this expertise to guide product decisions and validate analytical outputs.</li></ul><h2>Qualifications</h2><h1>Required Skills And Experience</h1><ul><li>46+ years in product management, ad tech, marketing technology, search marketing advertising, social media advertising, or a related data-driven advertising role.</li><li>Hands-on knowledge of search and social advertising, data management-engineering, reporting, campaign performance optimization.</li><li>Strong understanding of bidding mechanics, auction logic, and platform-specific bidding processes and rules</li><li>Experience working with engineering teams on data or platform products (APIs, ETL pipelines, cloud technologies).</li><li>Familiarity with tools such as Jira, Confluence, Tableau, Databricks, Snowflake, or similar.</li><li>Excellent communication and stakeholder management skills across technical and non-technical groups.</li><li>Ability to translate business needs into structured product requirements and clear deliverables.</li></ul><h1>Nice-to-Have</h1><ul><li>Experience with programmatic advertising (display and video)</li><li>Experience with data QA, mapping, and troubleshooting within programmatic datasets.</li><li>Understanding of identity, audience targeting, or cookie-less strategies.</li><li>Experience with cross-channel analytics or MMM/MTA inputs.</li></ul><h2>Additional Information</h2><p>Our Publicis Groupe motto "Viva La Diffrence" means we're better together, and we believe that our differences make us stronger. It means we honor and celebrate all identities, across all facets of intersectionality, and it underpins all that we do as an organization. We are focused on fostering belonging and creating equitable & inclusive experiences for all talent.</p><p>Publicis Groupe provides robust and inclusive benefit programs and policies to support the evolving and diverse needs of our talent and enable every person to grow and thrive. Our benefits package includes medical coverage, dental, vision, disability, 401K, as well as parental and family care leave, family forming assistance, tuition reimbursement, and flexible time off.</p><p>If you require accommodation or assistance with the application or onboarding process specifically, please contact USMSTACompliance@publicis.com. All your information will be kept confidential according to EEO guidelines.</p><p>Compensation Range: $88,540.00 - $141,687.00 Annually. This is the pay range the Company believes it will pay for this position at the time of this posting. Consistent with applicable law, compensation will be determined based on the skills, qualifications, and experience of the applicant along with the requirements of the position, and the Company reserves the right to modify this pay range at any time. Temporary roles may be eligible to participate in our freelancer/temporary employee medical plan through a third-party benefits administration system once certain criteria have been met. Temporary roles may also qualify for participation in our 401(k) plan after eligibility criteria have been met. For regular roles, the Company will offer medical coverage, dental, vision, disability, 401k, and paid time off. The Company anticipates the application deadline for this job posting will be 4/25/2026.</p></div>

<div><h2>Technical Product Manager For Search And Social Advertising</h2><p>The Technical Product Manager for Search and Social Advertising is a core member of the PMX PAS Product organization. This role bridges search and social media expertise, data/technology understanding, and product execution discipline to advance PAS' cross-channel analytics, reporting, and optimization capabilities.</p><p>You will be responsible for owning and advancing PAS capabilities that support search and social media activation, measurement, and optimization across channels and platforms such as Google Ads, Microsoft Ads, Search Ads 360, Facebook, Instagram, Tik Tok, Snap. Working closely with Engineering, Data Science, Enablement, Marketplace, and Search + Social Activation teams, you will translate real-world trading and measurement needs into scalable, high-quality product solutions.</p><p>This role is ideal for someone who brings hands-on search and social media advertising experience and wants to shape enterprise-level advertising products used by agencies and clients at scale.</p><p>The ideal candidate is a hybrid between part product manager, part ad-tech SME, part data-literate problem solver.</p><h2>Product Strategy Roadmap</h2><ul><li>Contribute to PAS roadmap across search and social media measurement (SEM, YouTube, social media and more), benchmarks, quality/brand safety insights, marketplace integrations, and manual workflow automations.</li><li>Assess needs from agency activation teams, PMX Partnerships, and Analytics to prioritize features based on impact, feasibility, and user value.</li><li>Support long-term platform evolution including marketplace integrations, improved data quality, and automated reporting expansions.</li></ul><h2>Requirement Definition, Technical Execution, and Backlog Ownership</h2><ul><li>Act as day-to-day Product Owner and SME (Subject Matter Experts) for one or more PAS development pods for search and social media advertising services and deliverables.</li><li>Translate search and social media marketplace mechanics and product requirements into clear user stories, functional requirements, acceptance criteria, and technical dependencies.</li><li>Groom to refine and define stories and tasks that can be transformed into agile tickets and tasks/sub-tasks.</li><li>Bridge Product and Engineering on signal ingestion, business logic integration, taxonomy alignment, platform consistency and UX/UI.</li><li>Discover, identify and define QA methodologies, data backfill/reprocessing, rollback, disaster recovery, observability C monitoring</li><li>Discover, identify and prevent/prepare for errors and failures (known and unknown/assumed)</li><li>Work with internal technical, support and resource teams to enable PAS to deliver solutions. Collaborate closely with internal Datalake teams.</li><li>Partner with Engineering on: <ul><li>Data pipeline readiness</li><li>API integrations (search platforms, social media platforms, analytics vendors, marketplace partners)</li><li>Validation and QA for search and social media datasets</li><li>Troubleshooting discrepancies, mapping issues, and freshness problems</li></ul></li><li>Define requirements for multiple reports and features</li><li>Maintain a clean, prioritized backlog and ensure sprint readiness with Engineering.</li></ul><h2>Stakeholder Engagement</h2><ul><li>Work closely with search + social media and PAS Enablement and other internal teams to gather feedback and validate solutions.</li><li>Work with PAS Enablement to support GTM and adoption through training materials, documentation, sample analyses, playbooks, and FAQs.</li><li>Partner with external ad-tech vendors (analytics, measurement partners) as needed.</li></ul><h2>Search and Social Media Advertising Expertise</h2><ul><li>Serve as the internal SME on search and social media advertising mechanics involving platforms/resources such as Google Ads, Microsoft Ads, Search Ads 360, Google Analytics, Facebook, Instagram, Tik Tok, Snap, measurement partners, audience targeting partners.</li><li>Use this expertise to guide product decisions and validate analytical outputs.</li></ul><h2>Qualifications</h2><ul><li>46+ years in product management, ad tech, marketing technology, search marketing advertising, social media advertising, or a related data-driven advertising role.</li><li>Hands-on knowledge of search and social advertising, data management-engineering, reporting, campaign performance optimization.</li><li>Strong understanding of bidding mechanics, auction logic, and platform-specific bidding processes and rules</li><li>Experience working with engineering teams on data or platform products (APIs, ETL pipelines, cloud technologies).</li><li>Familiarity with tools such as Jira, Confluence, Tableau, Databricks, Snowflake, or similar.</li><li>Excellent communication and stakeholder management skills across technical and non-technical groups.</li><li>Ability to translate business needs into structured product requirements and clear deliverables.</li></ul><h2>Nice-to-Have</h2><ul><li>Experience with programmatic advertising (display and video)</li><li>Experience with data QA, mapping, and troubleshooting within programmatic datasets.</li><li>Understanding of identity, audience targeting, or cookie-less strategies.</li><li>Experience with cross-channel analytics or MMM/MTA inputs.</li></ul><h2>Additional Information</h2><p>Our Publicis Groupe motto "Viva La Diffrence" means we're better together, and we believe that our differences make us stronger. It means we honor and celebrate all identities, across all facets of intersectionality, and it underpins all that we do as an organization. We are focused on fostering belonging and creating equitable & inclusive experiences for all talent.</p><p>Publicis Groupe provides robust and inclusive benefit programs and policies to support the evolving and diverse needs of our talent and enable every person to grow and thrive. Our benefits package includes medical coverage, dental, vision, disability, 401K, as well as parental and family care leave, family forming assistance, tuition reimbursement, and flexible time off.</p><p>If you require accommodation or assistance with the application or onboarding process specifically, please contact USMSTACompliance@publicis.com. All your information will be kept confidential according to EEO guidelines.</p><p>Compensation Range: $88,540.00 - $141,687.00 Annually. This is the pay range the Company believes it will pay for this position at the time of this posting. Consistent with applicable law, compensation will be determined based on the skills, qualifications, and experience of the applicant along with the requirements of the position, and the Company reserves the right to modify this pay range at any time. Temporary roles may be eligible to participate in our freelancer/temporary employee medical plan through a third-party benefits administration system once certain criteria have been met. Temporary roles may also qualify for participation in our 401(k) plan after eligibility criteria have been met. For regular roles, the Company will offer medical coverage, dental, vision, disability, 401k, and paid time off. The Company anticipates the application deadline for this job posting will be 4/25/2026.</p></div>

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

Publicis Groupe Interview Questions

Publicis Groupe Interview Guides

Publicis Groupe Interview Questions

Challenge

Publicis Groupe Opening Jobs

Discussion & Interview Experiences

Discussion & Interview Experiences