8 H&R Block SQL Interview Questions (Updated 2024)
H&R Block employees write SQL queries daily for analyzing tax-related data and optimizing client financial data queries. That's why H&R Block often tests SQL questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.
To help you prep for the H&R Block SQL interview, here’s 8 H&R Block SQL interview questions in this blog.
8 H&R Block SQL Interview Questions
SQL Question 1: Identify Power Users for H&R Block
H&R Block is a company that offers tax preparation services. One important metric for them could be the number of tax returns a customer files in a given year using their service, presumably, customers filing more tax returns are more valuable to the business.
Please write a SQL query to find all customers who have filed more than 10 tax returns within the year 2022. The output should include the user ID, user full name, and the total number of tax returns they filed within 2022.
Sample Input:
| user_id | first_name | last_name |
|---|---|---|
| 101 | John | Doe |
| 102 | Jane | Smith |
| 103 | Jim | Brown |
| 104 | Jill | White |
Sample Input:
| tax_id | user_id | filing_date |
|---|---|---|
| 2001 | 101 | 02/01/2022 |
| 2002 | 101 | 04/15/2022 |
| 2003 | 101 | 09/21/2022 |
| 2004 | 102 | 03/30/2022 |
| 2005 | 103 | 04/14/2022 |
| 2006 | 103 | 10/15/2022 |
| 2007 | 104 | 04/15/2022 |
| 2008 | 101 | 12/04/2022 |
| 2009 | 101 | 11/23/2022 |
| 2010 | 101 | 07/06/2022 |
| 2011 | 101 | 05/27/2022 |
| 2012 | 101 | 05/12/2022 |
Answer:
The SQL query will look like this:
Explanation:
This query joins the table with the table on the field, then it filters out the tax returns that were not filed in the year 2022. It groups the results by user and counts the number of tax returns each user has filed. Finally, it filters out the users who have filed 10 or fewer tax returns.
To practice a similar customer analytics SQL question where you can solve it right in the browser and have your SQL solution instantly graded, try this Walmart SQL Interview Question:
Read more about HR&R Block's Data Security, since it's a project that their data analysts/scientists work on!
SQL Question 2: Top Three Salaries
Imagine there was a table of H&R Block employee salary data. Write a SQL query to find the top 3 highest earning employees in each department.
H&R Block Example Input:
| employee_id | name | salary | department_id |
|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 |
| 2 | Daniel Rodriguez | 2230 | 1 |
| 3 | Olivia Smith | 2000 | 1 |
| 4 | Noah Johnson | 6800 | 2 |
| 5 | Sophia Martinez | 1750 | 1 |
| 8 | William Davis | 6800 | 2 |
| 10 | James Anderson | 4000 | 1 |
Example Input:
| department_id | department_name |
|---|---|
| 1 | Data Analytics |
| 2 | Data Science |
Example Output:
| department_name | name | salary |
|---|---|---|
| Data Analytics | James Anderson | 4000 |
| Data Analytics | Emma Thompson | 3800 |
| Data Analytics | Daniel Rodriguez | 2230 |
| Data Science | Noah Johnson | 6800 |
| Data Science | William Davis | 6800 |
Check your SQL query for this question directly within the browser on DataLemur:
Answer:
We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.
If the solution above is confusing, you can find a step-by-step solution here: Top 3 Department Salaries.
SQL Question 3: What's the main difference between ‘BETWEEN’ and ‘IN’ operators?
is used to select rows that match a range of values, whereas the operator checks for values in a specified list of values.
For example, say you were a Data Analyst at H&R Block and had a table of advertising campaign data.
To find campaigns with between 10k in spend, you could use BETWEEN`:
To find ad campaigns that were run on Facebook and Google's Display Network, you could use :
SQL Question 4: Compute Average Service Rating per Tax Professional by Month
As an analyst for H&R Block, your task is to compute the average service rating received by each Tax Professional on a month-by-month basis over the past year. This data will be used to gauge employee performance and client satisfaction over time.
Example Input:
| rating_id | pro_id | client_id | rating_date | rating |
|---|---|---|---|---|
| 100 | 1 | 357 | 07/01/2022 00:00:00 | 5 |
| 200 | 2 | 562 | 07/10/2022 00:00:00 | 4 |
| 300 | 1 | 981 | 07/11/2022 00:00:00 | 3 |
| 400 | 3 | 783 | 08/18/2022 00:00:00 | 4 |
| 500 | 2 | 462 | 09/05/2022 00:00:00 | 5 |
Example Output:
| month | year | pro_id | avg_rating |
|---|---|---|---|
| 7 | 2022 | 1 | 4.00 |
| 7 | 2022 | 2 | 4.00 |
| 8 | 2022 | 3 | 4.00 |
| 9 | 2022 | 2 | 5.00 |
Answer:
This query uses a window function to compute the average rating per Tax Professional per month over the past year. The window function works by creating a 'window' of rows around each current row based upon the PARTITION criteria; in this case, , , and . It then computes the average rating over this set of rows. The result is an average rating for each Tax Professional for each month. Finally, the records are ordered by year, month, and then .
To solve another window function question on DataLemur's free interactive coding environment, solve this Google SQL Interview Question:
SQL Question 5: Can you explain the purpose of UNION?
The operator merges the output of two or more statements into a single result set. The two SELECT statements within the UNION must have the same number of columns and the data types of the columns are all compatible.
For example, if you were a Data Analyst on the marketing analytics team at H&R Block, this statement would return a combined result set of both H&R Block's Google and Facebook ads that have more than 300 impressions:
SQL Question 6: Database Design for H&R Block's Client Tax Returns
H&R Block, a tax preparation service, needs a database to track all tax returns processed by various clients. Assume the following:
- A client can have multiple tax returns.
- Each tax return contains various forms with their respective data.
- Different clients may process tax returns in different states.
- Certain forms or data might only be relevant for returns in specific states.
Design a database schema that enables H&R Block to store, retrieve and analyze data efficiently. Also, write a SQL query to find out the total number of clients who have processed their tax returns in a specific state last year.
Sample Table:
| client_id | client_name |
|---|---|
| 123 | John Smith |
| 427 | Alice Johnson |
| 789 | Robert Brown |
Sample Table:
| return_id | client_id | year | state |
|---|---|---|---|
| 17001 | 123 | 2021 | CA |
| 19852 | 427 | 2021 | TX |
| 33001 | 123 | 2021 | NY |
Sample Table:
| form_id | return_id | form_name | value |
|---|---|---|---|
| 12001 | 17001 | W2 | $50,000 |
| 12982 | 19852 | 1099-INT | $2,000 |
| 26901 | 33001 | 1040 | $70,000 |
| 26502 | 33001 | State | $5,000 |
Answer:
This query returns the total number of distinct clients who processed their tax returns in California during the year 2021. The keyword is necessary to ensure that each client is counted only once, even if they processed multiple tax returns.
SQL Question 7: What do primary keys do?
The primary key of a table is a column or set of columns that serves as a unique identifier for each row. It ensures that all rows are distinct and does not allow null values.
For example, say you had stored some Facebook ad campaign data that H&R Block ran:
The CampaignID column is used to uniquely identify each row in the table, and the constraint ensures that there are no duplicate CampaignID values. This helps to maintain the accuracy of the data by preventing duplicate rows. The primary key is also an important part of the table because it enables you to easily identify and reference specific campaigns in your Facebook Ad data. You can use it to join to other tables in the database, such as a table with data on the results of the campaigns.
SQL Question 8: Filter HR Block Customers Based on Income and Transaction History
H&R Block, a tax preparation company, is launching a new premium service for high-income earners who have used their services in the past two years. To identify potential customers, they would like you to write a query to filter their database for customers who have an annual income greater than $100,000, and have had at least two transactions in the past two years.
The two relevant tables are and . The table has a record for each customer, while the table keeps track of each transaction a customer has.
Sample Data:
Table:
| customer_id | first_name | last_name | annual_income |
|---|---|---|---|
| 101 | John | Smith | 120000 |
| 102 | Jane | Doe | 95000 |
| 103 | Jim | Brown | 105000 |
| 104 | Jill | Johnson | 80000 |
Table:
| transaction_id | customer_id | transaction_date | amount |
|---|---|---|---|
| 201 | 101 | 06/18/2020 | 350 |
| 202 | 101 | 04/15/2021 | 400 |
| 203 | 102 | 05/10/2019 | 300 |
| 204 | 103 | 03/20/2020 | 350 |
| 205 | 103 | 04/15/2021 | 400 |
| 206 | 104 | 07/18/2019 | 300 |
Answer:
This query first finds the customers who have at least two transactions in the past two years by grouping transactions by the and filtering groups with at least two transactions. It then joins this list of customer IDs with the customers who earn over $100,000 annually. The result is a list of and for high earning customers who have at least two transactions in the past two years.
How To Prepare for the H&R Block SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the H&R Block SQL interview is to solve as many practice SQL interview questions as you can!
Beyond just solving the earlier H&R Block SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google).
Each problem on DataLemur has hints to guide you, detailed solutions and best of all, there's an online SQL coding environment so you can instantly run your SQL query and have it executed.
To prep for the H&R Block SQL interview it is also wise to practice SQL questions from other payment & credit companies like:
However, if your SQL query skills are weak, don't worry about jumping right into solving questions – improve your SQL foundations with this interactive SQL tutorial.
This tutorial covers topics including handling strings and LEFT vs. RIGHT JOIN – both of which show up frequently during SQL interviews at H&R Block.
H&R Block Data Science Interview Tips
What Do H&R Block Data Science Interviews Cover?
Besides SQL interview questions, the other question categories to practice for the H&R Block Data Science Interview are:
- Probability & Stats Questions
- Python or R Coding Questions
- Product-Sense and Business Case Questions
- ML Modelling Questions
- Behavioral Interview Questions
How To Prepare for H&R Block Data Science Interviews?
To prepare for H&R Block Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions sourced from Google, Microsoft & tech startups
- a crash course on SQL, AB Testing & ML
- over 1000+ 5-star reviews on Amazon