logo

8 Remitly SQL Interview Questions (Updated 2024)

Updated on

March 2, 2024

At Remitly, SQL is often used for analyzing transaction data for fraud detection and optimizing remittance flow patterns for improved customer service. Unsurprisingly this is why Remitly typically asks SQL query questions in interviews for Data Analytics, Data Science, and Data Engineering jobs.

As such, to help you study for the Remitly SQL interview, we'll cover 8 Remitly SQL interview questions – how many can you solve?

8 Remitly SQL Interview Questions

SQL Question 1: Transaction Analysis with Window Functions

As a data scientist at Remitly, you are tasked with performing an analysis on the company's historical money transfer transactions. Your job is to write a SQL query that will determine the total amount of money transferred for each sender, the average amount of money transferred per transaction for each sender, and the rank of each sender based on the total amount of money transferred.

Below is a sample data from the table:

Example Input:
transaction_idsender_idreceiver_idtransfer_dateamount
100111012022-06-08500
100221022022-06-10200
100311032022-06-18300
100431042022-07-261000
100531052022-07-05500
100621062022-08-01100
100721072022-08-201000
100811082022-09-041500

Answer:


This query is broken down into two main parts:

  • The first part (total_transfers) is using an aggregate function to calculate the total and average amount of money transferred for each sender.

  • The second part (sender_ranks) is using a window function to assign a rank to each sender based on total amount transferred. The RANK() function is used to provide this ranking. The results are then selected from the ranking table.

For more window function practice, try this Uber SQL problem within DataLemur's online SQL code editor:

Uber Window Function SQL Interview Question

SQL Question 2: Filter Customers based on Transactions

Remitly is a service that allows users to send money globally. You are given a table that contains fields such as , , , , , , and . The column can contain the values , , and .

Your task is to write a SQL query to filter all customers who have made a successful transaction of more than dollars from to between , and .

Example Input:
transaction_idcustomer_idsend_datesend_countryreceive_countryamountstatus
101120101/15/2020 00:00:00USAIndia1024completed
102243402/07/2020 00:00:00USAIndia540completed
103354403/21/2020 00:00:00USAIndia780pending
104550012/26/2020 00:00:00USAIndia777completed
105732201/31/2020 00:00:00USAIndia430failed

Answer:


This query uses a combination of and conditions to filter the customers based on the given business requirements. It checks that the is 'USA', the is 'India', the is 'completed', the is greater than 500, and the is between '2020-01-01' and '2020-03-31'.

SQL Question 3: Why is normalizing a database helpful?

There are several advantages to normalizing a database, including less redundancy, more flexibility, and better performance.

  • Less Redundancy: Normalization reduces redundancy by breaking down a larger, more general table into smaller, more specific tables. This reduces the amount of data that needs to be accessed for queries.

  • More Flexibility: Normalization makes it easier to modify the structure of the database, as there is less redundancy, so it allows you to make changes to one table without affecting others. This makes it easier to adapt the database to changing business needs (a very real reality at Remitly!)

  • Better Performance: Normalization can improve the performance of the database by reducing the amount of data that needs to be stored and accessed in a single table. This can result in faster query times and better overall performance.

Remitly SQL Interview Questions

SQL Question 4: Average Transaction Amount

At Remitly, an online international money transfer service, it's important to understand the average amounts of transactions that are sent. For this question, let's consider a table that stores the details of every transaction made through Remitly. Write an SQL query to find the average transaction amount per country (receiving country) for the last month.

Example Input:
transaction_idsender_idreceiver_idtransaction_dateamountreceiving_country
1011208/12/2022200.00Philippines
1023408/13/2022150.00India
1035608/15/2022500.00Mexico
1047808/17/2022300.00Philippines
10591008/18/2022250.00India

Answer:


This query first filters out the transactions from the previous month using the WHERE clause. Then it groups transactions by receiving country and calculates the average transaction amount for each country. The average amount could provide insights into which countries are receiving larger sums of money on average from their inbound Remitly transactions.

SQL Question 5: What's the difference between and clause?

The clause is used to filter the groups created by the clause. It's similar to the clause, but it is used to specify conditions on the groups created by the clause, rather than on the individual rows of the table.

For example, say you were analyzing salaries for analytics employees at Remitly:


This query retrieves the total salary for each Analytics department at Remitly and groups the rows by the specific department (i.e. ""Marketing Analytics"", ""Business Analytics"", ""Sales Analytics"" teams).

The clause then filters the groups to include only Remitly departments where the total salary is greater than $1 million

SQL Question 6: Calculate Average Transaction Value per Country

Remitly is a digital remittance service, enabling users to send money to others in different countries. Given a table of these transactions, can you write a SQL query to find the average value of transactions sent to each country per month?

Please identify the month in the format 'YYYY-MM'. Assume the table has the following schema:

Example Input:
transaction_iduser_idsend_datesend_countryreceive_countryamount
17501012022-08-02 00:00:00USPH200.00
22062022022-08-10 00:00:00USMX150.00
30943032022-08-18 00:00:00USPH250.00
45824042022-09-05 00:00:00USMX200.00
53975052022-09-08 00:00:00USPH300.00

Answer:


This query first groups the transactions by both month (with year) and the receiving country. It then calculates the average transaction value to each country per month using the AVG() function. Finally, it orders the output by month and average amount in descending order.

Example Output:
monthreceive_countryavg_amount
2022-08PH225.00
2022-08MX150.00
2022-09PH300.00
2022-09MX200.00

Which shows that in August 2022, the average transaction amount sent to Philippines (PH) was 225andtoMexico(MX)was225 and to Mexico (MX) was 150, etc.

SQL Question 7: What is a database index, and what are the different types of indexes?

A database index is a way to optimize the performance of a database by reducing the amount of data that needs to be searched to retrieve a record.

There are several types of indexes that can be used in a database:

  1. Primary index: a unique identifier is used to access the row directly.
  2. Unique index: used to enforce the uniqueness of the indexed columns in a table.
  3. Composite index: created on multiple columns of a table, is used to speed up the search process for multiple columns
  4. Clustered index: determines the physical order of the data in a table

For a concrete example, say you had a table of Remitly customer payments with the following columns: payment_id, customer_id, payment_amount, and payment_date.

Here's what a clustered index on the column would look like:


A clustered index on the column would determine the physical order of the records in the table based on the . This means that the records with the earliest values would be stored together physically in the table, followed by records with later payment_date values.

Having a clustered index on the column can speed up queries that filter or sort the data based on the payment_date, as the records are already physically sorted in the table. For example, if you want to retrieve all the payments made in the month of January, the database can use the clustered index to quickly locate and retrieve the desired records without having to search through the entire table.

SQL Question 8: Transaction Data Analysis for Customer Insights

You currently work as a data analyst for Remitly, an American company that provides mobile payments services. One of your tasks is to analyze the transaction and customer data to draw useful insights that can inform business decisions.

Your customers table has the following schema:

Example Input:
customer_idfirst_namelast_namecountry
1JohnDoeUSA
2JaneSmithCanada
3AliAhmedUK
4MariaLopezUSA

And your transactions table is:

Example Input:
transaction_idcustomer_iddateamount
122022-01-01100.00
212022-02-15250.00
332022-03-20150.00
422022-04-05200.00
542022-05-31300.00

Write a SQL query to get total transaction amount for each customer and join the output with customer information.

Answer:


With the above PostgreSQL query, we join the and tables on the column which is common to both tables. We then group the result by the customer's ID, first name, last name, and country, and calculate the sum of transaction amounts for each group, effectively giving us the total transaction amount for each customer.

Example Output:
customer_idfirst_namelast_namecountrytotal_amount
1JohnDoeUSA250.00
2JaneSmithCanada300.00
3AliAhmedUK150.00
4MariaLopezUSA300.00

Because joins come up so often during SQL interviews, take a stab at an interactive SQL join question from Spotify: Spotify JOIN SQL question

Remitly SQL Interview Tips

The best way to prepare for a Remitly SQL interview is to practice, practice, practice. In addition to solving the earlier Remitly SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Facebook, Google and unicorn tech startups. DataLemur Question Bank

Each SQL question has multiple hints, step-by-step solutions and best of all, there's an interactive SQL code editor so you can easily right in the browser your query and have it executed.

To prep for the Remitly SQL interview you can also be helpful to practice interview questions from other tech companies like:

However, if your SQL skills are weak, forget about going right into solving questions – go learn SQL with this DataLemur SQL tutorial.

Free SQL tutorial

This tutorial covers things like Union vs. UNION ALL and filtering on multiple conditions using AND/OR/NOT – both of which show up often during SQL job interviews at Remitly.

Remitly Data Science Interview Tips

What Do Remitly Data Science Interviews Cover?

Besides SQL interview questions, the other question categories covered in the Remitly Data Science Interview are:

Remitly Data Scientist

How To Prepare for Remitly Data Science Interviews?

The best way to prepare for Remitly Data Science interviews is by reading Ace the Data Science Interview. The book's got:

  • 201 Interview Questions from tech companies like Netflix, Google, & Airbnb
  • A Crash Course covering SQL, Product-Sense & ML
  • Amazing Reviews (900+ reviews, 4.5-star rating)

Acing Data Science Interview