LinkedIn·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL-heavy screen for a Data Scientist role at LinkedIn. Two questions, both off the same table, both requiring you to think carefully about self-joins and date filtering. Pretty focused session, no fluff.

Questions Asked (2)

Using the article_views table, write a query to count how many authors have never viewed any of their own articles.

Product Analytics & MetricsAlgorithms & Data Structures

Author's notes

This one tripped me up for a second because the table uses both author_id and viewer_id and you have to compare them.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify the total number of distinct authors from the articles table. Then, find authors who have viewed their own articles by joining article_views with articles on article_id and filtering where viewer_id equals author_id. Finally, subtract the second count from the first to get the number of authors who have never viewed their own articles.

Pro tip: Clarify the table schema and assumptions (e.g., whether author_id is in articles or a separate table) before writing the query. Use a LEFT JOIN or NOT IN to handle authors with no views, and consider performance implications for large datasets.

1. Understand the schema and define 'own article'

Identify the relevant tables and columns: articles (article_id, author_id) and article_views (article_id, viewer_id). Define that an author views their own article when viewer_id equals author_id for the same article_id.

2. Find authors who have viewed their own articles

Write a subquery or join to select distinct author_ids from articles where there exists a view by the same author on that article. This gives the set of authors who have self-viewed.

3. Get all distinct authors

Select all distinct author_ids from the articles table to get the complete set of authors.

4. Compute the difference

Use a LEFT JOIN, NOT IN, or EXCEPT to find authors in the full set who are not in the self-viewed set. Count these authors.

5. Write the final SQL query

Combine the steps into a single SQL query, ensuring correct handling of NULLs and duplicates. Optionally, discuss alternative approaches and their trade-offs.

Key Points to Mention

Assumption about schema: author_id is in the articles table; if not, adjust join accordingly.
Use of DISTINCT to avoid counting authors multiple times.
Handling of authors with no views at all (they should be included in the 'never viewed' count).
Performance considerations: indexing on article_id and viewer_id, and avoiding correlated subqueries if possible.
Edge cases: authors with multiple articles, authors who viewed some but not all of their articles.
Alternative approaches: LEFT JOIN with IS NULL, NOT EXISTS, or EXCEPT for clarity and efficiency.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

On a specific date, how many members viewed more than one distinct article? Write the SQL to find this count.

Product Analytics & MetricsData Modeling

Author's notes

Straightforward GROUP BY with a HAVING clause.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify the table schema and the definition of 'viewed' (e.g., view events). Then, filter for the specific date, group by member and article to get distinct articles per member, and finally count members with more than one distinct article.

Pro tip: Mention that you would confirm whether the date is based on event timestamp and whether views are deduplicated per member-article pair; this shows attention to data nuances and avoids double-counting.

1. Clarify requirements and schema

Ask about the table structure (e.g., view events with member_id, article_id, timestamp) and confirm that 'viewed' means a view event. Ensure the date filter is correct (e.g., event_date = 'specific date').

2. Filter for the specific date

Use a WHERE clause to select only rows where the event date matches the given date. If the timestamp is a datetime, use a range or date function to filter.

3. Get distinct articles per member

Group by member_id and count distinct article_id to find how many unique articles each member viewed on that date.

4. Count members with more than one distinct article

Use a HAVING clause to filter groups where the distinct count > 1, then count the resulting members. Alternatively, use a subquery or CTE to first compute per-member counts, then count those with count > 1.

5. Write the final SQL query

Combine the steps into a single SQL statement, ensuring proper aggregation and filtering. Optionally, discuss performance considerations like indexing on date and member_id.

Key Points to Mention

Use COUNT(DISTINCT article_id) to count unique articles per member.
Filter by the specific date using WHERE clause on the event timestamp or date column.
Group by member_id to aggregate views per member.
Use HAVING COUNT(DISTINCT article_id) > 1 to filter members with more than one distinct article.
Wrap the query in a subquery or CTE to count the number of such members.
Consider edge cases like multiple views of the same article by the same member (should count as one distinct article).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.