Prepare for and practice interview questions from Sopra Steria.

Sopra Steria Interview Questions

Sopra Steria Interview Guides

Machine Learning

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

Encoding Categorical Features

Which model would perform better and why?

Booking Regression

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

Missing Housing Data

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

Data Structures & Algorithms

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

Merge Sorted Lists

Find the missing integer from a array of consequtive integers

Find the Missing Number

Write a function n_frequent_words that returns the top N frequent words and their frequencies, and state its run-time

Get Top N Frequent Words

Write a function to return the names and resume IDs for resumes whose IDs have not already been scraped

New Resumes

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

Prime to N

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

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

First Touch Attribution

Write a query to return the two students with the closest test scores and the score difference

Closest SAT Scores

Write a query to get the total three-day rolling average for deposits by day

Rolling Bank Transactions

Probability

First to Six

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

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

Data Modeling

Design a data warehouse for a new online retailer

Retailer Data Warehouse

Design a database schema for a Tinder-style dating app and discuss needed optimizations

Swiping App Design

System design for a digital classroom service.

Digital Classroom System Design

Use of foreign key constraints instead of BIGINTS

Foreign Key Constraints

Design a database for a stand-alone fast food restaurant

Fast Food Database

<p>Given a string, write a function to determine if it is palindrome or not.</p>

<p><em>Note: A palindrome is a word/string that is read the same way forward as it is backward, e.g. <code>&#39;reviver&#39;</code>, <code>&#39;madam&#39;</code>, <code>&#39;deified&#39;</code> and <code>&#39;civic&#39;</code> are all palindromes, while <code>&#39;tree&#39;</code>, <code>&#39;music&#39;</code> and <code>&#39;person&#39;</code> are not palindromes.</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">word1</span> <span class="o">=</span> <span class="s2">&#34;tree&#34;</span>
</span></span><span class="line"><span class="cl"><span class="n">word2</span> <span class="o">=</span> <span class="s2">&#34;radar&#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">is_palindrome</span><span class="p">(</span><span class="n">word1</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">False</span>
</span></span><span class="line"><span class="cl"><span class="k">def</span> <span class="nf">is_palindrome</span><span class="p">(</span><span class="n">word2</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">True</span>
</span></span></code></pre>


Given a string, write a function to determine if it is palindrome or not.

String Palindromes

<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 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>We’re given a table of bank transactions with three columns, <code>user_id</code>, a deposit or withdrawal value (determined if the value is positive or negative), and <code>created_at</code> time for each transaction.</p>

<p>Write a query to get the total three-day rolling average for deposits by day.</p>

<p><em>Note: Please use the format <code>&#39;%Y-%m-%d&#39;</code> for the date in the outout</em></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>user_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>dt</code></td>
<td>VARCHAR</td>
</tr>

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


<p>Given a table of students and their SAT test scores, write a query to return the two students with the closest test scores with the score difference.</p>

<p>If there are multiple students with the same minimum score difference, select the student name combination that is higher in the alphabet. </p>

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

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

<p><code>scores</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>student</code></td>
<td>VARCHAR</td>
</tr>

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

<tr>
<td><code>other_student</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>score_diff</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>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 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 that your company is running a standard control and variant AB test on a feature to increase conversion rates on the landing page. The PM checks the results and finds a .04 p-value.</p>

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


How would you assess the validity of the result?

Experiment Validity

A/B Testing

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

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

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

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

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

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

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


<p>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’re comparing two machine learning algorithms. In which case would you use a bagging algorithm versus a boosting algorithm? </p>

<p>Give an example of the tradeoffs between the two.</p>


<p>In machine learning, when would you use a bagging algorithm over a boosting algorithm?</p>


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

Bagging vs. Boosting

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

Bagging vs Boosting

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

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

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

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

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

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

<p>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 <sup>2</sup>⁄<sub>3</sub> chance of telling you the truth and a <sup>1</sup>⁄<sub>3</sub> chance of messing with you by lying. All 3 friends tell you that “Yes” it is raining.</p>

<p>What is the probability that it’s actually raining in Seattle?</p>


<p>What is the <strong>main</strong> difference between the Frequentist and Bayesian methods of probabilistic analysis?</p>


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

Frequentist Vs Bayesian Analysis

<p>Tell me about a data project that didn’t go the way you expected. What did you set out to do, what surprised you, and how did you handle it?</p>


Describing a data project and its challenges

Hurdles In Data Projects

Behavioral

<p>Let’s say you run a wine house. You have detailed information about the chemical composition of wines in a <code>wines</code> table.</p>

<p>One day, a customer comes asking specifically for a wine that has</p>

<ul>
<li>Greater or equal to than 13% alcohol content</li>
<li>Ash content less than 2.4</li>
<li>A color intensity less than 3</li>
</ul>

<p>Write a query to find the <code>id</code>s of suitable wines for this customer.</p>

<p><em>Note:</em> All percentages are reported with two numbers before the decimal point; for example, 13.55% is represented as <code>13.55</code> instead of <code>0.1355</code>.</p>

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

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

<p><code>wines</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>alcohol</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>malic_acid</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>ash</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>alcalinity_of_ash</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>magnesium</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>total_phenols</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>flavanoids</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>nonflavanoid_phenols</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>proanthocyanins</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>color_intensity</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>hue</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>od280_or_od315_of_diluted_wines</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>proline</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>id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>


Let’s say you run a wine house. You have detailed information about the chemical composition of wines in a wines table.

SELECTive Wine Connoisseur

<p>You have an array of integers, <code>nums</code> of length <code>n</code> spanning <code>0</code> to <code>n</code> with one missing. Write a function <code>missing_number</code> that returns the missing number in the array.</p>

<p><em>Note: Complexity of \(O(n)\) required.</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">nums</span> <span class="o">=</span> <span class="p">[</span><span class="mi">0</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">4</span><span class="p">,</span><span class="mi">5</span><span class="p">]</span> 
</span></span><span class="line"><span class="cl"><span class="n">missing_number</span><span class="p">(</span><span class="n">nums</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mi">3</span>
</span></span></code></pre>


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

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

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


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


<p>What does a p-value in a statistical test represent?</p>

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

P-value to a Layman I

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

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

P-value to a Layman II

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

P-value to a Layman

Statistics

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

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

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

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

<p><em>Note: Please return only one query with each number in a different row</em></p>

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

<p><strong>Input:</strong></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>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

<p>Over budget on a project is defined when the salaries, prorated to the day, exceed the budget of the project.</p>

<p>For example, if Alice and Bob both combined income make 200K and work on a project of a budget of 50K that takes half a year, then the project is over budget given 0.5 * 200K = 100K &gt; 50K.</p>

<p>Write a query to forecast the budget for all projects and return a label of <code>&#34;overbudget&#34;</code> if it is over budget and <code>&#34;within budget&#34;</code> otherwise.</p>

<p><em>Note: Assume that employees only work on one project at a time.</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>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><strong>departments</strong> 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><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>project_forecast</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>


Write a query to forecast each project's budget and label it overbudget or within budget

Over-Budget Projects

<p>Let’s say you’re working in HR at a major tech company, and they ask you to find employees with a high probability of leaving the company. They tell you these employees perform well but have the lowest pay.</p>

<p>Given two tables, <code>employees</code>  and <code>projects</code>, find the five lowest-paid employees who have completed at least three projects.</p>

<p>Note: We consider projects to be completed when they have an end date, which is the same as saying their <code>End_dt</code> is not <code>NULL</code>.</p>

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

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

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

<table>
<thead>
<tr>
<th>id</th>
<th>salary</th>
</tr>
</thead>

<tbody>
<tr>
<td>INTEGER</td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<p><code>projects</code> table</p>

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

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

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

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


Find the five employees with the hightest probability of leaving the company

Top 5 Turnover Risk

<p>Let’s say you have a categorical variable with thousands of distinct values, how would you encode it?</p>


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

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

<p>Write a query to identify the manager with the biggest team size.</p>

<p>You may assume there is only one manager with the largest team size.</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>name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>manager_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>managers</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>

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

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


Manager Team Sizes

<p>Given a table of <code>transactions</code> and <code>products</code>, write a function to get the <code>month_over_month</code> change in revenue for the year 2019. Make sure to round <code>month_over_month</code> to 2 decimal places.</p>

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

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

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

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

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


Write a function to get the month_over_month change in revenue for 2019 rounded to 2 decimal places

Month Over Month

<p>Write a Python function called <strong><code>max_profit</code></strong> 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.</p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">prices</span> <span class="o">=</span> <span class="p">[</span><span class="mi">3</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">5</span><span class="p">,</span> <span class="mi">0</span><span class="p">,</span> <span class="mi">0</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">4</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">max_profit</span><span class="p">(</span><span class="n">prices</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="mi">6</span>
</span></span></code></pre>

<p>Explanation:</p>

<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

<p>Let’s say we have two tables, <code>transactions</code> and <code>products</code>. Hypothetically the <code>transactions</code> table consists of over a billion rows of purchases bought by users.</p>

<p>We are trying to find paired products that are often purchased together by the same user, such as wine and bottle openers, chips and beer, etc..</p>

<p>Write a query to find the top five paired products and their names.</p>

<p><em>Notes:</em>
   <em>For the purposes of satisfying the test case, p2 should be the item that comes first in the alphabet.</em>
   <em>The <code>qty</code> column represents paired products count</em></p>

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

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

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

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

<tr>
<td><code>p2</code></td>
<td>VARCHAR</td>
</tr>

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


Paired Products

<p>There are two tables. One table is called <code>swipes</code> that holds a row for every Tinder swipe and contains a boolean column that determines if the swipe was a right or left swipe called <code>is_right_swipe</code>. The second is a table named <code>variants</code> that determines which user has which variant of an AB test.</p>

<p>Write a SQL query to output the average number of right swipes for two different variants of a feed ranking algorithm by comparing users that have swiped 10, 50, and 100 swipes in a <code>feed_change</code> experiment.</p>

<p><em>Note: Users have to have swiped at least 10 times to be included in the subset of users to analyze the mean number of right swipes.</em></p>

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

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

<p><code>variants</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>experiment</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>variant</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>user_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>swipes</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>user_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>swiped_user_id</code></td>
<td>INTEGER</td>
</tr>

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

<tr>
<td><code>is_right_swipe</code></td>
<td>BOOLEAN</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

<tr>
<td><code>mean_right_swipes</code></td>
<td>FLOAT</td>
</tr>

<tr>
<td><code>swipe_threshold</code></td>
<td>INTEGER</td>
</tr>

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


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

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

Sopra Steria Interview Questions

Sopra Steria Interview Guides

Sopra Steria Interview Questions

Challenge

Sopra Steria Salaries by Position

Discussion & Interview Experiences

Discussion & Interview Experiences