Write a query to return the IDs of the Facebook pages which do not have any likes. The output should be sorted by page ID in ascending order.
This problem tests your understanding of set operations and NULL handling—critical skills for Meta's social graph queries. At Meta's scale, identifying inactive or underperforming content is essential for content recommendation algorithms and creator insights. The challenge is choosing the right approach: LEFT JOIN with NULL checks, NOT IN subqueries, or NOT EXISTS—each with different performance characteristics at scale.
Key concepts tested: LEFT JOIN with NULL filtering, NOT IN with subqueries, NOT EXISTS for anti-joins, DISTINCT to handle duplicates in the likes table, and ORDER BY for result sorting. Understanding when to use each approach based on data distribution and NULL handling is crucial for production queries.
Meta's data engineers use similar queries to: identify pages eligible for growth campaigns, detect content that needs promotion, generate creator analytics showing engagement gaps, feed recommendation systems with underperforming content for A/B testing, and audit data quality by finding orphaned records in distributed systems.
When tackling this Meta problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT page_id
FROM pages
WHERE page_id NOT IN (
SELECT DISTINCT page_id
FROM page_likes
)
ORDER BY page_id ASC;NOT IN with a subquery is the most readable expression of the intent: 'give me pages whose ID does not appear in the likes table'. The DISTINCT inside the subquery reduces the set size the NOT IN list must search, though many optimizers apply deduplication automatically for IN/NOT IN subqueries. This approach is easy to understand but has a critical weakness: if page_likes.page_id contains any NULL value, the entire NOT IN condition evaluates to UNKNOWN for every row, returning an empty result — a silent bug that is easy to miss.
SELECT p.page_id
FROM pages p
LEFT JOIN page_likes pl ON p.page_id = pl.page_id
WHERE pl.page_id IS NULL
ORDER BY p.page_id ASC;A LEFT JOIN keeps all rows from pages; when no matching row exists in page_likes, the joined columns are NULL. Filtering WHERE pl.page_id IS NULL isolates pages with zero likes. This approach is NULL-safe — unlike NOT IN, it is unaffected by NULL values in page_likes.page_id. It also typically outperforms NOT IN on large datasets because the optimizer can use a hash anti-join or merge anti-join without materializing a full subquery list. This is the preferred production pattern for anti-join problems.
The NOT IN approach translates internally to a nested-loop or hash semi-join depending on the optimizer. When the page_likes table is large, materializing the DISTINCT page_id subquery into a hash table and probing it for each page is efficient — O(n + m). However, NOT IN cannot use a standard B-tree index probe the same way a JOIN can. The LEFT JOIN anti-join pattern is usually better optimized: with an index on page_likes(page_id), the database performs an index lookup per page row and identifies misses in O(n log m) time. For very large page_likes tables, a partial index or covering index on page_likes(page_id) eliminates heap access entirely. A NOT EXISTS subquery is a third alternative that shares the NULL-safety of LEFT JOIN and often has identical execution plans in modern optimizers like PostgreSQL's query planner.
The biggest pitfall is using NOT IN when page_likes.page_id can be NULL — this causes the query to silently return zero rows because NULL comparisons evaluate to UNKNOWN, making every NOT IN check fail. Always prefer LEFT JOIN IS NULL or NOT EXISTS for anti-join patterns. Another mistake is omitting ORDER BY page_id ASC — the problem explicitly requires sorted output, and without it the result may pass some test cases by luck due to physical storage order but fail on others. Candidates also sometimes write WHERE page_id NOT IN (SELECT page_id ...) without DISTINCT, which works correctly but may hint at not understanding query efficiency.
NOT IN fails silently when the subquery contains any NULL — it returns zero rows rather than an error. SQL's three-valued logic means 'x NOT IN (1, 2, NULL)' is UNKNOWN, not TRUE, for any x. LEFT JOIN with IS NULL is immune to this: a non-matching row simply produces NULLs in the right-side columns regardless of the data values. Additionally, LEFT JOIN is typically better optimized with indexes and explicitly expresses the anti-join intent to other developers reading the code.
NOT EXISTS uses a correlated subquery that short-circuits on the first match, making it efficient for large tables. Like LEFT JOIN, it is NULL-safe because it tests for row existence, not value equality. Most modern query optimizers transform NOT EXISTS, NOT IN (on non-nullable columns), and LEFT JOIN IS NULL into the same anti-join execution plan. In practice, NOT EXISTS is semantically the clearest — 'return pages for which no like exists' — and is the preferred style in many SQL style guides.
The most impactful index is on page_likes(page_id) — it turns the anti-join lookup from a full table scan into an index scan, dramatically reducing I/O when page_likes is large. If the pages table is also large, an index on pages(page_id) ensures the ORDER BY can be satisfied without a sort step. A covering index on page_likes(page_id) — where page_id is the only column needed — allows index-only scans and avoids heap access entirely.