At ASGN Incorporated, SQL is used for analyzing complex datasets to offer business solutions, such as identifying opportunities for cost savings and process improvements, as well as managing the company's vast data infrastructure, including data warehousing and data governance. This is why ASGN often uses SQL questions during interviews for Data Science, Analytics, and Data Engineering jobs.
So, to help you practice for the ASGN SQL interview, we'll cover 9 ASGN Incorporated SQL interview questions in this blog.
Given a database of customer transactions for ASGN, we want to identify the "Whale" customers. These are customers who have made significant transaction amounts over the past year. Let us assume we have access to a table named containing relevant customer transaction data.
Sample Table:
transaction_id | user_id | transaction_date | amount |
---|---|---|---|
1020 | 4567 | 08/20/2021 | 500 |
2450 | 4567 | 07/15/2021 | 600 |
7634 | 3421 | 09/25/2021 | 100 |
7253 | 4567 | 06/30/2021 | 750 |
8215 | 8271 | 10/11/2021 | 200 |
Your task is to write a SQL query that identifies customers who have made total transactions amounting to $1000 or more in the last year.
This SQL query will return ID of users along with the total amount transacted by each user in the last year. Here, we've used PostgreSQL's to look at transactions from the last year only. The GROUP BY clause is used to group the results by each unique user_id, and the HAVING clause is used to filter out users who have made total transactions of $1000 or more. The result is ordered in descending order of the total transaction amount.
To practice a related super-user data analysis question on DataLemur's free online SQL code editor, try this recently asked Microsoft SQL interview question:
Imagine you had a table of ASGN employee salary data. Write a SQL query to find the 2nd highest salary among all employees.
employee_id | salary |
---|---|
1 | 2500 |
2 | 800 |
3 | 1000 |
4 | 1200 |
second_highest_salary |
---|
1200 |
You can solve this interview question and run your code right in DataLemur's online SQL environment:
You can find a step-by-step solution with hints here: 2nd Highest Salary.
If you want to return records with no duplicates, you can use the keyword in your statement.
For example, if you had a table of ASGN employees, and wanted to see what are all the unique job titles that currently worked at the company, you could write the following query:
If had the following data:
f_name | job_title |
---|---|
Akash | Data Analyst |
Brittany | Data Scientist |
Carlos | Data Engineer |
Diego | Data Engineer |
Eva | Data Analyst |
Then the output from the query would be:
job_title |
---|
Data Analyst |
Data Scientist |
Data Engineer |
ASGN Inc. is a company that sells digital online products. They would like to analyze their data to understand the average customer review ratings for their products on a monthly basis. Please write a SQL query that provides the average star ratings for each product for each month.
The date the rating was given will be given in the "submit_date" column and provided in the format "MM/DD/YYYY HH:MI:SS". For example, "06/10/2022 00:00:00" represents the 10th day of June 2022.
To simplify the process, consider only the products with at least one rating for a given month.
You have a table called which contains the following fields:
review_id | user_id | submit_date | product_id | stars |
---|---|---|---|---|
6171 | 123 | 06/08/2022 00:00:00 | 50001 | 4 |
7802 | 265 | 06/10/2022 00:00:00 | 69852 | 4 |
5293 | 362 | 06/18/2022 00:00:00 | 50001 | 3 |
6352 | 192 | 07/26/2022 00:00:00 | 69852 | 3 |
4517 | 981 | 07/05/2022 00:00:00 | 69852 | 2 |
Given the provided data, the above SQL query extracts the month from the 'submit_date' field, groups the data by the extracted month and the product, then calculates the average star rating for each product in each month. The results are ordered first by month, then by product.
mth | product | avg_stars |
---|---|---|
6 | 50001 | 3.50 |
6 | 69852 | 4.00 |
7 | 69852 | 2.50 |
In the month of June, product 50001 had an average star rating of 3.5, and product 69852 had an average rating of 4. Similarly, in July, the average star rating for product 69852 was 2.5.
For more window function practice, try this Uber SQL problem within DataLemur's online SQL code editor:
A non-relational (NoSQL) database is any database that does not use the typical tabular format of rows and columns like in relational databases.
While knowing the four different types of NoSQL databases is beyond the scope of most Data Analyst & Data Science interviews, Data Engineers at ASGN should vaguely refresh these concepts:
ASGN is a company that provides high-quality professional-technical support to its clients. To better manage its internal operations, ASGN would like to implement a database system to track its ongoing projects and employees.
A project is defined with a unique ID, a project name, the client's name, and a project manager. An employee is identified by a unique ID, name, role, and area of expertise.
Every project might or might not have one or many employees working on it.
Design a system to model this business scenario. What would the tables look like? Which columns should each table have? What are the relationships between the tables?
Consider how you would implement this in PostgreSQL.
project_id | project_name | client_name | manager |
---|---|---|---|
1 | Project_A | Client_X | Manager_1 |
2 | Project_B | Client_Y | Manager_2 |
3 | Project_C | Client_Z | Manager_1 |
employee_id | employee_name | role | expertise |
---|---|---|---|
1 | Employee_A | Engineer | JavaScript |
2 | Employee_B | Designer | UI/UX |
3 | Employee_C | Project Manager | Leadership |
project_id | employee_id |
---|---|
1 | 1 |
1 | 2 |
2 | 1 |
3 | 2 |
3 | 3 |
One way to model this scenario would be to create three distinct tables: ``
A table with columns including (primary key), , , and .
An table with columns including (primary key), , , and .
A junction table with columns and '=, establishing the many-to-many relationship between and .
Then, a query to retrieve a list of employees working on Project_A would look like:
This design enables efficient querying of employees-by-project and projects-by-employee. You can add, remove, or change projects or employees individually without affecting other related data.
Remember that performance can be optimized further by implementing appropriate indexing on the 'project_employees' table.
A join in SQL combines rows from two or more tables based on a shared column or set of columns. To demonstrate the difference between a and , say you had a table of ASGN orders and ASGN customers.
LEFT JOIN: A retrieves all rows from the left table (in this case, the Orders table) and any matching rows from the right table (the Customers table). If there is no match in the right table, NULL values will be returned for the right table's columns.
RIGHT JOIN: A retrieves all rows from the right table (in this case, the Customers table) and any matching rows from the left table (the Orders table). If there is no match in the left table, NULL values will be returned for the left table's columns.
As a Data Analyst at ASGN, you are asked by the Marketing Team to calculate the Click-Through-Conversion rate for each product over the past month. The conversion is defined as the transition of a user from viewing a product to adding the product to their cart.
You have two relevant tables for this task:
view_id | user_id | view_date | product_id |
---|---|---|---|
101 | 1256 | 06/12/2022 00:00:00 | 5001 |
202 | 2657 | 06/13/2022 00:00:00 | 6002 |
303 | 9648 | 06/14/2022 00:00:00 | 7003 |
404 | 1256 | 06/15/2022 00:00:00 | 5001 |
505 | 2657 | 06/16/2022 00:00:00 | 6002 |
add_id | user_id | add_date | product_id |
---|---|---|---|
1001 | 1256 | 06/12/2022 00:00:00 | 5001 |
2002 | 2657 | 06/13/2022 00:00:00 | 6002 |
3003 | 9648 | 06/14/2022 00:00:00 | 8004 |
4004 | 1256 | 06/15/2022 00:00:00 | 5001 |
5005 | 2657 | 06/16/2022 00:00:00 | 7003 |
This query calculates the total number of views and total conversions for each product over the past month. It then calculates the conversion rate as the percentage of views that result in a conversion. The statement is used to handle cases where there are no views, preventing division by zero.
To practice a similar problem about calculating rates, try this TikTok SQL question within DataLemur's interactive SQL code editor:
Given two tables, and . The contains the data about customer purchases and contains user registration data. Write a SQL query to find out monthly total expenditure by the users who registered in 2022.
Consider the months from user registration date for calculation, not from current date.
user_id | purchase_date | price |
---|---|---|
123 | 05/05/2022 | 50 |
265 | 05/10/2022 | 150 |
362 | 04/15/2022 | 100 |
192 | 04/05/2022 | 75 |
981 | 04/25/2022 | 125 |
user_id | name | registration_date |
---|---|---|
123 | John | 01/05/2022 |
265 | Alex | 02/10/2022 |
362 | Maria | 03/25/2022 |
192 | Leo | 04/12/2022 |
981 | Ana | 01/30/2022 |
month | year | total_expenditure |
---|---|---|
5 | 2022 | 200 |
4 | 2022 | 200 |
You can use a combination of the , , and clauses to solve this question.
Here is an example of a PostgreSQL query to solve the problem:
The above query works by first joining the table with the table on the user_id column. After this, it filters out the users who registered in 2022. Finally, it groups the expenditure by month and year, and sums the purchase prices for each group. The result is sorted in the ascending order of the year and then the month.
Since joins come up so often during SQL interviews, try an interactive Spotify JOIN SQL question:
The key to acing a ASGN SQL interview is to practice, practice, and then practice some more! Beyond just solving the above ASGN SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like FAANG (FB, Apple, Amazon, Netflix, Google).
Each DataLemur SQL question has multiple hints, fully explained answers along with a discussion board to see how others solved it and crucially, there's an online SQL code editor so you can easily right in the browser your query and have it graded.
To prep for the ASGN SQL interview you can also be wise to practice interview questions from other IT companies like:
Stay up-to-date on the latest news and developments from ASGN Incorporated, a leading provider of IT and professional services!
In case your SQL foundations are weak, don't worry about going right into solving questions – refresh your SQL knowledge with this interactive SQL tutorial.
This tutorial covers things like Union vs. UNION ALL and aggregate window functions – both of these pop up often during ASGN SQL interviews.
In addition to SQL interview questions, the other topics tested in the ASGN Data Science Interview include:
To prepare for the ASGN Data Science interview make sure you have a firm understanding of the company's values and mission – this will be clutch for acing the behavioral interview. For technical interviews prepare by reading Ace the Data Science Interview. The book's got: