11 New York Times SQL Interview Questions (Updated 2024)

Data Analysts & Data Scientists at New York Times uses SQL to extract insights from readership data to understand consumer behavior, such as which articles are most popular, how users interact with the website, and what topics are trending. They also use it to manage the digital ads based on readers' preferences and engagement, which is why, New York Times asks prospective hires SQL interview questions.

To help you study for the New York Times SQL interview, here's 11 NYT SQL interview questions in this blog.

11 NYT SQL Interview Questions

SQL Question 1: Laptop vs. Mobile Viewership

This is the same question as problem #3 in the SQL Chapter of Ace the Data Science Interview!

Assume you're given the table on user viewership categorised by device type where the three types are laptop, tablet, and phone.

Write a query that calculates the total viewership for laptops and mobile devices where mobile is defined as the sum of tablet and phone viewership. Output the total viewership for laptops as and the total viewership for mobile devices as .

Table

Example Input

Example Output

Answer:

Practice this NYT SQL Interview Question on our FREE interactive coding platform!

SQL Question 2: Top Department Salaries

Given a table of New York Times employee salary data, write a SQL query to find the top 3 highest earning employees in each department.

New York Times Example Input:

Example Input:

Example Output:

Code your solution to this question directly within the browser on DataLemur:

Answer:

We use the DENSE_RANK() window function to generate unique ranks for each employee's salary within their department, with higher salaries receiving lower ranks. Then, we wrap this up in a CTE and filter the employees with a ranking of 3 or lower.

f the solution above is confusing, you can find a step-by-step solution here: Top 3 Department Salaries.

SQL Question 3: What are the three different normal forms?

Normalization is the process of dividing a larger table into smaller tables, to eliminate redundancy and dependency. Although there are 5 levels of normalization (normal forms), the 3 most important ones that you should know for the New York Times SQL interview are:

1. First Normal Form (1NF): Remove a table's duplicate columns, and make sure each value in the column is a singular value (no containers or lists of data). Each row of table should have a unique identifier as well.
2. Second Normal Form (2NF): A table is in 2NF if it meets all requirements of the 1NF the non-key columns are dependent only on the primary key. You do this by separating subsets of columns subsets, and associating the tables by using primary/foreign keys.
3. Third Normal Form (3NF): The table should be in 2NF and there shouldn't be any dependency on any non-key attributes (meaning a primary key should be the only thing needed to identify a row).

SQL Question 4: Analyzing Article Reads by Month

The New York Times wants to analyze the monthly activity of their online articles. They would like to understand how many unique readers they have for each article every month. You are given a table which tracks each time a user opens an article. Every row in the dataset means that a user has read an article. Duplicate entries are possible and indicate that a user read the article multiple times. Write a SQL query to return the month, the article_id and the number of unique readers for that article for that month.

Consider below is the dataset:

Example Input:

Expected Output:

Answer:

In PostgreSQL, you can utilize the clause to get the month from a date. Hence, your SQL query might look like:

This query counts the unique users (denoted by unique ) separated by and . The clause allows for unique counts within each grouping.

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

SQL Question 5: What is a database index, and what are the different types of indexes?

A database index is a way to optimize the performance of a database by reducing the amount of data that needs to be searched to retrieve a record.

There are several types of indexes:

• unique & non-inuqie indexes
• primary & composite indexes
• clustered & non-clustered indexes

SQL Question 6: Design and Query a Database for New York Times Subscriptions and Articles

You've been hired as a database designer for the New York Times. Your task is to model a database to keep track of magazine subscriptions, articles, and authors. Each author can write many articles, and multiple authors can collaborate on a single article. An article can belong to multiple magazine issues. A subscription can access multiple magazine issues.

Design a database schema and construct a PostgreSQL query to find all the authors who wrote more than three articles in any magazine issue a subscriber has access to. Assume all articles are in the English language.

Example Input:

Example Input:

Example Input:

Example Input:

Answer:

This SQL query begins by joining the subscriptions, magazine issues, articles, and authors tables. The join is based on the common columns between these tables, constructing a mega table which includes subscription information, articles, magazine issues, and authors all at once. Afterward, it groups the data based on subscription and author, and calculates the count of distinct articles for each author. Finally, the HAVING clause filters out authors who have written more than 3 articles for any magazine issues that a subscriber has access to.

SQL Question 7: What are the various types of joins used in SQL?

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 8: Average Number of Shares per Article

As a data analyst at the New York Times, you have been asked to determine the average number of shares per article for the last month. The company wants to understand the reach of their articles on social platforms. Assume we have a table with the columns , , and , and another table with the columns , , , and .

Here's a sample of the and tables:

:

:

Answer:

You can determine the average number of shares per article for the last month with the following PostgreSQL query:

This query first selects the and from the table, and computes the average number of share IDs () from the table. The operation is used to merge the and tables based on matching s. The clause restricts the data to articles published in the last month. The clause allows calculating the average number of shares per article.

To practice a very similar question try this interactive New York Times Laptop vs. Mobile Viewership Question which is similar for dealing with article analytics or this Facebook Average Post Hiatus (Part 1) Question which is similar for querying date-based statistics.

SQL Question 9: Click-through-rate for NY Times Articles

Assuming that New York Times (NYT) wants to calculate the click-through-rate (CTR) for its articles. Each time an article is served on the homepage, it is counted as an impression. If a user clicks on the article to read it, it is counted as a click.

Calculate the CTR as the total number of clicks on an article divided by the total number of impressions, for articles served in the top slot on the homepage, on an hourly basis.

Example Input:

Example Input:

Answer:

Here, we use a LEFT JOIN to combine with , because we want to keep all impressions (the served articles) even if there are no corresponding clicks. We use date_trunc('hour') to round down the impression_time and click_time to an hour, so that clicks and impressions within the same hour are counted together. For each combination of hour and article, we calculate the total number of clicks and impressions, and then calculate the CTR by dividing total clicks by total impressions. Finally, we order the results by hour and ctr in descending order.

To solve a similar problem about calculating rates, try this SQL interview question from TikTok within DataLemur's online SQL coding environment:

SQL Question 10: How do you identify duplicated data in a table?

One way to find duplicate records in a table is by using , and then seeing which groups have more than one occurence:

Another way is by using the operator:

SQL Question 11: Joining and Analyzing User Subscription Data

You are given two tables, and . The table records different users' subscription status at the New York Times. The table details the various subscription plans the company offers.

Your task is to write a SQL query to find the total revenue generated by each subscription plan per year.

Here is a sample representation of the and tables:

Example Input:

Example Input:

Answer:

Here's a SQL query to solve this question:

The above SQL query is joining the and tables using the field. We're grouping by the subscription plan and year, and calculating the total revenue by multiplying the number of subscriptions with the monthly price of each plan and the number of months in a year (assuming each subscription lasts exactly a year). The query is filtered to only include records where the is greater than the , to avoid including cancelled subscriptions.

Since joins come up frequently during SQL interviews, try an interactive Spotify JOIN SQL question:

Preparing For The New York Times SQL Interview

The best way to prepare for a SQL interview, besides making sure you have strong SQL fundamentals, is to practice a ton of real SQL questions that were asked in recent job interviews. In addition to solving the earlier New York Times SQL interview questions, you should also solve the 200+ DataLemur SQL Interview Questions which come from companies like Netflix, Airbnb, and Amazon.

Each interview question has multiple hints, step-by-step solutions and best of all, there's an online SQL code editor so you can easily right in the browser your SQL query answer and have it checked.

To prep for the New York Times SQL interview you can also be useful to solve SQL problems from other media companies like:

• Fox SQL questions
• Facebook SQL questions
• Publicis Groupe SQL questions
• Disney SQL questions

Stay ahead of the curve with The New York Times' in-depth coverage of Artificial Intelligence trends and breakthroughs!

In case your SQL skills are weak, forget about diving straight into solving questions – refresh your SQL knowledge with this free SQL tutorial.

This tutorial covers things like filtering with LIKE and sorting data with ORDER BY – both of which come up frequently in SQL interviews at New York Times.

NYT Data Science Interview Tips

What Do New York Times Data Science Interviews Cover?

In addition to SQL query questions, the other question categories covered in the New York Times Data Science Interview are:

• Stats Interview Questions
• Python or R Coding Questions
• Product Data Science Interview Questions
• ML Modelling Questions
• Behavioral Interview Questions centered on New York Times company values

How To Prepare for New York Times Data Science Interviews?

To prepare for New York Times Data Science interviews read the book Ace the Data Science Interview because it's got:

• 201 interview questions sourced from companies like Google, Tesla, & Goldman Sachs
• a refresher covering Python, SQL & ML
• over 1000+ reviews on Amazon & 4.5-star rating

Don't ignore the behavioral interview – prep for that using this list of behavioral interview questions for Data Scientists.