Facebook SQL Interview Questions


Write a query to find the top 10 users with the highest number of friends.

SELECT user_id, COUNT(*) AS num_friends
FROM user_friends
GROUP BY user_id
ORDER BY num_friends DESC
LIMIT 10;

Write a query to find the average engagement rate for posts in each category.

SELECT category, AVG(likes + comments + shares) AS avg_engagement_rate
FROM posts
GROUP BY category;

Write a query to find the top 5 countries with the highest number of active users in the last month.

SELECT country, COUNT(DISTINCT user_id) AS num_active_users
FROM user_activity
WHERE activity_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY country
ORDER BY num_active_users DESC
LIMIT 5;

Write a query to find the number of users who have logged in every day for the last week.

SELECT COUNT(*) AS num_users
FROM (
  SELECT user_id
  FROM user_activity
  WHERE activity_date >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
  GROUP BY user_id, DATE(activity_date)
  HAVING COUNT(*) = 7
) AS temp;

Write a query to find the top 10 pages with the highest number of followers.

SELECT page_name, SUM(followers) AS total_followers
FROM page_followers
GROUP BY page_name
ORDER BY total_followers DESC
LIMIT 10;

Write a query to find the top 5 cities with the highest number of users who have made purchases in the last quarter.

SELECT city, COUNT(DISTINCT user_id) AS num_purchasing_users
FROM user_purchases
JOIN users ON user_purchases.user_id = users.user_id
WHERE purchase_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
GROUP BY city
ORDER BY num_purchasing_users DESC
LIMIT 5;

Write a query to find the number of new users who have signed up in each month of the current year.

SELECT MONTH(signup_date) AS month, COUNT(*) AS num_new_users
FROM user_accounts
WHERE YEAR(signup_date) = YEAR(NOW())
GROUP BY month;

Write a query to find the top 5 articles with the highest number of views in the last week.

SELECT article_title, COUNT(*) AS num_views
FROM article_views
WHERE view_time >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
GROUP BY article_title
ORDER BY num_views DESC
LIMIT 5;

Write a query to find the percentage of users who completed a specific action on a website in the last week, grouped by device type.

SELECT device_type, 100.0 * COUNT(DISTINCT CASE WHEN action_completed = true THEN user_id END) / COUNT(DISTINCT user_id) AS percent_completed_action
FROM website_user_activity
WHERE activity_date >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
GROUP BY device_type;

Write a query to find the top 5 ads with the highest click-through rate in the last month.

SELECT ad_title, 100.0 * clicks / impressions AS click_through_rate
FROM ads
WHERE ad_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
ORDER BY click_through_rate DESC
LIMIT 5;

Thanks for Reading..