# 11 Workiva SQL Interview Questions (Updated 2024)

Updated on

March 2, 2024

At Workiva, SQL is used across the company for analyzing complex financial data sets and for helping companies generate ESG reports and insights. Because of this, Workiva almost always asks SQL questions in interviews for Data Science, Analytics, and & Data Engineering jobs.

As such, to help you prepare for the Workiva SQL interview, we've collected 11 Workiva SQL interview questions – can you answer each one?

## 11 Workiva SQL Interview Questions

### SQL Question 1: Calculate the Monthly Average Rating for Each Product

Workiva is a company that provides cloud solutions for enterprises to manage and report their financial data. They also sell a variety of software products to different customers. Your task is to analyze the performance of these software products based on user reviews.

The company maintains a 'reviews' table that logs user reviews for different software products. Each row in the table corresponds to a unique review, and captures the 'review_id', 'user_id', 'submit_date', 'product_id', and 'stars' (the rating given by the user).

Given this 'reviews' table, write a SQL query to calculate the average rating (stars) for each product on a monthly basis. Report the results in a table with the following columns: 'mth' (month of the review), 'product_id', and 'avg_stars' (average rating for the product in that month).

##### Example Input:
review_iduser_idsubmit_dateproduct_idstars
617112306/08/2022 00:00:00500014
780226506/10/2022 00:00:00698524
529336206/18/2022 00:00:00500013
635219207/26/2022 00:00:00698523
451798107/05/2022 00:00:00698522

This PostgreSQL query first extracts the month from the 'submit_date' using the EXTRACT function. It then groups the data by the month and 'product_id', and for each group, it calculates the average rating. The resulting table is ordered by 'mth' and 'product_id'.

##### Example Output:
mthproduct_idavg_stars
6500013.50
6698524.00
7698522.50

Pro Tip: Window functions are a frequent SQL interview topic, so practice all the window function problems on DataLemur

### SQL Question 2: Sales and Returns Analysis

Workiva is a company that sells hundreds of different software products around the world. As a data analyst at Workiva, you are tasked with analyzing their sales and returns to understand which products are performing best. The data is stored in two tables: and . Here is how the schema looks like:

##### Example Input:
sale_idproduct_idcustomer_idsale_dateunits_soldprice_per_unit
13729871542021/11/1510300
31444568122021/10/205400
87393215962021/07/2620100
95021232312021/12/307200
45821238122022/01/055200
##### Example Input:
return_idsale_idreturn_dateunits_returned
487113722021/12/302
511687392021/08/155
366295022022/01/101

Your task is to create a SQL query that returns a table showing the total sales and total returns for each product by month. It should include the columns , , and . The column should be in the format 'YYYY-MM'.

In this query, we are using the function to handle values that might arise in case a product has sales but no returns in a month (or vice versa). We are also joining and tables on to track the returns against the right product. We are calculating the total sales and total returns based on and respectively. We use to group the results by month.

### SQL Question 3: What is the purpose of the constraint, and when might it be helpful to use it?

{#Question-3}

The CHECK constraint is used to set a rule for the data in a column. If a row is inserted or updated and the data in the column does not follow the rule specified by the CHECK constraint, the operation will be unsuccessful.The CHECK constraint is often used in conjunction with other constraints, such as NOT NULL or UNIQUE.

You might consider implementing the CHECK constraint in your database if you want to ensure that certain data meets specific conditions. This can be helpful for maintaining the quality and reliability of your data.

For example, you might use a CHECK constraint to ensure that a column contains only positive numbers, or that a date is within a certain range.

### SQL Question 4: Filter Customers Based on Subscription Status and Signup Date

As a data analyst at Workiva, your task is to manage the customer records database. The marketing team needs a list of customers for a promotional campaign. Specifically, they are interested in customers who:

• Registered after January 1, 2020;
• Have an active subscription;
• And are located in the USA.

Your query should return a list of users who meet all the above conditions, including their username, email address, signup date, subscription type, and location.

Please use the table as a reference.

##### Example Input:
102emilydaviesemilydav@gmail.com01/20/2020ActiveBasicUSA
103johndoejohndoe@yahoo.com03/01/2021InactiveProUK
104anacostaanacosta@hotmail.com04/10/2020ActiveProUSA

This PostgreSQL query filters the table based on the requirement: customers who signed up after January 1, 2020, have active subscription status, and their location is the USA. Therefore, based on the sample data, the output would be:

##### Example Output:
emilydaviesemilydav@gmail.com01/20/2020BasicUSA
anacostaanacosta@hotmail.com04/10/2020ProUSA

### SQL Question 5: Could you provide a list of the join types in SQL and explain what each one does?

Using a join in SQL, you can retrieve data from multiple tables and merge the results into a single table.

In SQL, there are four distinct types of JOINs. To demonstrate each kind, Imagine you had two database tables: an table that contains data on Google Ads keywords and their bid amounts, and a table with information on product sales and the Google Ads keywords that drove those sales.

• : An INNER JOIN retrieves rows from both tables where there is a match in the shared key or keys. For example, an INNER JOIN between the table and the table could be performed using the keyword column as the shared key. This would retrieve only the rows where the keyword in the table matches the keyword in the table.

• : A LEFT JOIN retrieves all rows from the left table (in this case, the table) and any matching rows from the right table (the Sales table). If there is no match in the right table, values will be returned for the right table's columns.

• : A RIGHT JOIN retrieves all rows from the right table (in this case, the Sales table) and any matching rows from the left table (the table). If there is no match in the left table, values will be returned for the left table's columns.

• : A FULL OUTER JOIN retrieves all rows from both tables, regardless of whether there is a match in the shared key or keys. If there is no match, values will be returned for the columns of the non-matching table.

### SQL Question 6: Finding the Average Duration of Projects

As a project manager at Workiva, you are interested in tracking how long projects are taking. You want to find the average duration of all projects in each department.

#### Example Input:

##### Example Output:

In the above query, we use the to calculate the duration of each project in days. The AVG function then calculates the average duration for each department. The output gives us the average project duration in days for each department.

To practice a very similar question try this interactive Amazon Highest-Grossing Items Question which is similar for needing to perform calculations for grouped data or this Facebook Average Post Hiatus (Part 1) Question which is similar for needing to calculate durations between dates.

### SQL Question 7: Consider unique indexes, and non-unique indexes. What are some similarities and differences?

Some similarities between unique and non-unique indexes include:

1. Both types improve the performance of SQL queries by providing a faster way to lookup the desired data.
2. Both types use an additional data structure to store the indexed data, which requires additional storage space which impacts write performance.
3. Both types of indexes can be created on one or more columns of a table.

Some differences between unique and non-unique indexes include:

1. A unique index enforces the uniqueness of the indexed columns, meaning that no duplicate values are allowed in the indexed columns. A non-unique index allows duplicate values in the indexed columns.
2. A unique index can be used to enforce the primary key of a table, but a non-unique index cannot.
3. A unique index can have a maximum of one NULL value in the indexed columns, but a non-unique index can have multiple NULLs

### SQL Question 8: Click-Through Rate Analysis for Digital Ads

Workiva uses various digital advertising platforms to promote their products. Each ad click redirects the user to a landing page, and if the user finds the product interesting, they might add the product to their cart.

We have two tables here: which records every click on the ads with the ad_id, user_id, click_time and which records every product added to the cart with the user_id, add_time and product_id.

Calculate the Click-through Conversion rate, which is the number of users who added the product to the cart after clicking on an ad, divided by the total number of clicks each ad has received.

##### Example Input:
A112306/08/2022 00:00:00
A226506/10/2022 00:00:00
A198206/15/2022 00:10:00
A219207/26/2022 00:15:00
A198107/05/2022 00:00:00
##### Example Input:
12306/08/2022 00:05:00P50001
26506/10/2022 00:05:00P69852
36206/18/2022 00:00:00P50001
19207/26/2022 00:20:00P69852
98206/15/2022 00:15:00P50001
##### Example Output:
A10.666
A21.000

The CTEs are used to gather the click and add actions per ad. We join the two tables based on the user_id and make sure that click_time is less or equal to add_time. In the final query, we obtain the conversion rate by dividing the number of adds by the number of clicks per ad. The LEFT JOIN ensures we get all ads, even those without any add_to_cart action.

To practice another question about calculating rates, try this SQL interview question from TikTok within DataLemur's online SQL coding environment:

### SQL Question 9: Average Amount of Licenses Sold per Month

Workiva, a leading provider of enterprise cloud solutions, sells software licenses to different companies. Write an SQL query that provides a monthly breakdown for the average amount of licenses sold per month.

##### Example Output:

This query extracts the month from the purchase date of each record in the table and groups the records by this month value. For each group of records (i.e., each month), it calculates the average number of licenses sold rounding to two decimal places. The results are returned in ascending order by month.

### SQL Question 10: Can you explain the distinction between cross join and natural join?

Imagine you are organizing a party and have two database tables: one table of people you want to invite and another list of food items you want to serve.

A cross join would be like inviting every person on your list to the party and serving them every food item on the menu, regardless of whether they like the food or not. So, if you had 10 people on your invite list and 5 food items on the menu, you would generate all 50 different combinations of people and food (10 x 5 = 50).

On the other hand, a natural join would be like inviting only the people who like the food items on the menu (based on doing a inner/left/right/outer JOIN on a common key like ).

### SQL Question 11: Filter Customer Records

Workiva's customers come from a wide range of industries, including technology, education, healthcare, and more. As an SQL developer, you'll often be asked to filter customer data based on certain criteria.

Consider the table below. Write a SQL query to find customers whose begins with the letter 'W', hailing from the 'Technology' industry and having their domain as '@workiva.com'.

##### Example Input:
customer_idfirst_namelast_namecontact_emailindustrycompany_name
7802AliceSmithalice.smith@othertech.comTechnologyWeboTech
5293JohnWhitejohn.white@workiva.comTechnologyWorkiva
6352AveryJohnsonavery.johnson@gmail.comHealthcareWellnessCorp
4517RachelJamesrachel.james@workiva.comEducationWorldwide Educators
##### Example Output:
customer_idfirst_namelast_namecontact_emailindustrycompany_name
5293JohnWhitejohn.white@workiva.comTechnologyWorkiva

This query filters the table to return records where the begins with the letter 'W', the is 'Technology', and the ends with '@workiva.com'. This allows us to find customers who work in the technology industry for companies with names starting with 'W' and use their Workiva email as the contact email.

### Workiva SQL Interview Tips

The key to acing a Workiva SQL interview is to practice, practice, and then practice some more! Besides solving the earlier Workiva SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like Facebook, Google and unicorn tech startups.

Each interview question has multiple hints, step-by-step solutions and most importantly, there is an interactive coding environment so you can instantly run your SQL query and have it graded.

To prep for the Workiva SQL interview you can also be wise to solve interview questions from other tech companies like:

But if your SQL query skills are weak, forget about going right into solving questions – strengthen your SQL foundations with this free SQL tutorial.

This tutorial covers SQL topics like functions like SUM()/COUNT()/AVG() and INTERCEPT/EXCEPT – both of which come up routinely during Workiva interviews.

### Workiva Data Science Interview Tips

#### What Do Workiva Data Science Interviews Cover?

Besides SQL interview questions, the other question categories tested in the Workiva Data Science Interview are:

• Statistics and Probability Questions
• Coding Questions in Python or R
• Data Case Study Questions
• Machine Learning Questions
• Resume-Based Behavioral Questions

#### How To Prepare for Workiva Data Science Interviews?

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

• 201 Interview Questions from Microsoft, Amazon & startups
• A Crash Course on SQL, Product-Sense & ML
• Amazing Reviews (900+ reviews, 4.5-star rating)