8 Ericsson SQL Interview Questions (Updated 2024)
At Ericsson, SQL is used day-to-day for analyzing telecommunication network data, and to manage and manipulate data in their global customer database. Unsurprisingly this is why Ericsson almost always evaluates jobseekers on SQL questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.
So, to help you study for the Ericsson SQL interview, here’s 8 Ericsson SQL interview questions – can you solve them?
8 Ericsson SQL Interview Questions
SQL Question 1: Compute Running Monthly Average Rating for Each Product
In Ericsson, each product is often reviewed by the users. The user reviews contain a star rating for the product. You are given a dataset containing the product reviews made by users to the products, where the rating stars range from 1 to 5.
You are required to write a SQL query that computes the running monthly average rating for each product. The running average should be computed for each month and should include all the previous months' ratings.
Example Input:
| review_id | user_id | submit_date | product_id | stars |
|---|---|---|---|---|
| 6171 | 123 | 2022-06-08 | 50001 | 4 |
| 7802 | 265 | 2022-06-10 | 69852 | 4 |
| 5293 | 362 | 2022-06-18 | 50001 | 3 |
| 6352 | 192 | 2022-07-26 | 69852 | 3 |
| 4517 | 981 | 2022-07-05 | 69852 | 2 |
Example Output:
| mth | product | avg_stars |
|---|---|---|
| 6 | 50001 | 3.50 |
| 6 | 69852 | 4.00 |
| 7 | 69852 | 2.50 |
Answer:
You can write a window function to solve this task. Here is the PostgreSQL query:
This query first extracts the month part from the column. Then it calculates the average of the per product () for each month and all preceding months. The ORDER BY clause ensures that the output rows are sorted by the extracted month and product.
For more window function practice, solve this Uber SQL Interview Question within DataLemur's online SQL coding environment:
SQL Question 2: Determine the Click-through rate for Ericsson's Ad Campaigns
Ericsson is a major telecommunications company that handles a lot of online traffic. As a part of their marketing strategy, they run various ad campaigns that aim to direct users to their product webpages. To measure the performance of these campaigns, they want to calculate the click-through rate (CTR), defined as the percentage of users who click on the advertisement and proceed to view the product out of all users exposed to the ad.
Given datasets of detailing every time an ad was shown to a user, and concerning instances where a user views a product after clicking on the ad. Calculate the click-through rate per advertisement campaign.
Example Input:
| ad_id | campaign_id | user_id | display_time |
|---|---|---|---|
| 101 | A1 | 123 | 06/08/2022 00:00:00 |
| 102 | A2 | 265 | 06/10/2022 00:00:00 |
| 103 | A1 | 362 | 06/18/2022 00:00:00 |
| 104 | A2 | 192 | 07/26/2022 00:00:00 |
| 105 | A1 | 981 | 07/05/2022 00:00:00 |
Example Input:
| view_id | user_id | ad_id | view_time | product_id |
|---|---|---|---|---|
| 201 | 123 | 101 | 06/08/2022 00:02:00 | 50001 |
| 202 | 265 | 102 | 06/10/2022 00:01:00 | 69852 |
| 203 | 362 | 103 | 06/18/2022 00:00:30 | 50001 |
Answer:
The CTE first counts the total number of ad displays per campaign. The CTE counts the total product view events linked with each ad campaign. Finally, we join these two CTEs and calculate the click-through rate (CTR) as the ratio of to , expressed as a percentage.
To practice a similar problem about calculating rates, solve this TikTok SQL Interview Question within DataLemur's online SQL code editor:
SQL Question 3: What is a foreign key?
A foreign key is a column or group of columns in a table that refers to the primary key in another table. The foreign key constraint helps maintain referential integrity between the two tables. The table with the foreign key is called the child table, while the table with the candidate key is called the parent or referenced table.
For example, consider a database with two tables: and customer_idcustomer_idcustomer_id` column in Ericsson customers table.
SQL Question 4: Average Data Usage per Device Model
Ericsson, being a network and telecommunications company, deals with a wide range of devices. We are interested in determining which device models on average consume the most data on our network. You need to write a query that will return each device model and the average data usage of users who use each of these device models.
Example Input:
| user_id | device_model |
|---|---|
| 123 | iPhone 12 |
| 265 | Samsung S21 |
| 362 | iPhone 12 |
| 192 | Samsung S20 |
| 981 | iPhone 11 |
Example Input:
| user_id | date | amount_data_used |
|---|---|---|
| 123 | 06/08/2022 00:00:00 | 2.1 |
| 265 | 06/10/2022 00:00:00 | 3.0 |
| 362 | 06/18/2022 00:00:00 | 2.9 |
| 192 | 07/26/2022 00:00:00 | 1.8 |
| 981 | 07/05/2022 00:00:00 | 3.3 |
Example Output:
| device_model | avg_data_used |
|---|---|
| iPhone 12 | 2.5 |
| Samsung S21 | 3.0 |
| Samsung S20 | 1.8 |
| iPhone 11 | 3.3 |
Answer:
We join the devices table and the data_usage table on the user_id which is the common column in both tables. Then, we group by the device_model column in the devices table. Finally, we get the average of the amount_data_used from the data_usage table and column for every group.
SQL Question 5: What are the main differences between foreign and primary keys in a database?
To explain the difference between a primary key and foreign key, let's inspect employee data from Ericsson's HR database:
:
+------------+------------+------------+------------+ | employee_id| first_name | last_name | manager_id | +------------+------------+------------+------------+ | 1 | Aubrey | Graham | 3 | | 2 | Marshal | Mathers | 3 | | 3 | Dwayne | Carter | 4 | | 4 | Shawn | Carter | | +------------+------------+------------+------------+
In this table, could be a primary key. It is unique for each row in the table and cannot contain null values.
could be a foreign key. It references the of the manager of each employee in the table, establishing a relationship between the employees and their managers. This foreign key allows you to easily query the table to find out who a specific employee's manager is, or to find out which employees report to a specific manager.
It is also possible for a table to have multiple foreign keys that reference different primary keys in other tables. For example, the table could have additional foreign keys for the of the department where each employee works, and the l of the location where each employee is based.
SQL Question 6: Filtering the Customer Database on Job Title at Ericsson.
As part of your role in Ericsson's database management team, you are frequently tasked to extract relevant customer information from the company's expansive database. Create an SQL statement to select customers from the database who work as engineers, with the job title containing the term 'engineer' anywhere in the string. Order the result by customer name.
Here are some records from the customer table to demonstrate the problem:
Example Input:
| customer_id | name | job_title | purchase_count | last_purchase_date |
|---|---|---|---|---|
| 101 | John Doe | Electrical Engineer | 5 | 06/22/2022 |
| 102 | Jane Doe | Marketing Manager | 3 | 06/18/2022 |
| 103 | Adam Smith | Civil Engineer | 7 | 07/08/2022 |
| 104 | Emma Jones | Software Engineer | 6 | 06/01/2022 |
| 105 | Michael Brown | Finance Analyst | 2 | 07/15/2022 |
Your query should return the following results:
Example Output:
| customer_id | name | job_title |
|---|---|---|
| 101 | John Doe | Electrical Engineer |
| 103 | Adam Smith | Civil Engineer |
| 104 | Emma Jones | Software Engineer |
Answer:
The query to extract customer information for all engineers would look like this:
This query filters the customer table to only return rows where the job title contains the string 'Engineer'. It orders the result by the customer's name.
SQL Question 7: Does a typically give the same results as a ?
For all practical purposes, and do NOT produce the same results.
While both are similar in that they combine two tables, you can think of joins as increasing the width of the resulting table (you'll have more columns in the result set for a left/inner/right join), whereas a union is used to combine rows which increases the height of the result set but keeps the column count the same.
If your familiar with set operations, you can think of a as set addition, whereas a is set multiplication.
SQL Question 8: Customer Purchase Analysis
You're given two tables and . The table includes customer ID, first name, last name, and country. The table comprises order ID, product, cost, and customer ID.
Write a SQL Query to find all customers from 'Sweden' and details of their order history. Also, compute the total cost incurred by each Swedish customer.
Sample Input
| customer_id | first_name | last_name | country |
|---|---|---|---|
| C001 | Emma | Larsson | Sweden |
| C002 | Olivia | Svensson | Sweden |
| C003 | Lucas | Johansson | Denmark |
| C004 | Elijah | Karlsson | Germany |
Sample Input
| order_id | product | cost | customer_id |
|---|---|---|---|
| O001 | Product A | 200 | C001 |
| O002 | Product B | 120 | C002 |
| O003 | Product C | 300 | C003 |
| O004 | Product D | 150 | C001 |
| O005 | Product E | 220 | C002 |
Answer:
This query starts by joining the and tables on . It then filters for those records where the customer's country is 'Sweden'. Finally, for each Swedish customer and their respective order details, it calculates the aggregated sum of order costs on a per-customer basis using the SQL aggregate function .
Since joins come up frequently during SQL interviews, try this interactive Snapchat SQL Interview question using JOINS:
Ericsson SQL Interview Tips
The best way to prepare for a Ericsson SQL interview is to practice, practice, practice.
In addition to solving the earlier Ericsson SQL interview questions, you should also solve the 200+ SQL exercises on DataLemur which come from companies like Microsoft, Google, and Facebook.
Each interview question has hints to guide you, step-by-step solutions and crucially, there is an online SQL coding environment so you can easily right in the browser your SQL query and have it graded.
To prep for the Ericsson SQL interview you can also be a great idea to solve interview questions from other tech companies like:
In case your SQL skills are weak, don't worry about going right into solving questions – improve your SQL foundations with this DataLemur SQL Tutorial.
This tutorial covers SQL concepts such as CASE/WHEN/ELSE statements and creating summary stats with GROUP BY – both of these pop up often in Ericsson SQL interviews.
Ericsson Data Science Interview Tips
What Do Ericsson Data Science Interviews Cover?
Beyond writing SQL queries, the other topics to practice for the Ericsson Data Science Interview are:
- Probability & Stats Questions
- Coding Questions in Python or R
- Product-Sense and Business Case Questions
- ML Interview Questions
- Resume-Based Behavioral Questions
How To Prepare for Ericsson Data Science Interviews?
To prepare for Ericsson Data Science interviews read the book Ace the Data Science Interview because it's got:
- 201 interview questions taken from Microsoft, Amazon & startups
- a crash course on SQL, AB Testing & ML
- over 900+ 5-star reviews on Amazon