10 Lamar Advertising SQL Interview Questions (Updated 2024)

Lamar Advertising employees use SQL daily to help anayze digital billboard inventory and performacne across all their clients. So, it shouldn't surprise you that Lamar Advertising almost always asks SQL coding questions in interviews for Data Science, Data Engineering and Data Analytics jobs.

Thus, to help you practice, we've curated 10 Lamar Advertising SQL interview questions – can you answer each one?

10 Lamar Advertising SQL Interview Questions

SQL Question 1: Identify the Top Spending Customers in Lamar Advertising

Assuming Lamar Advertising is a company that rents out advertising spaces, we would like to find the customers who spend significantly on advertising spaces - which we could term as 'VIP' Customers.

A 'VIP' customer in this case is defined as any customer who has made more than 5 ad placements in a month or has spent more than $10,000 in a month.

We'll assume that we have two tables and with the following structure:

Example Input:

Example Input:

Given these tables, the task is to write a SQL query that identifies and returns the VIP customers for a given month (August in this case).

Answer:

With this query, the sub-select clause will get all customers who have either placed more than 5 ads or have spent more than $10,000 in August. By joining that with the table we get the names of these VIP customers.

To practice a similar VIP customer analysis question on DataLemur's free online SQL code editor, try this Microsoft SQL Interview problem:

SQL Question 2: Employees Earning More Than Managers

Given a table of Lamar Advertising employee salaries, write a SQL query to find all employees who make more than their own boss.

Lamar Advertising Example Input:

Example Output:

This is the output because Olivia Smith earns $8,000, surpassing her manager, William Davis who earns 7,800.

Try this question interactively on DataLemur:

Answer:

First, we perform a SELF-JOIN where we treat the first table () as the managers' table and the second table () as the employees' table. Then we use a clause to filter the results, ensuring we only get employees whose salaries are higher than their manager's salary.

If the code above is hard to understand, you can find a detailed solution with hints here: Well Paid Employees.

SQL Question 3: What's the difference between an inner and a full outer join?

An inner join only includes rows from both tables that satisfy the join condition, whereas a full outer join includes all rows from both tables, regardless of whether they match the join condition or not.

For example, suppose you had a table of Lamar Advertising orders and Lamar Advertising customers.

INNER JOIN: When there is a match in the shared key or keys, rows from both tables are retrieved. In this example, an between the Orders and Customers tables would retrieve rows where the in the Orders table matches the in the Customers table.

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

SQL Question 4: Analyze Billboard Campaign Performance

Lamar Advertising Company provides out-of-home advertising space for clients on Billboards, Digital, Transit and Highway Logo Signs. Given the billboard_ad data, write a SQL query to calculate the number of impressions and average click-through-rate (CTR) of each client’s advertising campaign on an hourly basis on a particular day. Assume an impression is counted each time the ad is displayed, and the click-through rate (CTR) is calculated as (clicks / impressions)*100.

Example Input:

Example Output:

Answer:

This query first filters the data for a specific date using the WHERE clause with the date as '01/10/2022'. Then the GROUP BY clause groups the records by client_id and hour. Within each group, total impressions are calculated and the average CTR is also computed using the clicks and impressions columns. This result will give an hourly analysis of each client's ad campaign performance on the given date, making it easier for Lamar Advertising to understand their ad impact.

To practice another window function question on DataLemur's free online SQL code editor, try this Google SQL Interview Question:

SQL Question 5: In what circumstances might you choose to denormalize a database?

There are several advantages to normalizing a database, including less redundancy, more flexibility, and better performance.

• Less Redundancy: Normalization reduces redundancy by breaking down a larger, more general table into smaller, more specific tables. This reduces the amount of data that needs to be accessed for queries.

• More Flexibility: Normalization makes it easier to modify the structure of the database, as there is less redundancy, so it allows you to make changes to one table without affecting others. This makes it easier to adapt the database to changing business needs (a very real reality at Lamar Advertising!)

• Better Performance: Normalization can improve the performance of the database by reducing the amount of data that needs to be stored and accessed in a single table. This can result in faster query times and better overall performance.

SQL Question 6: Lamar Advertising Billboard Management Database Design

Lamar Advertising Company is one of the largest outdoor advertising companies and operates more than 350,000 displays across the United States and Canada. They need to manage the information of their billboards, advertisers, duration of advertising and the revenue they are making.

Design a database for them with the following tables:

1. billboards: contains information about all the billboards they own
2. advertisers: contains information about all the advertisers
3. contracts: contains information about all the advertising contracts they signed

Perform a PostgreSQL query to get the revenue for each advertiser for the month of November:

Example Input:

Example Input:

Example Input:

Answer:

This query joins the and tables on the . It then checks if the contract was active for any duration in November. It sums up the column to get the revenue generated from each advertiser for that month.

SQL Question 7: What does the operator do?

The / operator is used to remove to return all rows from the first SELECT statement that are not returned by the second SELECT statement.

Note that is available in PostgreSQL and SQL Server, while MINUS is available in MySQL and Oracle (but don't stress about knowing which DBMS supports what exact commands since the interviewers at Lamar Advertising should be lenient!).

Here's a PostgreSQL example of using EXCEPT to find all of Lamar Advertising's Facebook video ads with more than 10k views that aren't also being run on YouTube:

If you want to retain duplicates, you can use the EXCEPT ALL operator instead of EXCEPT. The EXCEPT ALL operator will return all rows, including duplicates.

SQL Question 8: Filtering Client Contracts for Lamar Advertising

As a data analyst at Lamar Advertising, you are tasked with managing the client contracts database. You're required to filter down the client records to only include those who have an advertisement display in Times Square, and signed their contract in or after the year 2020. Furthermore, exclude any clients who are categorized as 'local business'.

For the purpose of this question, we will be working with the and tables having the below structure and data.

Example Input:

Example Input:

Answer:

This query joins the contracts and clients tables on their client_id. It then filters for records where the display location is Times Square, the contract was signed in or after the year 2020, and the client is not a local business.

SQL Question 9: Retrieving Customers Interested in Billboard Advertising

As a media company, Lamar Advertising often has to categorize their customers based on their advertising requirements. Based on the company's customer records, write an SQL query to filter out those customers who have shown interest in 'billboard' advertising.

A sample input table with customer records might look like this:

Example Input:

You need to list the names and emails of customers who are interested in billboard advertising.

Answer:

The SQL query will be as follows:

This query follows a simple syntax where we are selecting 'name' and 'email' from the 'customer_records' table where the 'interested_in' column contains the word 'billboard'.

The usage of the sign on both sides of the keyword 'billboard' within the condition means that we are looking for any string that contains the word 'billboard', regardless of what comes before or after it.

Example Output:

The output shows the names and emails of the customers interested in billboard advertising.

Read about how Lamar Advertising customized their DoorDash campaign by following the average customer journey.

SQL Question 10: Can you explain the difference between a foreign and primary key in a database?

To explain the difference between a primary key and foreign key, let's start with some data from Lamar Advertising's marketing analytics database which stores data from Google Ads campaigns:

:

+------------+------------+------------+------------+ | ad_id | campaign_id| keyword | click_count| +------------+------------+------------+------------+ | 1 | 100 | Lamar Advertising pricing | 10 | | 2 | 100 | Lamar Advertising reviews | 15 | | 3 | 101 | Lamar Advertising alternatives | 7 | | 4 | 101 | buy Lamar Advertising | 12 | +------------+------------+------------+------------+

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 Google Ads campaign that each ad belongs to, establishing a relationship between the ads and their campaigns. This foreign key allows you to easily query the table to find out which ads belong to a specific campaign, or to find out which campaigns a specific ad belongs to.

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 ad group that each ad belongs to, and the of the Google Ads account that the campaigns belong to.

Lamar Advertising SQL Interview Tips

Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Lamar Advertising SQL interview is to solve as many practice SQL interview questions as you can! Beyond just solving the above Lamar Advertising SQL interview questions, you should also solve the 200+ SQL questions on DataLemur which come from companies like Facebook, Google, and VC-backed startups.

Each interview question has multiple hints, fully explained answers along with a discussion board to see how others solved it and most importantly, there's an online SQL coding environment so you can instantly run your SQL query answer and have it checked.

To prep for the Lamar Advertising SQL interview it is also wise to practice interview questions from other advertising companies like:

• Clear Channel SQL questions

• Omnicom Group SQL questions

• Interpublic Group SQL questions

• Facebook SQL questions

In case your SQL coding skills are weak, don't worry about diving straight into solving questions – go learn SQL with this SQL tutorial for Data Analytics.

This tutorial covers things like cleaning text data and grouping by multiple columns – both of which pop up routinely during SQL job interviews at Lamar Advertising.

Lamar Advertising Data Science Interview Tips

What Do Lamar Advertising Data Science Interviews Cover?

Besides SQL interview questions, the other types of problems to practice for the Lamar Advertising Data Science Interview include:

• Statistics & AB Testing Questions
• Coding Questions in Python or R
• Product Data Science Interview Questions
• ML Interview Questions
• Behavioral Based Interview Questions

How To Prepare for Lamar Advertising Data Science Interviews?

To prepare for Lamar Advertising Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions sourced from Microsoft, Amazon & startups
• a crash course on Stats, SQL & ML
• over 1000+ reviews on Amazon & 4.5-star rating