Apple SQL Interview Questions

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

2. Write a query to find the number of users who have purchased at least one app in the last month.

SELECT COUNT(DISTINCT user_id) AS num_purchasers
FROM app_purchases
WHERE purchase_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

3. Write a query to find the total revenue generated by each app in the last quarter.

ELECT app_name, SUM(revenue) AS total_revenue
FROM app_purchases
JOIN apps ON app_purchases.app_id = apps.app_id
WHERE purchase_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
GROUP BY app_name;

4. Write a query to find the number of apps in each category in the App Store.

SELECT category, COUNT(*) AS num_apps
FROM apps
GROUP BY category;

5. Write a query to find the average rating for each app in the App Store.

SELECT app_name, AVG(rating) AS avg_rating
FROM app_reviews
JOIN apps ON app_reviews.app_id = apps.app_id
GROUP BY app_name;

6. Write a query to find the number of apps that have been updated in the last week.

SELECT COUNT(*) AS num_updated_apps
FROM apps
WHERE update_date >= DATE_SUB(NOW(), INTERVAL 1 WEEK);

7. Write a query to find the top 5 countries with the highest number of App Store downloads.

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

8. Write a query to find the top 10 most profitable apps in the App Store.

SELECT app_name, SUM(revenue) AS total_revenue
FROM app_purchases
JOIN apps ON app_purchases.app_id = apps.app_id
GROUP BY app_name
ORDER BY total_revenue DESC
LIMIT 10;

9. Write a query to find the number of users who have rated each app in the App Store.

SELECT app_name, COUNT(DISTINCT user_id) AS num_raters
FROM app_reviews
JOIN apps ON app_reviews.app_id = apps.app_id
GROUP BY app_name;

10. Write a query to find the number of apps in the App Store that have not been downloaded in the last month.

SELECT COUNT(*) AS num_inactive_apps
FROM apps
LEFT JOIN app_downloads ON apps.app_id = app_downloads.app_id AND download_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
WHERE app_downloads.app_id IS NULL;

Thanks for Reading..