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

GameStop Interview Questions

GameStop Interview Guides

Select the 2nd highest salary in the engineering department

2nd Highest Salary

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

Comments Histogram

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

Empty Neighborhoods

Closest SAT Scores

Subscription Overlap

Data Structures & Algorithms

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

Merge Sorted Lists

Get Top N Frequent Words

Weekly Aggregation

Write a function to return a dataframe containing every transaction with a total value of over $100.

Over 100 Dollars

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

Prime to N

Machine Learning

Encoding Categorical Features

Coefficients of Logistic Regression

Booking Regression

How do we give each rejected applicant a reason why they got rejected?

Rejection Reason

Bank Fraud Model

Probability

Found Item

Jars and Coins

Flipping 576 Times

How would you assess if a coin is fair after observing 8 tails in 10 flips?

Fair Coin

HHT or HTT

A/B Testing

Experiment Validity

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

Pre-Launching Shows

Button AB Test

A/B Test Power Size

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

Customer Orders

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


<p>Given 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 product purchases. Each row in the table represents an individual user product purchase.</p>

<p>Write a query to get the number of customers that were upsold, or in other words, the number of users who bought additional products after their first purchase.</p>

<p><em>Note: If the customer purchased two things on the same day, that does not count as an upsell, as they were purchased within a similar timeframe.</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><strong>Output:</strong></p>

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

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


Write a query to get the number of customers that were upsold

Upsell Transactions

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


Rolling Bank Transactions

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

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

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

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

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

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

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

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

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

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

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

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

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


Last Transaction

<p>Given a table of product subscriptions with a subscription start date and end date for each user, write a query that returns true or false whether or not each user has a subscription date range that overlaps with any other completed subscription.</p>

<p>Completed subscriptions have <code>end_date</code> recorded.</p>

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

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

<p><code>subscriptions</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>start_date</code></td>
<td>DATETIME</td>
</tr>

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

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

<tbody>
<tr>
<td>1</td>
<td>2019-01-01</td>
<td>2019-01-31</td>
</tr>

<tr>
<td>2</td>
<td>2019-01-15</td>
<td>2019-01-17</td>
</tr>

<tr>
<td>3</td>
<td>2019-01-29</td>
<td>2019-02-04</td>
</tr>

<tr>
<td>4</td>
<td>2019-02-05</td>
<td>2019-02-10</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

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

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

<tr>
<td>2</td>
<td>1</td>
</tr>

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

<tr>
<td>4</td>
<td>0</td>
</tr>
</tbody>
</table>


<p>Given a table of <code>transactions</code>, write a query to find the average quantity of each product purchased per transaction each year.</p>

<p>The output should include the year, product_id, and average quantity of the product purchased per transaction that year. The output table should be sorted by year and product_id in ascending order. The value for avg_quantity should be rounded to two decimal places.</p>

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

<p><strong>Input:</strong>
<code>transactions</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>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>Columns</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>avg_quantity</code></td>
<td>DECIMAL</td>
</tr>
</tbody>
</table>


Find the average yearly purchases for each product

Average Quantity

<p>Consider a trip from one city to another that may contain many layovers. Given the list of flights out of order, each with a starting city and end city, write a function <code>plan_trip</code> to reconstruct the path of the trip so the trip tickets are in order.</p>

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

<p>For a trip from Bombay to Beijing:</p>

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="n">flights</span> <span class="o">=</span> <span class="p">[</span>
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Chennai&#39;</span><span class="p">,</span> <span class="s1">&#39;Bangalore&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Bombay&#39;</span><span class="p">,</span> <span class="s1">&#39;Delhi&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Goa&#39;</span><span class="p">,</span> <span class="s1">&#39;Chennai&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Delhi&#39;</span><span class="p">,</span> <span class="s1">&#39;Goa&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Bangalore&#39;</span><span class="p">,</span> <span class="s1">&#39;Beijing&#39;</span><span class="p">]</span>
</span></span><span class="line"><span class="cl"><span class="p">]</span>
</span></span><span class="line"><span class="cl"><span class="n">output</span> <span class="o">=</span> <span class="p">[</span>
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Bombay&#39;</span><span class="p">,</span> <span class="s1">&#39;Delhi&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Delhi&#39;</span><span class="p">,</span> <span class="s1">&#39;Goa&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Goa&#39;</span><span class="p">,</span> <span class="s1">&#39;Chennai&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Chennai&#39;</span><span class="p">,</span> <span class="s1">&#39;Bangalore&#39;</span><span class="p">],</span> 
</span></span><span class="line"><span class="cl">    <span class="p">[</span><span class="s1">&#39;Bangalore&#39;</span><span class="p">,</span> <span class="s1">&#39;Beijing&#39;</span><span class="p">],</span>
</span></span><span class="line"><span class="cl"><span class="p">]</span>
</span></span></code></pre>


Reconstruct the path of a trip so that the trip tickets are in order.

Scrambled Tickets

<p>A jar holds 1000 coins. Out of all of the coins, 999 are fair and one is double-sided with two heads. Picking a coin at random, you toss the coin ten times.</p>

<p>Given that you see 10 heads, what is the probability that the coin is double headed and the probability that the next toss of the coin is also a head?</p>


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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Emails Opened

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

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

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

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

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

<p><code>accounts</code> table</p>

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

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

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

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

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

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

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

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

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

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

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


Download Facts

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

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

<h3>Schema</h3>

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

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

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

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

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

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

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

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

<h3>Example</h3>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Flight Records

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

P-value to a Layman

Statistics

<p>You’re given two dataframes: <code>transactions</code> and <code>products</code>.</p>

<p>The transactions dataframe contains transaction ids, product ids, and the total amount of each product sold.</p>

<p>The products dataframe contains product ids and prices.</p>

<p>Write a function to return a dataframe containing every transaction with a total value of over $100. Include the total value of the transaction as a new column in the dataframe.</p>

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

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

<pre tabindex="0" class="chroma"><code><span class="line"><span class="cl"><span class="kn">import</span> <span class="nn">pandas</span> <span class="k">as</span> <span class="nn">pd</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">transactions</span> <span class="o">=</span> <span class="p">{</span><span class="s2">&#34;transaction_id&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">,</span> <span class="mi">3</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="s2">&#34;product_id&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="mi">101</span><span class="p">,</span> <span class="mi">102</span><span class="p">,</span> <span class="mi">103</span><span class="p">,</span> <span class="mi">104</span><span class="p">,</span> <span class="mi">105</span><span class="p">],</span> <span class="s2">&#34;amount&#34;</span> <span class="p">:</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">8</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span> <span class="mi">2</span><span class="p">]}</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">products</span> <span class="o">=</span> <span class="p">{</span><span class="s2">&#34;product_id&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="mi">101</span><span class="p">,</span> <span class="mi">102</span><span class="p">,</span> <span class="mi">103</span><span class="p">,</span> <span class="mi">104</span><span class="p">,</span> <span class="mi">105</span><span class="p">],</span> <span class="s2">&#34;price&#34;</span> <span class="p">:</span> <span class="p">[</span><span class="mf">20.00</span><span class="p">,</span> <span class="mf">21.00</span><span class="p">,</span> <span class="mf">15.00</span><span class="p">,</span> <span class="mf">16.00</span><span class="p">,</span> <span class="mf">52.00</span><span class="p">]}</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">df_transactions</span> <span class="o">=</span> <span class="n">pd</span><span class="o">.</span><span class="n">DataFrame</span><span class="p">(</span><span class="n">transactions</span><span class="p">)</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="n">df_products</span> <span class="o">=</span> <span class="n">pd</span><span class="o">.</span><span class="n">DataFrame</span><span class="p">(</span><span class="n">products</span><span class="p">)</span>
</span></span></code></pre>

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

<table>
<thead>
<tr>
<th>transaction_id</th>
<th>product_id</th>
<th>amount</th>
<th>total_value</th>
</tr>
</thead>

<tbody>
<tr>
<td>2</td>
<td>102</td>
<td>5</td>
<td>105.00</td>
</tr>

<tr>
<td>3</td>
<td>103</td>
<td>8</td>
<td>120.00</td>
</tr>

<tr>
<td>5</td>
<td>105</td>
<td>2</td>
<td>104.00</td>
</tr>
</tbody>
</table>


<p>Let’s say that you’re a data scientist working on a distribution team at Amazon.</p>

<p>Create a report displaying which shipments were delivered to customers during their membership period.</p>

<p>If the transaction is shipped during the membership period, column <code>is_member</code> should have the value <code>Y</code>. If not, the column should have the value <code>N</code>.</p>

<p><em>Hint: You may assume that <code>customer_id</code> is unique and that shipments are made only to registered customers.</em></p>

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

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

<p><code>customers</code> table</p>

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

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

<tr>
<td><code>membership_start_date</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>membership_end_date</code></td>
<td>DATETIME</td>
</tr>
</tbody>
</table>
<p><code>shipments</code> table</p>

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

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

<tr>
<td><code>ship_date</code></td>
<td>DATETIME</td>
</tr>

<tr>
<td><code>customer_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>shipment_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>ship_date</code></td>
<td>DATETIME</td>
</tr>

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

<tr>
<td><code>is_member</code></td>
<td>TEXT</td>
</tr>

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


Create a report displaying which shipments were delivered to customers during their membership period.

Completed Shipments

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

Swipe Precision

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


Monthly Customer Report

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

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


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

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

Network Experiment Design I

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

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

Network Experiment Design II

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

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

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

GameStop Interview Questions

GameStop Interview Guides

GameStop Interview Questions

Challenge

GameStop Salaries by Position

Discussion & Interview Experiences

Discussion & Interview Experiences