Prepare for and practice interview questions from FlightAware across topics like Algorithms, SQL, Python and more.

FlightAware Interview Questions

FlightAware Interview Guides

Data Structures & Algorithms

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

Merge Sorted Lists

Find the missing integer from a array of consequtive integers

Find the Missing Number

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

Complete Addresses

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

Get Top N Frequent Words

Write a function can_shift to return whether or not A can be shifted some number of places to get B

String Shift

Machine Learning

Which model would perform better and why?

Booking Regression

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

Missing Housing Data

How would we build a model to detect fraud and text customers to approve or deny fraudulent transactions

Bank Fraud Model

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

Bagging vs Boosting

Which model do you pick given 85% and 82% accuracy

85% vs 82%

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 to return the two students with the closest test scores and the score difference

Closest SAT Scores

Write a query to return whether each user's subscription date range overlaps with any other completed subscription

Subscription Overlap

Longest Streak Users

Data Modeling

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

Swiping App Design

System design for a digital classroom service.

Digital Classroom System Design

How would you create a schema to represent client click data on the web

Click Data Schema

Migrating a social network's data from a document database to a relational database for better data metrics

Relational Migration

Design a database schema for a blogging platform.

Blogging Platform Schema

Data Pipelines

Design a data pipeline to compute hourly, daily, and weekly active user metrics from a data lake for an hourly-refreshing dashboard

Data Pipelines and Aggregation

How would you build an ETL pipeline to ingest Stripe payment data into a warehouse for revenue dashboards and analytics

Payment Data Pipeline

How would you clean a 100GB .csv file without loading it into memory

Processing Large CSV

Redesign batch credit card processing to enable real-time streaming for fraud detection and reporting.

Real-Time Transaction Streaming

Design a data pipeline that supports resumable loading for a fact table after partial failure.

Resumable Fact Table Load

Write a SQL query to select the 2nd highest salary in the engineering department.

Note: If more than one person shares the highest salary, the query should select the next highest salary.

Example:

Input:

<code>employees</code> table

<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>
<code>departments</code> table

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

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

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

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

Bonus: What’s the time complexity?

Example:

Input:

<pre tabindex="0" class="chroma"><code>list1 = [1,2,5]
list2 = [2,4,6]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def merge_list(list1,list2) -&gt; [1,2,2,4,5,6]
</code></pre>

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?

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

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.

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

Example:

Input:

<code>subscriptions</code> table

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

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

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

Note: Assume bin buckets class intervals of one.

Note: Comments that were created outside of January 2020 should be counted in a “0” bucket

Example:

Input:

<code>users</code> table

<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>
<code>comments</code> table

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

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

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.

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

Example:

Input:

<code>scores</code> table

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

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

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

Example:

Input:

<code>transactions</code> table

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

<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>
<code>users</code> table

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

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

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

Monthly Customer Report

We’re given a table of product purchases. Each row in the table represents an individual user product purchase.

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.

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.

Example:

Input:

<code>transactions</code> table

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

<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

A team wants to A/B test multiple different changes through a sign-up funnel.

For example, on a page, a button is currently red and at the top of the page. They want to see if changing a button from red to blue and/or from the top of the page to the bottom of the page will increase click-through.

How would you set up this test?

Your team wants to run an AB test on the color (red or blue) and position (top/bottom of the page) of a button that links to a promotion.

How many different variants of the test should you run to see the effect that a red button has on the bottom of the page, if currently the button is blue and on the top of the page.

Your team wants to run an AB test on the color (red or blue) and position (top/bottom of the page) of a button that links to a promotion.

How many different variants of the test should you run to see the effect that a red button has on the bottom of the page, if currently the button is blue and on the top of the page.

A/B Test Variants

Button AB Test

A/B Testing

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

Example:

Input:

<pre tabindex="0" class="chroma"><code>A = &#39;abcde&#39;
B = &#39;cdeab&#39;
can_shift(A, B) == True

A = &#39;abc&#39;
B = &#39;acb&#39;
can_shift(A, B) == False
</code></pre>

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

Describing a data project and its challenges

Hurdles In Data Projects

Behavioral

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

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

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

Example:

Input:

<code>employees</code> table

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

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

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

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

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

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

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

Top 5 Turnover Risk

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.

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

Input:

<code>big_table</code> table

<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

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

Note: Complexity of \(O(n)\) required.

Example:

Input:

<pre tabindex="0" class="chroma"><code>nums = [0,1,2,4,5] 
missing_number(nums) -&gt; 3
</code></pre>

You are given two rectangles <code>a</code> and <code>b</code> each defined by four ordered pairs denoting their corners on the <code>x</code>, <code>y</code> plane. Write a function <code>rectangle_overlap</code> to determine whether or not they overlap. Return <code>True</code> if so, and <code>False</code> otherwise.

Note: If the two rectangles border one another, or share a corner like two diagonally adjacent positions on a chessboard, they are said to overlap.

Note: The lists of ordered pairs are in no particular order. The first entry in list <code>a</code> could be the top left corner while the first in list <code>b</code> is the bottom right.

Example:

Input:

<pre tabindex="0" class="chroma"><code>
a = [(-3,5), (-3,2),(0,5),(0,2)]
b = [(-1,4), (3,4), (3,1), (-1,1)]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def rectangle_overlap(a, b) -&gt; True
</code></pre>

As point <code>(0,2)</code> is fully contained in rectangle <code>b</code> and point <code>(-1,4)</code> is fully contained in a rectangle <code>a</code>.

Write a function to determine whether or not two rectangles overlap.

Rectangle Overlap

Given an integer <code>N</code>, write a function that returns a list of all of the prime numbers up to <code>N</code>.

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

Example:

Input:

<pre tabindex="0" class="chroma"><code>N = 3
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def prime_numbers(N) -&gt; [2,3]
</code></pre>

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

Prime to N

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

Example:

Input:

<code>employees</code> table

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

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

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

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

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

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

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

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

Largest Salary by Department

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

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

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

Note: Assume that employees only work on one project at a time.

Example:

Input:

<code>employees</code> table

<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>
<code>projects</code> table

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

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

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

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

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

<tr>
<td><code>budget</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
departments table

<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>
<code>employee_projects</code> table

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

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

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

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

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

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

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

Over-Budget Projects

You have a table containing information about the time that employees have worked on a project.

Write a query to find how many unique calendar days each employee worked. Order your query by the <code>employee_id</code>.

Example:

An employee worked on two projects, and recorded their time like this:

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

<tbody>
<tr>
<td>1	</td>
<td>1</td>
<td>2024-01-01</td>
<td>2024-01-06</td>
</tr>

<tr>
<td>1	</td>
<td>2</td>
<td>2024-01-03</td>
<td>2024-01-07</td>
</tr>
</tbody>
</table>
The number of unique calendar days is therefore 7.

<h3>Schema</h3>

Input:

<code>projects</code> table

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

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

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

<tr>
<td><code>start_date</code>	</td>
<td>	DATE	</td>
</tr>

<tr>
<td><code>end_date</code>	</td>
<td>	DATE	</td>
</tr>
</tbody>
</table>
Output:

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

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

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

<h3>Example</h3>

Input:

<code>projects</code> table

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

<tbody>
<tr>
<td>1</td>
<td>1</td>
<td>2024-01-01</td>
<td>2024-01-15</td>
</tr>

<tr>
<td>1</td>
<td>1</td>
<td>2024-01-21</td>
<td>2024-01-31</td>
</tr>

<tr>
<td>1</td>
<td>2</td>
<td>2024-01-25</td>
<td>2024-02-28</td>
</tr>

<tr>
<td>2</td>
<td>3</td>
<td>2024-01-01</td>
<td>2024-01-31</td>
</tr>
</tbody>
</table>
Output:

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

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

<tr>
<td>2</td>
<td>31</td>
</tr>
</tbody>
</table>

Unique Work Days

Write a function <code>minimum_change</code> to find the minimum number of coins that make up the given amount of change <code>cents</code>. Assume we only have coins of value 1, 5, 10, and 25 cents.

Example:

Input:

<pre tabindex="0" class="chroma"><code>cents = 73
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def minimum_change(cents) -&gt; 7
#(25 + 25 + 10 + 10 + 1 + 1 + 1)
</code></pre>

Minimum Change

Given a table with event logs, find the top five users with the longest continuous streak of visiting the platform.

Note: A continuous streak counts if the user visits the platform at least once per day on consecutive days.

Example:

Input:

<code>events</code> table

<table>
<thead>
<tr>
<th><code>Column</code></th>
<th><code>Type</code></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>url</code></td>
<td>VARCHAR</td>
</tr>
</tbody>
</table>
Output:

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

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

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

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.

Example:

For a trip from Bombay to Beijing:

Input:

<pre tabindex="0" class="chroma"><code>flights = [
 [&#39;Chennai&#39;, &#39;Bangalore&#39;], 
 [&#39;Bombay&#39;, &#39;Delhi&#39;], 
 [&#39;Goa&#39;, &#39;Chennai&#39;], 
 [&#39;Delhi&#39;, &#39;Goa&#39;], 
 [&#39;Bangalore&#39;, &#39;Beijing&#39;]
]
output = [
 [&#39;Bombay&#39;, &#39;Delhi&#39;], 
 [&#39;Delhi&#39;, &#39;Goa&#39;], 
 [&#39;Goa&#39;, &#39;Chennai&#39;], 
 [&#39;Chennai&#39;, &#39;Bangalore&#39;], 
 [&#39;Bangalore&#39;, &#39;Beijing&#39;],
]
</code></pre>

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

Scrambled Tickets

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

Each of the impressions goes to just one user, defined at random. Every user in the audience has the same random chance of receiving each impression. 

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

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

Compute the probability a user gets exactly 0 impressions, and the probability everyone gets at least 1 impression

Impression Reach

Probability

Given an array and a target integer, write a function <code>sum_pair_indices</code> that returns the indices of two integers in the array that add up to the target integer. If not found, just return an empty list.

Note: Can you do it on \(O(n)\) time?

Note: Even though there could be many solutions, only one needs to be returned.

Example 1:

Input:

<pre tabindex="0" class="chroma"><code>array = [1 2 3 4] 
target = 5 
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def sum_pair_indices(array, target) -&gt; [0 3] or [1 2]
</code></pre>

Example 2:

Input:

<pre tabindex="0" class="chroma"><code>array = [3]
target = 6 
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>Do NOT return [0 0] as you can&#39;t use an index twice.
</code></pre>

Given an array and a target integer, write a function that returns the indices of two integers in the array that add up to the target integer.

Target Indices

Let’s say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service in addition that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

How would we build this model?

Let’s say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

Which statement is true?

Let's say that you work at a bank that wants to build a model to detect fraud on the platform.

The bank wants to implement a text messaging service that will text customers when the model detects a fraudulent transaction in order for the customer to approve or deny the transaction with a text response.

Which statement is true?

Write a query to return pairs of projects where the end date of one project matches the start date of another project. 

Example:

Input:

<code>projects</code> table

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

<tr>
<td><code>start_date</code></td>
<td>DATETIME</td>
</tr>

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

<tr>
<td><code>budget</code></td>
<td>FLOAT</td>
</tr>
</tbody>
</table>
Output

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

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

<tr>
<td><code>project_title_start</code></td>
<td>VARCHAR</td>
</tr>

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

Write a query to return pairs of projects where one project's end date matches another's start date

Project Pairs

You’re given a string that may contain the characters <code>{</code>, <code>}</code>, <code>[</code>, <code>]</code>, <code>(</code>, and <code>)</code>.

Task: Verify that the string is balanced. A balanced string is one where every opening character, <code>{</code>, <code>[</code>, or <code>(</code>, has a corresponding closing character, <code>}</code>, <code>]</code>, or <code>)</code>.

Write a function called <code>is_balanced(string: str) -&gt; bool</code> which verifies the balance of a string.

Example:

<pre tabindex="0" class="chroma"><code>is_balanced(&#39;(())[]{}&#39;) -&gt; True
</code></pre>

<pre tabindex="0" class="chroma"><code>is_balanced(&#39;{([(){}])()}&#39;) -&gt; True
</code></pre>

<pre tabindex="0" class="chroma"><code>is_balanced(&#39;{}[]())&#39;) -&gt; False
</code></pre>

<hr/>

Write a function that tests whether a string of brackets is balanced.

The Brackets Problem

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.

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

Example:

Input:

<code>users</code> table

<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>
<code>purchases</code> table

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

<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

Given a list of <code>integers</code>, and an integer <code>N</code>, write a function <code>sum_to_n</code> to find all combinations that sum to the value <code>N</code>.

Example: 

Input:

<pre tabindex="0" class="chroma"><code>integers = [2,3,5]
N = 8
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>def sum_to_n(integers, N) -&gt;
 [
 [2,2,2,2],
 [2,3,3],
 [3,5]
]
</code></pre>

Given a list of integers, and an integer N, write a function to find all combinations that sum to the value N.

Sum to N

Given the integer list nums with length n, create a function that converts each integer in the list into its corresponding Roman numeral representation. The Roman numeral symbols and corresponding values are provided in the table below. The function must be able to handle integers up to 1000.

<table>
<thead>
<tr>
<th>Symbols</th>
<th>Values</th>
</tr>
</thead>

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

<tr>
<td>IV</td>
<td>4</td>
</tr>

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

<tr>
<td>IX</td>
<td>9</td>
</tr>

<tr>
<td>X</td>
<td>10</td>
</tr>

<tr>
<td>XL</td>
<td>40</td>
</tr>

<tr>
<td>L</td>
<td>50</td>
</tr>

<tr>
<td>XC</td>
<td>90</td>
</tr>

<tr>
<td>C</td>
<td>100</td>
</tr>

<tr>
<td>CD</td>
<td>400</td>
</tr>

<tr>
<td>D</td>
<td>500</td>
</tr>

<tr>
<td>CM</td>
<td>900</td>
</tr>

<tr>
<td>M</td>
<td>1000</td>
</tr>
</tbody>
</table>

<h3>Examples:</h3>

Input:

<pre tabindex="0" class="chroma"><code>nums = [1, 4, 179]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>[&#34;I&#34;, &#34;IV&#34;, &#34;CLXXIX&#34;]
</code></pre>

Convert a list of integers into their Roman numeral representations

Integer to Roman

Given a 2D terrain represented by an array of non-negative integers, where each integer represents the height of a terrain level at that index, implement an algorithm to calculate the total amount of rainwater that can be trapped in this terrain.
Consider that rainwater can only be trapped between two terrain levels with higher heights, and the trapped water cannot flow out through the edges.

The algorithm should have an optimal time complexity of O(n) and a space complexity of O(n).
 Provide a detailed explanation of the algorithm and its implementation in Python.

Example:

<table>
<thead>
<tr>
<th>Index</th>
<th>0</th>
<th>1</th>
<th>2</th>
<th>3</th>
<th>4</th>
</tr>
</thead>

<tbody>
<tr>
<td>Terrain Level</td>
<td>3</td>
<td>0</td>
<td>2</td>
<td>0</td>
<td>4</td>
</tr>

<tr>
<td>Trapped Water</td>
<td>0</td>
<td>3</td>
<td>1</td>
<td>3</td>
<td>0</td>
</tr>
</tbody>
</table>
In this table, the ‘Index’ row represents the position of the terrain levels, the ‘Terrain Level’ row shows the height of the terrain at each index, and the ‘Trapped Water’ row illustrates the trapped rainwater between the terrain levels. The total trapped rainwater in this example is 7 units (3 + 1 + 3).

Input:

<pre tabindex="0" class="chroma"><code>terrainLevels = [3, 0, 2, 0, 4]
</code></pre>

Output:

<pre tabindex="0" class="chroma"><code>7
</code></pre>

In this example, the terrain is represented by the <code>terrainLevels</code> array. The algorithm should calculate the total trapped rainwater, which is 7 units in this case. The trapped rainwater is formed between the heights of 3, 0, 2, 0, and 4.

Given a 2D terrain array, calculate the total amount of trapped rainwater with O(n) time and O(n) space

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

FlightAware Interview Questions

FlightAware Interview Guides

FlightAware Interview Questions

Challenge

Discussion & Interview Experiences

Discussion & Interview Experiences