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

Write a query to identify customers who placed more than three transactions each in both 2019 and 2020

Customer Orders

Write a query to get the average order value by gender rounded to two decimal places

Average Order Value

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

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

Machine Learning

Which model would perform better and why?

Booking Regression

How does random forest generate the forest and why use it over logistic 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

How would you build a model to figure out the most optimal way to send 10 emails copies to increase conversions to a list of subscribers?

Email Marketing System

Data Structures & Algorithms

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

Common Prefix

Write a function to return True if two strings are anagrams of each other and False if they are not

Valid Anagram

Find Mismatched Words

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

String Palindromes

Business Case

Friend Requests Down

How would you investigate a 10% drop in usage on Google Docs

Google Docs Drop

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

Identical Pen Pricing

Behavioral

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

Your Strengths and Weaknesses

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

Client Solution Pushback

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

Why Do You Want to Work With Us

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

How would you investigate the drop in Facebook composer posts per user, especially photo posts

Product Sense & Metrics

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


Write a query to get the cost of all transactions by user ordered by total cost descending

Total Transactions

<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>What is the difference between covariance and correlation? Provide an example.</p>


Explain the difference between covariance and correlation and provide an example

Covariance vs Correlation

Statistics

<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>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>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 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>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>Let’s say you’re leading the analytics for a $100,000 digital marketing campaign to drive new signups for a subscription app. The campaign runs across both paid social and search channels over 30 days.</p>

<p>What metrics would you focus on to evaluate its effectiveness, and how would you use data to optimize performance during the campaign?</p>


How would you use metrics and data to evaluate and improve a digital marketing campaign?

Digital Marketing Metrics

Marketing

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


<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>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>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>Let’s say you manage a hardware store and need to order snow shovels in Q3, aiming to have zero inventory left by the end of winter. Last year, during a mild winter, you sold 120 shovels at $20 each, but in harsher years, sales have reached up to 400.</p>

<p>How would you estimate the number of shovels to order and set the price for a mild winter, given your goal of selling out by season’s end?</p>


How would you decide how many snow shovels to order and what price to set to sell out during a mild winter?

Snow Shovel Inventory

Supply Chain

<p>Let’s say we’ve proposed a new analytics dashboard to a client, but after reviewing our design and data sources, the client says the solution doesn’t meet their needs and pushes back on several key features.</p>

<p>How would you approach the conversation to negotiate alternative solutions, and what steps would you take to ensure disagreements are handled constructively so we can still reach a successful outcome?</p>


<p>The sales department is conducting a performance review and is interested in trends in product sales. They have decided to use a weighted moving average as part of their analysis.</p>

<p>Write a SQL query to calculate the 3-day weighted moving average of sales for each product. Use the weights 0.5 for the current day, 0.3 for the previous day, and 0.2 for the day before that.</p>

<p><em>Note: Only output the weighted moving average for dates that have two or more preceding dates. You may assume that the table doesn’t skip dates.</em></p>

<h3><strong>Example:</strong></h3>

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

<p><code>sales</code> table</p>

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

<tbody>
<tr>
<td>date</td>
<td>DATE</td>
</tr>

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

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

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

<tr>
<td>weighted_avg_sales</td>
<td>FLOAT</td>
</tr>
</tbody>
</table>


Write a query to calculate the 3-day weighted moving average of product sales.

Weighted Average Sales

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

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