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

Hallmark Interview Questions

Hallmark Interview Guides

Select the 2nd highest salary in the engineering department

2nd Highest Salary

Customer Orders

Average Order Value

Monthly Customer Report

There was a robbery from the ATM at the bank where you work. Some unauthorized withdrawals were made, and you need to help your bank find out more about those withdrawals.

ATM Robbery

Data Structures & Algorithms

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

Common Prefix

Valid Anagram

Find Mismatched Words

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

String Palindromes

Business Case

Friend Requests Down

Google Docs Drop

When would someone rationally buy a more expensive version of an identical product?

Identical Pen Pricing

Machine Learning

Booking Regression

Random Forest Explanation

Let's say that you're training a classification model.   How would you combat overfitting when building tree-based models?

Overfit Avoidance

Behavioral

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

Why Do You Want to Work With Us

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

Your Strengths and Weaknesses

<p>When an interviewer asks a question along the lines of:</p>

<ul>
<li>What would your current manager say about you? What constructive criticisms might he give?</li>
<li>What are your three biggest strengths and weaknesses you have identified in yourself?</li>
</ul>

<p>How would you respond?</p>


<p>When asked about your strengths in an interview, what is an effective way to respond?</p>

When asked about your strengths in an interview, what is an effective way to respond?

Your Strengths and Weaknesses I

<p>Which of the following is an acceptable strategy when discussing weaknesses in an interview?</p>

Which of the following is an acceptable strategy when discussing weaknesses in an interview?

Your Strengths and Weaknesses II

<p>When an interviewer asks you a question along the lines of:</p>

<ul>
<li>Why did you apply to our company?</li>
<li>What are you looking for in your next job?</li>
<li>What makes you a good fit for our company?</li>
</ul>

<p>How should you respond?</p>


<p>When asked 'What are you looking for in your next job?' in an interview, how can you tie the company's employee benefits into your response?</p>

When asked 'What are you looking for in your next job?' in an interview, how can you tie the company's employee benefits into your response?

Why Do You Want to Work With Us I

<p>How can company values be used effectively in an interview when asked 'What makes you a good fit for our company?'</p>

How can company values be used effectively in an interview when asked 'What makes you a good fit for our company?'

Why Do You Want to Work With Us II

<p>When responding to the question 'Why did you apply to our company?' during an interview, what aspect should you highlight?</p>

When responding to the question 'Why did you apply to our company?' during an interview, what aspect should you highlight?

Why Do You Want to Work With Us III

<p>Let’s say that you’re training a classification model.</p>

<p>How would you combat overfitting when building tree-based models?</p>


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


<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>How does random forest generate the forest? Additionally, why would we use it over other algorithms such as logistic regression?</p>


<p>What happens when you average the output on multiple decision trees?</p>


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

Average Trees

<p>Let’s say you’re given all the different marketing channels along with their respective marketing costs at a company called Mode, that sells B2B analytics dashboards.</p>

<p>What metrics would you use to determine the value of each marketing channel?</p>


<p>When determining the value of each marketing channel for the company Mode, which metric is considered the key metric?</p>

When determining the value of each marketing channel for the company Mode, which metric is considered the key metric?

Marketing Channel Metrics

What metrics would you use to determine the value of each marketing channel?

Analytics

<p>Given two strings, write a function to return <code>True</code> if the strings are anagrams of each other and <code>False</code> if they are not.</p>

<p><em>Note: A word is not an anagram of itself.</em></p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">string_1</span> <span class="o">=</span> <span class="s2">&#34;listen&#34;</span>
</span></span><span class="line"><span class="cl"><span class="n">string_2</span> <span class="o">=</span> <span class="s2">&#34;silent&#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="kc">True</span>
</span></span></code></pre>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">string_1</span> <span class="o">=</span> <span class="s2">&#34;banana&#34;</span>
</span></span><span class="line"><span class="cl"><span class="n">string_2</span> <span class="o">=</span> <span class="s2">&#34;bandana&#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="kc">False</span>
</span></span></code></pre>


<p>Let’s say we want to build a model to predict booking prices on Airbnb.</p>

<p>Between linear regression and random forest regression, which model would perform better and why?</p>


<p>What are the two key concepts of random forest regression?</p>

What are the two key concepts of random forest regression?

<p>Let’s say that you work for an online media company. The media company is starting to monetize its web traffic and wants to experiment with adding web banners into the middle of its reading content to see if it can monetize effectively.</p>

<p>How would you measure the success of the banner ad strategy?</p>


How would you measure the success of a banner ad strategy?

Banner Ad Strategy Success

A/B Testing

<p>What is the difference between covariance and correlation? Provide an example.</p>


Covariance vs Correlation

Statistics

<p>A product manager at Facebook comes to you and tells you that friend requests are down 10%.</p>

<p>What would you do?</p>


<p>You work as a data analyst on a social media platform similar to Facebook. If you observe a 10% decline in friend requests over the last three months, which analysis or metric-tracking approach could aid in uncovering the reasons?</p>


You work as a data analyst on a social media platform similar to Facebook. If you observe a 10% decline in friend requests over the last three months, which analysis or metric-tracking approach could aid in uncovering the reasons?

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

<p>If the list of strings has no common prefix, return an empty string.</p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">strings</span> <span class="o">=</span> <span class="p">[</span><span class="s2">&#34;flowers&#34;</span><span class="p">,</span> <span class="s2">&#34;flow&#34;</span><span class="p">,</span> <span class="s2">&#34;flight&#34;</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="s2">&#34;fl&#34;</span>
</span></span></code></pre>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">strings</span> <span class="o">=</span> <span class="p">[</span><span class="s2">&#34;showboat&#34;</span><span class="p">,</span> <span class="s2">&#34;showcase&#34;</span><span class="p">,</span> <span class="s2">&#34;shower&#34;</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="s2">&#34;show&#34;</span>
</span></span></code></pre>


<p>Write a query to show the number of users, number of transactions placed, and total order amount per month in the year 2020. Assume that we are only interested in the monthly reports for a single year (January-December).</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><code>users</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>sex</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>month</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>num_customers</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>num_orders</code></td>
<td>INTEGER</td>
</tr>

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


<p>Let’s say you work at Costco. Costco has a database with two tables. The first is <code>users</code> composed of user information, including their registration date, and the second is <code>purchases</code> that has the entire item purchase history (if any) for those users.</p>

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

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

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

<p><code>users</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>user_name</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>registration_date</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><code>purchases</code> table</p>

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

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

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

<tr>
<td><code>item</code></td>
<td>VARCHAR</td>
</tr>

<tr>
<td><code>price</code></td>
<td>FLOAT</td>
</tr>

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

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

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

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


Total Spent on Products

<p>How would you investigate a 10% drop in usage on Google Docs?</p>


<p>When adding a row to a very large database table, many factors contribute to making that operation especially resource-intensive on the system.</p>

<p>Each of the following options contains multiple relevant factors to consider when deciding on a solution. Identify the option that generally represents the most significant impact on the process.</p>


When adding a row to a very large database table, many factors contribute to making that operation especially resource-intensive on the system.

Each of the following options contains multiple relevant factors to consider when deciding on a solution. Identify the option that generally represents the most significant impact on the process.

Modifying a Billion Rows

<p>A product manager notices that the number of users for Google Docs drops, what strategies would best help determine the possible causes for a decrease in Google Docs usage?</p>


A product manager notices that the number of users for Google Docs drops, what strategies would best help determine the possible causes for a decrease in Google Docs usage? 

<p>You are a data architect consulting for a large retail company, which has experienced slowdowns in generating monthly and quarterly inventory analyses.</p>

<ol>
<li>What might be the reason for the OLAP system’s performance, specifically when handling monthly and quarterly reports?</li>
<li>What aggregation strategy might you look into in order to mitigate this issue?</li>
</ol>

<p>State any assumptions upfront.</p>


Assess and create an aggregation strategy for slow OLAP aggregations.

Slow OLAP Aggregations

Query Optimization

<p>Suppose that you work for McDonald’s. You are to create a dynamic real-time dashboard highlighting the sales of each branch.</p>

<p>How would you design the database and engineering system for this?</p>


Designing a dynamic sales dashboard to track McDonald's branch performance in real-time

Sales Leaderboard

<p>Given three tables, representing customer transactions and customer attributes:</p>

<p>Write a query to get the average order value by gender.</p>

<p><em>Note: We’re looking at the average order value by users that have ever placed an order. Additionally, please round your answer to two decimal places.</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>users</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>sex</code></td>
<td>VARCHAR</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>sex</code></td>
<td>VARCHAR</td>
</tr>

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


<p>Write a query to get the cost of all transactions by user ordered by total cost descending.</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><code>users</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>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>name</code></td>
<td>VARCHAR</td>
</tr>

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

<tr>
<td><code>total_cost</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
<p><em>Note: Round the total cost to 2 decimal places.</em></p>


Total Transactions

<p>As an accountant for a local grocery store, you have been tasked with calculating the daily sales of each product since their last restocking.</p>

<p>You have been provided with three tables: <code>products</code>, <code>sales</code>, and <code>restocking</code>. The <code>products</code> table contains information about each product, the <code>sales</code> table records the sales transactions, and the <code>restocking</code> table tracks the restocking events.</p>

<p>Write a query to retrieve the running total of sales for each product since its last restocking event.</p>

<p><strong><em>Notes:</em></strong> <em>The <code>sales_since_last_restock</code> column represents the cumulative sales of the product for each day since its last restocking event. Order the result set by <code>product_id</code>.</em></p>

<p>Example:</p>

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

<p>Table <code>products</code>:</p>

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

<tbody>
<tr>
<td>product_id</td>
<td>int</td>
</tr>

<tr>
<td>product_name</td>
<td>varchar</td>
</tr>
</tbody>
</table>
<p>Table <code>sales</code>:</p>

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

<tbody>
<tr>
<td>sales_id</td>
<td>int</td>
</tr>

<tr>
<td>product_id</td>
<td>int</td>
</tr>

<tr>
<td>date</td>
<td>date</td>
</tr>

<tr>
<td>sold_count</td>
<td>int</td>
</tr>
</tbody>
</table>
<p>Table <code>restocking</code>:</p>

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

<tbody>
<tr>
<td>restock_id</td>
<td>int</td>
</tr>

<tr>
<td>product_id</td>
<td>int</td>
</tr>

<tr>
<td>restock_date</td>
<td>date</td>
</tr>
</tbody>
</table>
<p><strong>Output</strong></p>

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

<tbody>
<tr>
<td>product_name</td>
<td>varchar</td>
</tr>

<tr>
<td>date</td>
<td>date</td>
</tr>

<tr>
<td>sales_since_last_restock</td>
<td>int</td>
</tr>
</tbody>
</table>


Calculate daily sales of each product since last restocking.

Cumulative Sales Since Last Restocking

<p>Write a query to identify customers who placed more than three transactions each in both 2019 and 2020.</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>users</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>customer_name</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>


<p>Which of the following queries will return a list of customers and the total amount they have spent on orders?</p>


Which of the following queries will return a list of customers and the total amount they have spent on orders?

<p>There was a robbery from the ATM at the bank where you work. Some unauthorized withdrawals were made, and you need to help your bank find out more about those withdrawals.</p>

<p>However, the only information you have is that there was more than 1 withdrawal, they were all performed in 10-second gaps, and no legitimate transactions were performed in between two fraudulent withdrawals.</p>

<p>We’re given a table of bank transactions with three columns, <code>user_id</code>, a deposit or withdrawal value <code>transaction_value</code>, and <code>created_at</code> time for each transaction.</p>

<p>Write a query to retrieve all user IDs in ascending order whose transactions have exactly a 10-second gap from one another.</p>

<p><em>Note: Assume that there are only withdrawals from the ATM, which are denoted with a positive <code>transaction_value</code></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>For given table <code>bank_transactions</code>:</p>

<table>
<thead>
<tr>
<th><code>user_id</code></th>
<th><code>created_at</code></th>
<th><code>transaction_value</code></th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>2017-01-01 10:10:15</td>
<td>10</td>
</tr>

<tr>
<td>2</td>
<td>2017-01-01 11:11:11</td>
<td>20</td>
</tr>

<tr>
<td>3</td>
<td>2017-01-01 12:12:12</td>
<td>10</td>
</tr>

<tr>
<td>4</td>
<td>2017-01-01 10:10:20</td>
<td>30</td>
</tr>

<tr>
<td>5</td>
<td>2017-01-01 10:10:34</td>
<td>40</td>
</tr>

<tr>
<td>6</td>
<td>2017-01-01 11:11:21</td>
<td>50</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

<tbody>
<tr>
<td>2</td>
</tr>

<tr>
<td>6</td>
</tr>
</tbody>
</table>


<p>Given the <code>transactions</code> table, write a query to get the max quantity purchased for each distinct <code>product_id</code>, every year. </p>

<p>The output should include the <code>year</code>, <code>product_id</code>, and <code>max_quantity</code> for that product sorted by year and <code>product_id</code> ascending.</p>

<h3>Schema:</h3>

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

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

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

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

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

<h3>Example</h3>

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

<table>
<thead>
<tr>
<th>id</th>
<th>user_id</th>
<th>created_at</th>
<th>product_id</th>
<th>quantity</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>59</td>
<td>2019-10-05 00:00:00</td>
<td>500</td>
<td>1</td>
</tr>

<tr>
<td>2</td>
<td>84</td>
<td>2019-08-11 00:00:00</td>
<td>500</td>
<td>2</td>
</tr>

<tr>
<td>3</td>
<td>21</td>
<td>2020-01-25 00:00:00</td>
<td>500</td>
<td>3</td>
</tr>

<tr>
<td>4</td>
<td>47</td>
<td>2019-12-25 00:00:00</td>
<td>500</td>
<td>2</td>
</tr>

<tr>
<td>5</td>
<td>100</td>
<td>2020-03-05 00:00:00</td>
<td>500</td>
<td>2</td>
</tr>

<tr>
<td>6</td>
<td>45</td>
<td>2019-06-30 00:00:00</td>
<td>500</td>
<td>1</td>
</tr>

<tr>
<td>7</td>
<td>44</td>
<td>2020-07-07 00:00:00</td>
<td>500</td>
<td>1</td>
</tr>

<tr>
<td>8</td>
<td>38</td>
<td>2020-09-23 00:00:00</td>
<td>500</td>
<td>2</td>
</tr>

<tr>
<td>9</td>
<td>45</td>
<td>2020-03-08 00:00:00</td>
<td>500</td>
<td>2</td>
</tr>

<tr>
<td>10</td>
<td>16</td>
<td>2020-04-22 00:00:00</td>
<td>999</td>
<td>1</td>
</tr>

<tr>
<td>11</td>
<td>17</td>
<td>2020-06-06 00:00:00</td>
<td>999</td>
<td>2</td>
</tr>

<tr>
<td>12</td>
<td>41</td>
<td>2020-09-17 00:00:00</td>
<td>999</td>
<td>1</td>
</tr>

<tr>
<td>13</td>
<td>34</td>
<td>2019-07-05 00:00:00</td>
<td>999</td>
<td>1</td>
</tr>

<tr>
<td>14</td>
<td>78</td>
<td>2020-02-16 00:00:00</td>
<td>999</td>
<td>3</td>
</tr>

<tr>
<td>15</td>
<td>16</td>
<td>2020-05-22 00:00:00</td>
<td>999</td>
<td>4</td>
</tr>

<tr>
<td>16</td>
<td>4</td>
<td>2020-05-10 00:00:00</td>
<td>999</td>
<td>2</td>
</tr>

<tr>
<td>17</td>
<td>25</td>
<td>2019-12-15 00:00:00</td>
<td>999</td>
<td>1</td>
</tr>

<tr>
<td>18</td>
<td>71</td>
<td>2020-05-03 00:00:00</td>
<td>999</td>
<td>1</td>
</tr>

<tr>
<td>19</td>
<td>3</td>
<td>2020-08-03 00:00:00</td>
<td>999</td>
<td>2</td>
</tr>

<tr>
<td>20</td>
<td>19</td>
<td>2019-03-13 00:00:00</td>
<td>999</td>
<td>5</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>year</th>
<th>product_id</th>
<th>max_quantity</th>
</tr>
</thead>

<tbody>
<tr>
<td>2019</td>
<td>500</td>
<td>2</td>
</tr>

<tr>
<td>2019</td>
<td>999</td>
<td>5</td>
</tr>

<tr>
<td>2020</td>
<td>500</td>
<td>3</td>
</tr>

<tr>
<td>2020</td>
<td>999</td>
<td>4</td>
</tr>
</tbody>
</table>


Max Quantity

<p>Given a table containing data for monthly sales, write a query to find the total amount of each product sold for each month with each product as its own column in the output table.</p>

<h3>Schema</h3>

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

<p><code>monthly_sales</code> table</p>

<table>
<thead>
<tr>
<th><code>month</code></th>
<th><code>product_id</code></th>
<th><code>amount_sold</code></th>
</tr>
</thead>

<tbody>
<tr>
<td>2021-01-01</td>
<td>1</td>
<td>100</td>
</tr>

<tr>
<td>2021-01-01</td>
<td>2</td>
<td>300</td>
</tr>

<tr>
<td>2021-02-01</td>
<td>3</td>
<td>200</td>
</tr>

<tr>
<td>2021-03-01</td>
<td>4</td>
<td>250</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th><strong>month</strong></th>
<th><strong>1</strong></th>
<th><strong>2</strong></th>
<th><strong>3</strong></th>
<th><strong>4</strong></th>
</tr>
</thead>

<tbody>
<tr>
<td>2021-01-01</td>
<td>100</td>
<td>0</td>
<td>300</td>
<td>0</td>
</tr>

<tr>
<td>2021-02-01</td>
<td>0</td>
<td>200</td>
<td>0</td>
<td>0</td>
</tr>

<tr>
<td>2021-03-01</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>250</td>
</tr>
</tbody>
</table>

<h3>Example</h3>

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

<p><strong><code>monthly_sales</code> table</strong></p>

<table>
<thead>
<tr>
<th>month</th>
<th>product_id</th>
<th>amount_sold</th>
</tr>
</thead>

<tbody>
<tr>
<td>2021-01-01</td>
<td>1</td>
<td>100</td>
</tr>

<tr>
<td>2021-01-01</td>
<td>2</td>
<td>0</td>
</tr>

<tr>
<td>2021-01-01</td>
<td>3</td>
<td>250</td>
</tr>

<tr>
<td>2021-01-01</td>
<td>4</td>
<td>300</td>
</tr>

<tr>
<td>2021-02-01</td>
<td>1</td>
<td>50</td>
</tr>

<tr>
<td>2021-02-01</td>
<td>2</td>
<td>200</td>
</tr>

<tr>
<td>2021-02-01</td>
<td>3</td>
<td>0</td>
</tr>

<tr>
<td>2021-02-01</td>
<td>4</td>
<td>300</td>
</tr>

<tr>
<td>2021-03-01</td>
<td>1</td>
<td>400</td>
</tr>

<tr>
<td>2021-03-01</td>
<td>2</td>
<td>150</td>
</tr>

<tr>
<td>2021-03-01</td>
<td>3</td>
<td>200</td>
</tr>

<tr>
<td>2021-03-01</td>
<td>4</td>
<td>300</td>
</tr>

<tr>
<td>2021-04-01</td>
<td>1</td>
<td>150</td>
</tr>

<tr>
<td>2021-04-01</td>
<td>2</td>
<td>200</td>
</tr>

<tr>
<td>2021-04-01</td>
<td>3</td>
<td>50</td>
</tr>

<tr>
<td>2021-04-01</td>
<td>4</td>
<td>300</td>
</tr>

<tr>
<td>2021-05-01</td>
<td>1</td>
<td>100</td>
</tr>

<tr>
<td>2021-05-01</td>
<td>2</td>
<td>150</td>
</tr>

<tr>
<td>2021-05-01</td>
<td>3</td>
<td>0</td>
</tr>

<tr>
<td>2021-05-01</td>
<td>4</td>
<td>300</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>month</th>
<th>1</th>
<th>2</th>
<th>3</th>
<th>4</th>
</tr>
</thead>

<tbody>
<tr>
<td>2021-01-01</td>
<td>100</td>
<td>0</td>
<td>250</td>
<td>300</td>
</tr>

<tr>
<td>2021-02-01</td>
<td>50</td>
<td>200</td>
<td>0</td>
<td>300</td>
</tr>

<tr>
<td>2021-03-01</td>
<td>400</td>
<td>150</td>
<td>200</td>
<td>300</td>
</tr>

<tr>
<td>2021-04-01</td>
<td>150</td>
<td>200</td>
<td>50</td>
<td>300</td>
</tr>

<tr>
<td>2021-05-01</td>
<td>100</td>
<td>150</td>
<td>0</td>
<td>300</td>
</tr>
</tbody>
</table>


Monthly Product Sales

<p>Let’s say your team wants to invest $1 million in a direct mail program and they’ve never done direct mail before.</p>

<p>What do you recommend for the short and the long term? How will you measure the direct impact from the investment?</p>


Direct Mail

Marketing

<p>You’re a data analyst working for Burlington studying customer behavior during the annual Black Friday shopping spree. Each customer may visit the store multiple times throughout the day and make several purchases across different product categories. The marketing team wants to understand which customer segments were most engaged.</p>

<p>Write a SQL query to compute the following metrics for each product category:</p>

<ol>
<li>Total number of unique customers who purchased from that category</li>
<li>Average spend per customer (rounded to 2 decimal places)</li>
<li>Percentage of customers who made more than one purchase in that category (rounded to 2 decimal places)</li>
</ol>

<p>Sort the final results in descending order by average spend per customer.</p>

<p><strong><em>Notes:</em></strong></p>

<ul>
<li><em>A “purchase” means a single row in the purchases table.</em></li>
<li><em>A customer who buys multiple items in one visit still counts as multiple purchases.</em></li>
<li><em>If a customer purchased from a category only once, they should not count toward the “multiple purchase” percentage.</em></li>
<li><em>Every category present in the purchases table should appear in the output.</em></li>
</ul>

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

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

<p><strong><code>purchases</code> table</strong></p>

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

<tbody>
<tr>
<td>purchase_id</td>
<td>INTEGER</td>
</tr>

<tr>
<td>customer_id</td>
<td>INTEGER</td>
</tr>

<tr>
<td>category</td>
<td>VARCHAR</td>
</tr>

<tr>
<td>amount</td>
<td>DECIMAL</td>
</tr>

<tr>
<td>purchase_time</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>category</td>
<td>VARCHAR</td>
</tr>

<tr>
<td>unique_customers</td>
<td>INTEGER</td>
</tr>

<tr>
<td>avg_spend_per_customer</td>
<td>DECIMAL</td>
</tr>

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

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

<p><strong><code>purchases</code> table</strong></p>

<table>
<thead>
<tr>
<th>purchase_id</th>
<th>customer_id</th>
<th>category</th>
<th>amount</th>
<th>purchase_time</th>
</tr>
</thead>

<tbody>
<tr>
<td>1</td>
<td>101</td>
<td>Electronics</td>
<td>120</td>
<td>2025-11-28 09:10:00</td>
</tr>

<tr>
<td>2</td>
<td>101</td>
<td>Electronics</td>
<td>80</td>
<td>2025-11-28 10:00:00</td>
</tr>

<tr>
<td>3</td>
<td>102</td>
<td>Clothing</td>
<td>40</td>
<td>2025-11-28 11:15:00</td>
</tr>

<tr>
<td>4</td>
<td>103</td>
<td>Electronics</td>
<td>300</td>
<td>2025-11-28 13:30:00</td>
</tr>

<tr>
<td>5</td>
<td>102</td>
<td>Electronics</td>
<td>150</td>
<td>2025-11-28 14:10:00</td>
</tr>

<tr>
<td>6</td>
<td>102</td>
<td>Clothing</td>
<td>10</td>
<td>2025-11-28 16:20:00</td>
</tr>

<tr>
<td>7</td>
<td>103</td>
<td>Clothing</td>
<td>50</td>
<td>2025-11-28 17:20:00</td>
</tr>
</tbody>
</table>
<p><strong>Output</strong></p>

<table>
<thead>
<tr>
<th>category</th>
<th>unique_customers</th>
<th>avg_spend_per_customer</th>
<th>pct_multi_purchase_customers</th>
</tr>
</thead>

<tbody>
<tr>
<td>Electronics</td>
<td>3</td>
<td>216.67</td>
<td>33.33</td>
</tr>

<tr>
<td>Clothing</td>
<td>2</td>
<td>50.00</td>
<td>50.00</td>
</tr>
</tbody>
</table>
<p><strong>Explanation</strong></p>

<p>For Electronics, there are three unique customers: 101, 102, and 103. Their spend amounts are 200 for customer 101 (120 + 80), 150 for customer 102, and 300 for customer 103, giving an average spend of 216.67. Only customer 101 made more than one purchase, so the multi-purchase rate is 33.33%.</p>

<p>For Clothing, there are two unique customers: 102 and 103. Customer 102 spent a total of 50 (40 + 10), while customer 103 spent 50, resulting in an average of 50.00. Customer 102 is the only one with multiple purchases, leading to a multi-purchase rate of 50.00%.</p>


Write a SQL query to compute metrics for each product category.

Black Friday Shopping Spree

<p>Let’s say that on Facebook composer, the posting tool, drops from 3% posts per user last month to 2.5% post per user today. </p>

<p>How would you investigate what happened?</p>

<p>Let’s say the drop is in photo posts. What would you investigate now?</p>


<p>In the analysis of a drop in photo posts per user on a social media site like Facebook, which factor(s) could influence the change in the average number of posts per user?</p>


In the analysis of a drop in photo posts per user on a social media site like Facebook, which factor(s) could influence the change in the average number of posts per user?

Post Composer Drop

Product Sense & Metrics

<p>You are given two sentences as <code>strings</code>. Create a function that returns a list of all words that are <strong>not</strong> in both sentences.</p>

<p>You can assume that the sentences have no punctuation marks, extra tabs, or spaces.</p>

<p><em>Note: The strings <code>&#34;Game&#34;</code> and <code>&#34;game&#34;</code> should be considered the same word. Because of this, make sure all the strings in the list you output are in lowercase.</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">string1</span> <span class="o">=</span> <span class="s2">&#34;I want to eat fish today with my friends&#34;</span>
</span></span><span class="line"><span class="cl"><span class="n">string2</span> <span class="o">=</span> <span class="s2">&#34;All I want is to eat fish today with my friends&#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">mismatched_words</span><span class="p">(</span><span class="n">string1</span><span class="p">,</span><span class="n">string2</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="p">[</span><span class="s2">&#34;all&#34;</span><span class="p">,</span><span class="s2">&#34;is&#34;</span><span class="p">]</span>
</span></span></code></pre>


Calculate Moving Average	SQL	Easy
Predict Customer Churn	Machine Learning	Medium
A/B Test Significance	Statistics	Medium
Optimize Query Performance	SQL	Hard
Feature Importance Analysis	Machine Learning	Medium
Clean Missing Data	Python	Easy
Neural Network Architecture	Deep Learning	Hard
Calculate Cohort Retention	SQL	Medium
Bayesian Probability	Statistics	Easy
Recommend Similar Products	Machine Learning	Hard

Hallmark Interview Questions

Hallmark Interview Guides

Hallmark Interview Questions

Challenge

Hallmark Salaries by Position

Discussion & Interview Experiences

Discussion & Interview Experiences