At Braze, SQL does the heavy lifting for analyzing user interaction data for client marketing strategies, and for creating customer segements. That's the reason behind why Braze LOVES to ask SQL query questions in interviews for Data Science, Analytics, and & Data Engineering jobs.
Thus, to help you practice for the Braze SQL interview, we've collected 9 Braze SQL interview questions – can you solve them?
Braze is a customer engagement platform that helps businesses analyze their user behavior and perform targeted marketing campaigns. In this scenario, suppose Braze considers a user a 'Power User' if they have sent more than 500 emails in the last 30 days. Write a PostgreSQL query to identify these power users from the customer database.
activity_id | user_id | activity_type | activity_count | activity_date |
---|---|---|---|---|
1 | 123 | Email Sending | 500 | 2022-08-01 00:00:00 |
2 | 256 | Email Sending | 450 | 2022-08-02 00:00:00 |
3 | 123 | Email Sending | 250 | 2022-08-02 00:00:00 |
4 | 362 | Email Sending | 600 | 2022-08-04 00:00:00 |
5 | 362 | Email Sending | 150 | 2022-08-05 00:00:00 |
The above query selects users from the table who sent emails in the past 30 days. It groups the result by , and sums up the for each user. The clause then filters out users who have sent more than 500 emails, thus identifying our 'Power Users'.
To practice a super-customer analysis question on DataLemur's free online SQL code editor, try this Microsoft Azure Cloud SQL Interview Question:
As a Data Analyst at Braze, you are tasked to analyze user engagement over time. You are given a table, , where each row represents an individual user event, like a user opening an app. You are asked to write a SQL query to calculate the rolling 7-day count of user events by for each day.
event_id | user_id | event_date | app_id |
---|---|---|---|
1257 | 123 | 2022-06-08 | 54321 |
2631 | 465 | 2022-06-10 | 98765 |
7812 | 362 | 2022-06-15 | 54321 |
9532 | 192 | 2022-06-19 | 98765 |
4517 | 981 | 2022-07-05 | 98765 |
Note: all dates are in the format .
You need to return a table that includes the date, app id, and the rolling 7-day count of user events.
Date | app_id | 7_day_event_count |
---|---|---|
2022-06-14 | 54321 | 2 |
2022-06-16 | 98765 | 2 |
2022-06-20 | 54321 | 1 |
The query employs a window function to calculate the rolling 7-day count of user events for each app_id. The separates the data into partitions based on the app_id. The sorts the rows in each partition by date. The defines the window frame where it starts from the current row and goes back 6 preceding rows (representing a 7 day span).
To practice another window function question on DataLemur's free online SQL coding environment, try this Google SQL Interview Question:
Joins in SQL allow you to combine data from different tables based on a shared key or set of keys.
Four JOIN types are available in SQL. For an example of each one, say you had sales data exported from Braze's Salesforce CRM stored in a PostgreSQL database, and had access to two tables: and .
Braze is a company that specializes in customer engagement, offering an email marketing platform among other features. For this scenario, let's assume you've been asked to calculate average email open rates per campaign for the last month.
Here's some sample data:
campaign_id | campaign_name | start_date |
---|---|---|
101 | Spring Sale | 06/01/2022 00:00:00 |
102 | Summer Promotion | 06/15/2022 00:00:00 |
103 | Fall Discounts | 07/01/2022 00:00:00 |
email_id | campaign_id | sent_date | total_sent | total_opened |
---|---|---|---|---|
1001 | 101 | 06/08/2022 00:00:00 | 5000 | 1500 |
1002 | 101 | 06/10/2022 00:00:00 | 4500 | 1250 |
1003 | 102 | 06/18/2022 00:00:00 | 6000 | 1800 |
1004 | 103 | 07/02/2022 00:00:00 | 7000 | 2100 |
1005 | 103 | 07/05/2022 00:00:00 | 6900 | 2050 |
The query above joins the campaigns and emails tables on campaign_id. It then restricts the set to only emails sent last month. The average open rate is calculated by dividing the number of emails opened () by the number of emails sent () for each campaign, and then taking the average of these rates. Note that is cast as a decimal to avoid integer division, which would truncate the results.
To practice a very similar question try this interactive Facebook App Click-through Rate (CTR) Question which is similar for calculating rates based on user activities or this TikTok Signup Activation Rate Question which is similar for analyzing user engagement from a campaign.
Database normalization is helpful because it improves the performance, flexibility, and scalability of a database. Some specific benefits of normalization include:
Reducing Redundancy: Normalization helps to minimize redundancy by breaking down a larger general table into smaller, more specific tables. This can often reduce the amount of data that needs to be accessed for particular queries.
Improving Data Integrity: Normalization helps to ensure the integrity of the data by minimizing the risk of data inconsistencies and anomalies. It does this by establishing clear relationships between the tables via primary and foreign keys.
Increasing 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 (and change is basically constant at Braze!)
Improving 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.
Easier Data Modeling: Normalization can make it easier to model the data in a logical and intuitive way, since less random data is commingled together, which improves the overall database design.
Braze is a customer engagement platform that powers relevant and memorable experiences between consumers and the brands they love. For this question, consider a scenario where Braze sends out marketing campaigns and monitors user engagement. The task is to find out the average user engagement per campaign over a specific time period.
campaign_id | campaign_name | launch_date |
---|---|---|
101 | Summer Sale | 06/01/2022 00:00:00 |
102 | Back to School | 07/01/2022 00:00:00 |
103 | Winter Sale | 12/01/2022 00:00:00 |
engagement_id | campaign_id | user_id | engagement_date | engagement_time |
---|---|---|---|---|
2356 | 101 | 1256 | 06/10/2022 00:00:00 | 30 |
3234 | 101 | 5124 | 06/12/2022 00:00:00 | 20 |
9873 | 102 | 9852 | 07/22/2022 00:00:00 | 10 |
3612 | 103 | 1261 | 12/05/2022 00:00:00 | 15 |
5620 | 103 | 9021 | 12/10/2022 00:00:00 | 25 |
campaign_name | avg_engagement_time |
---|---|
Summer Sale | 25 |
Back to School | 10 |
Winter Sale | 20 |
This query joins the and tables based on the . It then groups the data by the . The function is used to calculate the average for each campaign. This allows Braze to understand user engagement per campaign, which can help guide their campaign strategies.
To find records in one table that aren't in another, you can use a and check for values in the right-side table.
Here is an example using two tables, Braze employees and Braze managers:
This will return all rows from Braze employees where there is no matching row in managers based on the column.
You can also use the operator in PostgreSQL and Microsoft SQL Server to return the records that are in the first table but not in the second. Here is an example:
This will retrieve all rows from employees that do not appear in managers. The operator works by retreivingthe rows that are returned by the first query, but not by the second.
Please note that is not supported by all DBMS systems, such as MySQL and Oracle (however, you can use the operator to achieve a similar outcome).
For the following two tables: (containing information about each customer) and (containing customer interaction logs with Braze), identify customers that interacted with any product that contains the word "Braze" in its name, between the dates 1st January 2022 and 31st December 2022.
customer_id | first_name | last_name | signup_date |
---|---|---|---|
123 | John | Doe | 01/01/2021 |
456 | Jane | Smith | 16/08/2021 |
789 | Robert | Johnson | 25/11/2021 |
interaction_id | customer_id | interaction_date | product |
---|---|---|---|
50001 | 123 | 06/08/2022 | Braze CRM |
69852 | 456 | 06/10/2022 | Analytics software |
52149 | 789 | 06/18/2022 | Braze Messaging |
83265 | 456 | 07/17/2022 | Email Automation |
96711 | 123 | 07/25/2022 | Braze CRM |
This PostgreSQL query uses joins to combine the and tables. It applies a case-insensitive pattern search using the SQL keyword LIKE, and a date range filter, to detect customer interactions with any product that contains the word "Braze" in its name, between the dates 1st January 2022 and 31st December 2022.
You are provided with two tables: and . The table holds the basic info of each order including and . The table provides the detail information about each order, such as the , , and the the user spent on each product in each order. Your job is to calculate the average total amount that each user spent per order.
The amount should be quantified as the multiplication of the quantity and unit_price, and it should be rounded to 2 decimal places.
The tables are structured as follows:
order_id | user_id | order_date |
---|---|---|
1001 | 2001 | 2022-06-10 |
1002 | 2002 | 2022-06-12 |
1003 | 2003 | 2022-06-14 |
1004 | 2001 | 2022-06-15 |
Example Input:
order_id | product_id | quantity | unit_price |
---|---|---|---|
1001 | 4001 | 3 | 5.99 |
1001 | 4002 | 2 | 10.99 |
1002 | 4001 | 1 | 5.99 |
1003 | 4003 | 5 | 2.99 |
1004 | 4002 | 2 | 10.99 |
1004 | 4003 | 1 | 2.99 |
This query joins the and tables on the . For each , it calculates the total amount the user spent on each order (quantity times unit price) and then takes the average over all orders for that user. The average is rounded to 2 decimal places using the function.
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 earlier Braze SQL interview questions, you should also solve the 200+ SQL Interview Questions on DataLemur which come from companies like FAANG and tech startups.
Each SQL question has multiple hints, full answers and best of all, there is an online SQL coding environment so you can easily right in the browser your query and have it executed.
To prep for the Braze SQL interview it is also helpful to solve SQL questions from other tech companies like:
In case your SQL foundations are weak, don't worry about jumping right into solving questions – improve your SQL foundations with this SQL tutorial for Data Scientists & Analysts.
This tutorial covers SQL topics like WHERE vs. HAVING and finding NULLs – both of these come up frequently in SQL job interviews at Braze.
Besides SQL interview questions, the other types of problems to prepare for the Braze Data Science Interview are:
The best way to prepare for Braze Data Science interviews is by reading Ace the Data Science Interview. The book's got: