Best way to deal with the (hard level) question in SQL interview

In Meta’s data science and data engineering interviews, candidates often encounter complex SQL questions that assess their ability to handle real-world data scenarios. One such challenging question is:

Question: Average Post Hiatus

Given a table of Facebook posts, for each user who posted at least twice in 2024, write a SQL query to find the number of days between each user’s first post of the year and last post of the year in 2024. Output the user and the number of days between each user’s first and last post.

Table Schema:

• posts
  • user_id (INTEGER): ID of the user who made the post
  • post_id (INTEGER): Unique ID of the post
  • post_date (DATE): Date when the post was made

Approach:

1. Filter Posts from 2024:
   • Select posts where the post_date falls within the year 2024.
2. Identify First and Last Post Dates:
   • For each user, determine the minimum (first_post_date) and maximum (last_post_date) post dates in 2024.
3. Calculate the Difference in Days:
   • Compute the difference in days between last_post_date and first_post_date for each user.
4. Filter Users with At Least Two Posts:
   • Ensure that only users who have posted more than once are considered.

SQL Solution:

Explanation:

• Common Table Expression (CTE): user_posts_2024 filters posts from 2024 and groups them by user_id. It calculates the first and last post dates and counts the total posts per user.
• Main Query: Selects users with more than one post and computes the difference in days between their first and last posts using the DATEDIFF function.

Key Considerations:

• Date Functions: The DATEDIFF function calculates the difference between two dates. Note that the syntax may vary depending on the SQL dialect. For instance, in some systems, the order of parameters in DATEDIFF might be reversed.
• Filtering by Date: Ensure the date filter accurately captures the entire year of 2024.
• Handling Users with Single Posts: By counting posts per user and filtering out those with only one post (post_count > 1), we ensure that only users with multiple posts are considered.

Personal Experience:

In my experience preparing for SQL interviews at major tech companies, including Meta, it’s crucial to practice a variety of SQL problems that test different aspects of data manipulation and analysis. Resources like DataLemur offer curated questions that mirror the complexity and style of actual interview scenarios.

datalemur.com

Additionally, engaging in mock interviews and solving problems from platforms like StrataScratch can provide practical experience and enhance problem-solving skills.

stratascratch.com

By systematically practicing such problems and understanding the underlying concepts, candidates can develop the proficiency needed to excel in SQL interviews at Meta and similar companies.