FAANG SQL Interview Questions

FAAND SQL Complex Interview Questions:

1. Write a query to find the top 5 countries with the highest number of active users on a social media platform.

SELECT country, COUNT(DISTINCT user_id) AS num_active_users
FROM user_activity
JOIN users ON user_activity.user_id = users.user_id
GROUP BY country
ORDER BY num_active_users DESC
LIMIT 5;

2. Write a query to find the average time spent by users on a news website per visit.

SELECT AVG(time_spent) AS avg_time_spent
FROM user_sessions
WHERE website = 'news';

3. Write a query to find the top 10 most popular hashtags on a social media platform based on the number of posts they are used in.

SELECT hashtag, COUNT(*) AS num_posts
FROM post_hashtags
JOIN hashtags ON post_hashtags.hashtag_id = hashtags.hashtag_id
GROUP BY hashtag
ORDER BY num_posts DESC
LIMIT 10;

4. Write a query to find the top 5 most visited pages on a website based on the number of page views.

SELECT page_title, COUNT(*) AS num_page_views
FROM page_views
JOIN pages ON page_views.page_id = pages.page_id
GROUP BY page_title
ORDER BY num_page_views DESC
LIMIT 5;

5. Write a query to find the top 10 most listened-to songs on a music streaming platform based on the number of plays.

SELECT song_title, COUNT(*) AS num_plays
FROM song_plays
JOIN songs ON song_plays.song_id = songs.song_id
GROUP BY song_title
ORDER BY num_plays DESC
LIMIT 10;

6. Write a query to find the number of active subscribers for a video streaming platform in the last month.

SELECT COUNT(DISTINCT user_id) AS num_active_subscribers
FROM video_subscriptions
WHERE subscription_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

7. Write a query to find the top 5 bestselling electronics products on an e-commerce website based on the total revenue generated.

SELECT product_name, SUM(price * quantity) AS total_revenue
FROM sales
JOIN products ON sales.product_id = products.product_id
WHERE product_category = 'electronics'
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 5;

8. Write a query to find the top 10 most active forums on an online community platform based on the number of posts, comments, and likes.

SELECT forum_name, SUM(posts + comments + likes) AS activity_score
FROM forum_activity
GROUP BY forum_name
ORDER BY activity_score DESC
LIMIT 10;

9. Write a query to find the top 5 bestselling games on a gaming platform based on the total revenue generated.

SELECT game_title, SUM(price * quantity) AS total_revenue
FROM game_sales
JOIN games ON game_sales.game_id = games.game_id
GROUP BY game_title
ORDER BY total_revenue DESC
LIMIT 5;

• Write a query to find the number of active users in a language learning app in the last week.

SELECT COUNT(DISTINCT user_id) AS num_active_users
FROM lessons
WHERE lesson_date >= DATE_SUB(NOW(), INTERVAL 1 WEEK);

• Write a query to find the top 5 best-selling products on an e-commerce website based on the total revenue generated.

SELECT product_name, SUM(price * quantity) AS total_revenue
FROM sales
JOIN products ON sales.product_id = products.product_id
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 5;

• Write a query to find the average rating for each movie in a streaming platform.

SELECT movie_title, AVG(rating) AS avg_rating
FROM movie_ratings
JOIN movies ON movie_ratings.movie_id = movies.movie_id
GROUP BY movie_title;

• Write a query to find the number of active users in a messaging app in the last week.

SELECT COUNT(DISTINCT user_id) AS num_active_users
FROM messages
WHERE message_date >= DATE_SUB(NOW(), INTERVAL 1 WEEK);

• Write a query to find the top 100 most viewed videos on a video-sharing platform based on the number of views.

SELECT video_title, COUNT(*) AS num_views
FROM video_views
JOIN videos ON video_views.video_id = videos.video_id
GROUP BY video_title
ORDER BY num_views DESC
LIMIT 100;

• Write a query to find the top 50 cities with the highest number of orders on an online food delivery platform.

SELECT city, COUNT(*) AS num_orders
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
GROUP BY city
ORDER BY num_orders DESC
LIMIT 50;

• Write a query to find the number of active subscribers for a music streaming platform in the last month.

SELECT COUNT(DISTINCT user_id) AS num_active_subscribers
FROM subscriptions
WHERE subscription_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

8. Write a query to find the top 10 most popular apps in the App Store based on the number of downloads.

SELECT app_name, SUM(downloads) AS total_downloads
FROM app_downloads
GROUP BY app_name
ORDER BY total_downloads DESC
LIMIT 10;

9. Write a query to find the top 50 bestselling books on an online bookstore based on the total revenue generated.

SELECT book_title, SUM(price * quantity) AS total_revenue
FROM book_sales
JOIN books ON book_sales.book_id = books.book_id
GROUP BY book_title
ORDER BY total_revenue DESC
LIMIT 50;

10. Write a query to find the number of active users in a fitness app in the last week.

SELECT COUNT(DISTINCT user_id) AS num_active_users
FROM workouts
WHERE workout_date >= DATE_SUB(NOW(), INTERVAL 1 WEEK);

Thanks For Reading