SQL Interview Questions and Answers Asked in FAANG

To solve this, I first focus on the requirement: we only want candidates who have all three specific skills : Python, Tableau and PostgreSQL.

The data is stored in a row-per-skill format, so I filter the table to only those three skills using a WHERE ... IN clause. Then I group by candidate_id and use HAVING COUNT(DISTINCT skill) = 3 to ensure the candidate has all three required skills, not just one or two. Finally, I order by candidate_id as requested.

On a large dataset, I’d also consider adding an index on (skill, candidate_id) so the database can quickly locate relevant rows and perform the grouping efficiently.

SELECT
  candidate_id
FROM candidates
WHERE skill IN ('Python', 'Tableau', 'PostgreSQL')
GROUP BY candidate_id
HAVING COUNT(DISTINCT skill) = 3
ORDER BY candidate_id;

WITH sessions AS (
    SELECT
        server_id,
        status_time,
        session_status,
        LEAD(status_time) OVER (
            PARTITION BY server_id
            ORDER BY status_time
        ) AS next_status_time
    FROM server_utilization
),
running_intervals AS (
    SELECT
        server_id,
        status_time AS start_time,
        next_status_time AS stop_time
    FROM sessions
    WHERE session_status = 'start'
      AND next_status_time IS NOT NULL
)
SELECT
    SUM(TIMESTAMPDIFF(DAY, start_time, stop_time)) total_uptime_days
FROM running_intervals;

The first part of the query creates a temporary result called sessions. Inside this step, each event is ordered by time for its server and the LEAD() function is used to look ahead and retrieve the timestamp of the next event. This means that if you are looking at a start event, LEAD() provides the exact time when the same server later stopped. As a result, each start event becomes paired with its corresponding stop event. something like

In the next part, the query filters this list and keeps only the rows where the event is a “start” and there is a valid next event. This produces a clean list of running intervals. For example, if a server started on August 2nd at 10 AM and stopped on August 4th at 10 AM, this becomes one complete interval from which the uptime can be calculated.

The final step calculates the duration of each interval using TIMESTAMPDIFF(DAY, start_time, stop_time), which returns how many full days passed between the start and stop times. After calculating the durations of all these intervals, the query sums all of them into a single number that represents the total number of days the fleet of servers was operating.

Overall, the query works by identifying start–stop pairs, turning them into running intervals, converting those intervals into days and summing everything together to produce the final total uptime in days.

We can rewrite the query using a single CTE while keeping the exact same logic. In this simplified version, the CTE immediately renames status_time as start_time and the LEAD(status_time) value as end_time, while still keeping session_status so we know which rows are start events. That way, when we come out of the CTE, we already have everything we need :- the server id, the start time, the next end time and the session status.

Because of that, the final SELECT can now operate directly on sessions. We simply filter on session_status = 'start' and compute SUM(TIMESTAMPDIFF(DAY, start_time, end_time)) to get the total uptime in days. Logically, this is same to the earlier multi-CTE version. we are still pairing each start with the next event using LEAD, still only considering start rows and still summing the duration between start and end times.

WITH sessions AS (
    SELECT
        server_id,
        status_time AS start_time,
        LEAD(status_time) OVER (
            PARTITION BY server_id
            ORDER BY status_time
        ) AS end_time,
        session_status
    FROM server_utilization
)
SELECT
    SUM(TIMESTAMPDIFF(DAY, start_time, end_time)) AS total_uptime_days
FROM sessions
WHERE session_status = 'start';

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;

• LEFT JOIN keeps all pages from the pages table.
• For pages that have no matching rows in page_likes, the columns from page_likes become NULL.
• We check for: `WHERE pl.page_id IS NULL` This gives us pages that nobody has liked.

Select all pages whose page_id does not appear in the list of pages that have received likes. This filters out every page that appears in page_likes.

SELECT page_id
FROM pages
WHERE page_id NOT IN (
    SELECT page_id FROM page_likes
)
ORDER BY page_id;

This approach checks whether a page’s ID is not present in the list of pages that have received likes.

Although it looks straightforward, it has a major weakness: if the subquery returns even one NULL, the entire comparison fails and no rows are returned. This makes NOT IN unreliable unless you manually filter out NULLs in the subquery. Because of this pitfall, most developers avoid using NOT IN for anti-joins.

Note : If the subquery returns even one NULL, the entire result becomes empty this is a classic SQL pitfall.

Use an anti-join with NOT EXISTS to return pages that have no matching like records.

SELECT
    p.page_id
FROM pages p
WHERE NOT EXISTS (
    SELECT 1
    FROM page_likes pl
    WHERE pl.page_id = p.page_id
)
ORDER BY p.page_id;

It is highly reliable because it correctly handles NULLs and stops searching as soon as a matching row is found, making it efficient.

This pattern is widely used at FAANG companies because it clearly expresses intent and scales well with large datasets, especially when page_likes.page_id is indexed.