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..