9 Adyen SQL Interview Questions (Updated 2024)
At Adyen, SQL is used to query their internal Graph Database. Data Analysts and Data Scientists analyze transactional data for fraud detection, and manage customer data for personalized marketing strategies. That's why they almost always evaluates jobseekers on SQL questions during interviews for Adyen Data Science and Data Engineering positions.
As such, to help you prepare for the Adyen SQL interview, we've collected 9 Adyen SQL interview questions – how many can you solve?
9 Adyen SQL Interview Questions
SQL Question 1: Calculate the total revenue per merchant per month
At Adyen, we process countless transactions from various merchants every day. We're interested in knowing the total revenue we generate from each merchant on a monthly basis. We have a table that stores all the transaction information - this includes the , , and . The is the total transaction value that is processed through us.
Write a SQL query using window functions to calculate the total revenue generated per merchant per month.
Here is a small sample of data from the table for this SQL problem:
Example Input:
| transaction_id | merchant_id | transaction_date | amount |
|---|---|---|---|
| 1 | 5001 | 01/15/2022 | 100.00 |
| 2 | 5002 | 01/20/2022 | 500.00 |
| 3 | 5001 | 01/30/2022 | 300.00 |
| 4 | 5002 | 02/10/2022 | 200.00 |
| 5 | 5001 | 02/20/2022 | 100.00 |
We want to receive the output in this format:
Example Output:
| mth | year | merchant_id | total_revenue |
|---|---|---|---|
| 1 | 2022 | 5001 | 400.00 |
| 1 | 2022 | 5002 | 500.00 |
| 2 | 2022 | 5001 | 100.00 |
| 2 | 2022 | 5002 | 200.00 |
Answer:
Here's a SQL query that achieves this:
In this query, we're leveraging the Window Function's ability to calculate an aggregate (SUM) over a specified partition. The clause works similarly to , but it retains the original row-level information. It's grouping results by , and also by the Month and Year from .
To solve another window function question on DataLemur's free interactive SQL code editor, solve this Google SQL Interview Question:
SQL Question 2: Calculate Average Transaction Amount
As a part of your role in Adyen, a global payment company, you are required to find out the average transaction amount per merchant in the previous quarter to spot the highest earning merchants and sustain business relations. Build a SQL query to solve this problem.
Please find below the sample data:
Example Input:
| transaction_id | merchant_id | transaction_date | transaction_amount |
|---|---|---|---|
| 1001 | 501 | 04/10/2022 00:00:00 | 100.50 |
| 1002 | 502 | 04/15/2022 00:00:00 | 350.00 |
| 1003 | 501 | 05/01/2022 00:00:00 | 80.00 |
| 1004 | 503 | 05/25/2022 00:00:00 | 550.00 |
| 1005 | 502 | 06/15/2022 00:00:00 | 450.00 |
Answer:
The SQL block which solves the problem is as follows:
In this solution, we would first filter the transactions that happened in the last quarter (From April 1 to June 30). Then we group these transactions by and calculate average (AVG function in SQL) transaction amount for each merchant. The results are ordered in descending order to show the merchants with highest average transaction amount first.
The most similar questions to your are:
- "Highest-Grossing Items" by Amazon: It also deals with finding highest earning items like your question.
- "Repeated Payments" by Stripe: This question also requires understanding of transaction data similar to your question.
To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for requiring calculations on earnings or this Stripe Repeated Payments Question which is similar for involving transaction data.
SQL Question 3: How does differ from ?
is used to combine the output of multiple statements into one big result!
Suppose you were a Data Analyst at Adyen working on a Sales Analytics project, and had data on sales leads from both the company's Salesforce CRM and it's legacy Hubspot CRM. To write a query to analyze leads created before 2023 started, across both CRMs, you could use in the following way:
filters out duplicates, so each email/job title/company only shows up once (even if it's in both Salesforce and HubSpot CRMs).
On the other hand, the operator does NOT filter out duplicates, so if a lead shows up in both CRMs, it'll be listed twice in the output of .
SQL Question 4: Calculate the Average Daily Processing Volume for Each Merchant
As an assistant in Adyen, a global payment company, you are tasked to generate a report which shows the average daily volume of transactions for each merchant, over a certain month. The volume of a transaction is based on its amount.
The table contains the following columns:
- (integer)
- (Date)
- (integer)
- (float) - The amount of money processed in the transaction.
Generate a query that will output each merchant's ID, the month, and their respective average daily transaction volume for that month.
Example Input:
| transaction_id | date | merchant_id | amount |
|---|---|---|---|
| 101 | 2022-08-01 | 1 | 50000 |
| 102 | 2022-08-02 | 1 | 70000 |
| 103 | 2022-08-01 | 2 | 80000 |
| 104 | 2022-08-03 | 1 | 55000 |
| 105 | 2022-08-03 | 2 | 89000 |
| 106 | 2022-08-02 | 2 | 65000 |
Example Output:
| month | merchant_id | avg_daily_volume |
|---|---|---|
| 8 | 1 | 58333.33 |
| 8 | 2 | 78000.00 |
Answer:
In this query, we are using the statement to divide the rows in our table into groups, based on the month and merchant_id. Then with each group, we average the transaction amount (volume) to get their respective average daily transaction volume. The function ignores NULL values. EXTRACT function is used to get the month from the date column.
Please note that this solution assumes that there are transactions every day. If that is not the case an additional subquery would be needed to calculate the number of days with transactions for each merchant, and that value should be used to divide the SUM(amount).
SQL Question 5: When considering database normalization, how do 1NF, 2NF, and 3NF differ from one another?
Normalization is the process of dividing a larger table into smaller tables, to eliminate redundancy and dependency. Although there are 5 levels of normalization (normal forms), the 3 most important ones that you should know for the Adyen SQL interview are:
- First Normal Form (1NF): Remove a table's duplicate columns, and make sure each value in the column is a singular value (no containers or lists of data). Each row of table should have a unique identifier as well.
- Second Normal Form (2NF): A table is in 2NF if it meets all requirements of the 1NF the non-key columns are dependent only on the primary key. You do this by separating subsets of columns subsets, and associating the tables by using primary/foreign keys.
- Third Normal Form (3NF): The table should be in 2NF and there shouldn't be any dependency on any non-key attributes (meaning a primary key should be the only thing needed to identify a row).
SQL Question 6: Identify Customers from Specific Country
As an Adyen analyst, you are provided with a database of customer records. Your task is to find all customers that have a billing address in "Netherlands". Keep in mind that the column storing their address is in the format Street, City, Country.
Sample Input: 'customers'
| customer_id | first_name | last_name | billing_address |
|---|---|---|---|
| 001 | John | Doe | 123 Anywhere St, Amsterdam, Netherlands |
| 002 | Jane | Smith | 456 Another Rd, New York, USA |
| 003 | Sam | Lee | 789 Somewhere Ave, Rotterdam, Netherlands |
| 004 | Mike | Taylor | 321 Everywhere Blvd, London, UK |
| 005 | Emma | Brown | 654 Nowhere Ln, Utrecht, Netherlands |
Sample Output:
| customer_id | first_name | last_name | billing_address |
|---|---|---|---|
| 001 | John | Doe | 123 Anywhere St, Amsterdam, Netherlands |
| 003 | Sam | Lee | 789 Somewhere Ave, Rotterdam, Netherlands |
| 005 | Emma | Brown | 654 Nowhere Ln, Utrecht, Netherlands |
Answer:
This SQL query uses the keyword to filter out all records where the ends with "Netherlands". The '%' before 'Netherlands' is a wildcard character that matches any sequence of characters. As a result, all customers that have a billing address in Netherlands are returned.
SQL Question 7: What's a foreign key?
A foreign key is a column or group of columns in a table that refers to the primary key in another table. The foreign key constraint helps maintain referential integrity between the two tables. The table with the foreign key is called the child table, while the table with the candidate key is called the parent or referenced table.
For example, consider a database with two tables: and customer_idcustomer_idcustomer_id` column in Adyen customers table.
SQL Question 8: Track Total Transactions and Total Amount per Customer
Adyen is a payments company that processes transactions for businesses. Given two tables - and , you are asked to write a SQL query that returns each customer's name, total number of transactions and total amount spent. Assume each customer can have multiple transactions but each transaction can be associated with only one customer.
Consider the following tables:
Example Input:
| transaction_id | customer_id | amount | transaction_date |
|---|---|---|---|
| 1001 | 501 | 150 | 01/20/2022 |
| 1002 | 502 | 200 | 02/15/2022 |
| 1003 | 501 | 100 | 01/21/2022 |
| 1004 | 503 | 250 | 02/10/2022 |
| 1005 | 501 | 75 | 01/20/2022 |
Example Input:
| customer_id | name |
|---|---|
| 501 | John |
| 502 | Sara |
| 503 | Daniel |
| 504 | Monica |
Answer:
This SQL query joins the and tables on the . is then used to group the data by customer. The aggregate functions and are used to calculate the total number of transactions and the total amount spent by each customer, respectively. The clause is used to sort the data first by total transactions in descending order, and then by total amount in descending order in case of a tie.
Since joins come up so often during SQL interviews, try this interactive Snapchat Join SQL question:
SQL Question 9: Average Payment Volume per Hour
Adyen is a global payment processor. A common need for such a company would be to analyze payment volume over certain periods. Given a table of transactions, can you write a SQL query that returns the average payment volume per hour of the day?
Assume you are given a table, , with the following schema:
Example Input:
| transaction_id | timestamp | payment_volume |
|---|---|---|
| 1 | 2022-05-05 03:00:00 | 350.00 |
| 2 | 2022-05-05 04:15:00 | 240.00 |
| 3 | 2022-05-05 04:30:00 | 150.00 |
| 4 | 2022-05-06 05:00:00 | 560.00 |
| 5 | 2022-05-06 05:05:00 | 300.00 |
You are expected to produce output in the following format, where is a 24-hour format and is rounded to two decimal places:
Example Output:
| hour_of_day | avg_payment_volume |
|---|---|
| 3 | 350.00 |
| 4 | 195.00 |
| 5 | 430.00 |
Answer:
This query first extracts the hour of the day from the timestamp for each transaction. It then groups by the hour of the day and calculates the average payment volume per hour. The result is ordered by to make the output intuitive.
Adyen SQL Interview Tips
The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews.
In addition to solving the above Adyen SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google).
Each SQL question has multiple hints, fully explained answers along with a discussion board to see how others solved it and crucially, there's an interactive coding environment so you can right in the browser run your SQL query and have it executed.
To prep for the Adyen SQL interview it is also helpful to practice SQL questions from other tech companies like:
In case your SQL query skills are weak, don't worry about going right into solving questions – strengthen your SQL foundations with this DataLemur SQL Tutorial.
This tutorial covers SQL concepts such as handling dates and WHERE vs. HAVING – both of which come up often in Adyen SQL assessments.
Adyen Data Science Interview Tips
What Do Adyen Data Science Interviews Cover?
In addition to SQL interview questions, the other question categories covered in the Adyen Data Science Interview are:
- Statistics Interview Questions
- Coding Questions in Python or R
- Product-Sense Questions
- ML Modelling Questions
- Resume-Based Behavioral Questions
How To Prepare for Adyen Data Science Interviews?
To prepare for Adyen Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from FAANG, tech startups, and Wall Street
- a refresher covering Product Analytics, SQL & ML
- over 900+ 5-star reviews on Amazon