8 Tyler Technologies SQL Interview Questions (Updated 2024)
At Tyler Technologies, SQL does the heavy lifting for extracting and manipulating large datasets for the public sector, for hosting their Open Data Platform, and daily in their Data & Insights services. Because of this, Tyler Technologies LOVES to ask SQL query questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.
To help you prepare for the Tyler Technologies SQL interview, here’s 8 Tyler Technologies SQL interview questions – how many can you solve?
8 Tyler Technologies SQL Interview Questions
SQL Question 1: Analyzing Customer Support Satisfaction
Tyler Technologies provides multiple software solutions for the public sector. Let's say one of their systems is a customer support system that allows users (the public sector employees) to submit tickets whenever they encounter a software issue. And the ticket system tracks the date the ticket was submitted, the date it was resolved, the resolver's id and the rating given by the user.
Write a SQL query to calculate the average user rating and total tickets resolved per resolver per week.
The weeks should start from Monday and end on Sunday.
Example Input:
| ticket_id | submit_date | resolver_id | resolved_date | rating |
|---|---|---|---|---|
| 1001 | 06/01/2022 00:00:00 | 500 | 06/02/2022 00:00:00 | 4 |
| 1002 | 06/03/2022 00:00:00 | 510 | 06/04/2022 00:00:00 | 3 |
| 1003 | 06/06/2022 00:00:00 | 500 | 06/07/2022 00:00:00 | 5 |
| 1004 | 06/08/2022 00:00:00 | 600 | 06/10/2022 00:00:00 | 4 |
| 1005 | 06/13/2022 00:00:00 | 500 | 06/15/2022 00:00:00 | 4 |
Example Output:
| week_start | resolver_id | average_rating | tickets_resolved |
|---|---|---|---|
| 5/30/2022 | 500 | 4.50 | 2 |
| 5/30/2022 | 510 | 3.00 | 1 |
| 6/06/2022 | 500 | 5.00 | 1 |
| 6/06/2022 | 600 | 4.00 | 1 |
| 6/13/2022 | 500 | 4.00 | 1 |
Answer:
This SQL query begins by converting the 'resolved_date' to date type and then truncating it to week starting Monday. We then group by 'week_start' and 'resolver_id' and calculate the average rating and count the total tickets resolved per group. Finally we order our results by 'week_start' and 'resolver_id'. The output of this query will allow Tyler Tech to analyze the performance of their resolvers on a week-to-week basis.
Pro Tip: Window functions are a frequent SQL interview topic, so practice every window function problem on DataLemur
SQL Question 2: Municipal Services Usage Tracking for Tyler Technologies
As a technology services provider to several local municipalities, Tyler Technologies wants to monitor and assess the usage of their various services. The company is interested in knowing the monthly usage of each service by each municipality, and the average response time per service to help improve their delivery and respond to issues promptly.
Here are two tables:
Example Input:
Example Input:
The question is to find the total requests and average response time per request for each service in each municipality for every month.
Answer:
The CTE first aggregates the total requests made and total response time for each service in each municipality for every month. Then, for each row in the table, the average response time per request is calculated by dividing total response time by total requests. These results are joined with the table to include the municipality names in the final output.
SQL Question 3: What does the function do, and when would you use it?
The function can take in multiple paramaters, and returns the first input paramater that is not null. If all arguments are null, the COALESCE function will return null too.
Suppose you have a table of Tyler Technologies salespeople and the number of deals they closed. If a salesperson didn't close any deals, the data from the 3rd-party CRM system exports a NULL value.
| sales_person | closed_deals |
|---|---|
| Jason Wright | NULL |
| Drew Jackson | 3 |
| Chris Ho | NULL |
| Adam Cohen | 2 |
| Samantha Perez | 4 |
To change these NULLs to zeros, you can use the function in the following way:
This would result in the following data:
| sales_person | closed_deals |
|---|---|
| Jason Wright | 0 |
| Drew Jackson | 3 |
| Chris Ho | 0 |
| Adam Cohen | 2 |
| Samantha Perez | 4 |
SQL Question 4: Analyzing Click-Through-Rates for Digital Ads and Conversion Rates
Tyler Technologies places ads on various platforms and tracks the number of clicks each ad receives. They are interested in knowing the Click-Through Rate (CTR) which is the ratio of users who click on a specific link to the number of total users who view a page, ad, or email.
Given a table and , where has columns , , , and includes , , , , , write a PostgreSQL query that would compute the CTR for each ad and the conversion rate for each product.
Example Input:
| click_id | user_id | ad_id | click_time |
|---|---|---|---|
| 3521 | 567 | 101 | 2022-06-08 10:00:00 |
| 3522 | 265 | 102 | 2022-06-10 11:00:00 |
| 3523 | 789 | 101 | 2022-06-11 15:00:00 |
| 3524 | 987 | 103 | 2022-06-12 16:00:00 |
| 3525 | 654 | 103 | 2022-06-12 17:00:00 |
Example Input:
| view_id | user_id | product_id | add_to_cart | view_time |
|---|---|---|---|---|
| 4517 | 567 | 2001 | true | 2022-06-08 10:02:00 |
| 4518 | 265 | 2002 | false | 2022-06-10 11:02:00 |
| 4519 | 789 | 2001 | true | 2022-06-11 15:01:00 |
| 4520 | 265 | 2002 | true | 2022-06-11 20:00:00 |
| 4521 | 654 | 2001 | false | 2022-06-12 17:04:00 |
Answer:
Part 1: Click-Through Rate (CTR) for Each Ad:
Part 2: Conversion Rate for Each Product:
In the query, counts only the unique users who added the product to their cart after viewing it. Then we calculate the conversion rate as this count divided by the total unique users who viewed the ad or product.
To solve a similar problem on DataLemur's free interactive SQL code editor, attempt this SQL interview question asked by Facebook:
SQL Question 5: How does differ from just ?
is used to combine the output of multiple statements into one big result!
Suppose you were a Data Analyst at Tyler Technologies 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 6: Retrieve Customers from a Specific City
Given the table, write a SQL query that gives the full names of customers from the city 'Houston'. Assume that the table has the fields , , and .
Example Input:
| customer_id | first_name | last_name | city |
|---|---|---|---|
| 101 | John | Doe | New York |
| 102 | Jane | Doe | Houston |
| 103 | Mary | Johnson | Houston |
| 104 | James | Smith | Dallas |
| 105 | Robert | Brown | Houston |
Example Output:
| full_name |
|---|
| Jane Doe |
| Mary Johnson |
| Robert Brown |
Answer:
The SQL query for this would be:
In this query, we select the full name of the customers by concatenating first and last names and assigning the result to the new field . The clause is used to filter the customers based on the city. The function concatenates two or more strings into one string. It does not add spaces between concatenated string. Therefore, we need to manually insert a space by including ' ' as an argument to .
SQL Question 7: How is the constraint used in a database?
A is a field in a table that references the of another table. It creates a link between the two tables and ensures that the data in the field is valid.
Say for example you had sales analytics data from Tyler Technologies's CRM (customer-relationship management) tool.
In this example, the table has a foreign key field called that references the "account_id" field in the table (the primary key). This helps to link the data about each opportunity to the corresponding account information in the accounts table.
This makes sure the insertion of rows in the table that do not have corresponding entries in the table. It also helps to enforce the relationship between the two tables and ensures that data is not deleted from the table if there are still references to it in the table.
SQL Question 8: Retrieve Customer and Product Information
Suppose that Tyler Technologies has two separate tables - one for the customers and another for their product purchases. You, as a data analyst, are tasked to create a SQL query that will join the table and table to obtain a detailed report. This report should display the , , , , and of customers who made a purchase within the year 2022.
Example Input:
| customer_id | first_name | last_name |
|---|---|---|
| 001 | John | Doe |
| 002 | Jane | Doe |
| 003 | James | Smith |
| 004 | Jennifer | Johnson |
Example Input:
| purchase_id | customer_id | product_id | product_name | purchase_date |
|---|---|---|---|---|
| 111 | 001 | N001 | Product_1 | 2022-01-02 |
| 112 | 003 | N002 | Product_2 | 2022-06-03 |
| 113 | 002 | N003 | Product_3 | 2021-07-22 |
| 114 | 004 | N004 | Product_4 | 2022-04-10 |
| 115 | 001 | N002 | Product_2 | 2021-08-08 |
Answer:
This query joins the table and table on the field. The part of the query filters the records to only those purchases that took place in the year 2022.
Since joins come up frequently during SQL interviews, try this interactive Snapchat Join SQL question:
Preparing For The Tyler Technologies SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Tyler Technologies SQL interview is to solve as many practice SQL interview questions as you can!
Beyond just solving the above Tyler Technologies 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, full answers and most importantly, there's an online SQL coding environment so you can right online code up your SQL query answer and have it executed.
To prep for the Tyler Technologies SQL interview it is also helpful to practice SQL problems from other tech companies like:
In case your SQL foundations are weak, forget about jumping right into solving questions – refresh your SQL knowledge with this SQL interview tutorial.
This tutorial covers SQL topics like CASE/WHEN/ELSE statements and filtering data with WHERE – both of these show up often during SQL job interviews at Tyler Technologies.
Tyler Technologies Data Science Interview Tips
What Do Tyler Technologies Data Science Interviews Cover?
Besides SQL interview questions, the other types of questions covered in the Tyler Technologies Data Science Interview are:
- Statistics and Probability Questions
- Python or R Coding Questions
- Product Data Science Interview Questions
- Machine Learning and Predictive Modeling Questions
- Resume-Based Behavioral Questions
How To Prepare for Tyler Technologies Data Science Interviews?
To prepare for Tyler Technologies Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from tech companies like Google & Microsoft
- a refresher on Stats, ML, & Data Case Studies
- over 900+ 5-star reviews on Amazon