Write a query to find the average time taken to ship orders in each region.
SELECT region, AVG(DATEDIFF(shipped_date, order_date)) AS avg_time_to_ship
FROM orders
GROUP BY region;
Write a query to find the top 10 products that have the highest number of reviews.
SELECT product_name, COUNT(*) AS num_reviews
FROM product_reviews
JOIN products ON product_reviews.product_id = products.product_id
GROUP BY product_name
ORDER BY num_reviews DESC
LIMIT 10;
Write a query to find the names of employees who have worked on all projects.
SELECT name
FROM employees
WHERE NOT EXISTS (
SELECT project_id
FROM projects
WHERE NOT EXISTS (
SELECT employee_id
FROM project_assignments
WHERE project_assignments.project_id = projects.project_id
AND project_assignments.employee_id = employees.employee_id
)
);
Write a query to find the top 5 product categories with the highest revenue in the last quarter.
SELECT category, SUM(quantity * unit_price) AS revenue
FROM order_details
JOIN products ON order_details.product_id = products.product_id
JOIN categories ON products.category_id = categories.category_id
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
GROUP BY category
ORDER BY revenue DESC
LIMIT 5;
Write a query to find the number of distinct users who have made purchases in each month of the current year.
SELECT MONTH(order_date) AS month, COUNT(DISTINCT user_id) AS num_users
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())
GROUP BY month;
Write a query to find the top 10 most viewed pages on a website in the last week.
SELECT page_url, COUNT(*) AS num_views
FROM website_pageviews
WHERE view_time >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
GROUP BY page_url
ORDER BY num_views DESC
LIMIT 10;
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 cities with the highest number of orders in the last month.
SELECT city, COUNT(*) AS num_orders
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY city
ORDER BY num_orders 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 products that have the highest revenue in the last quarter, excluding products with a discount greater than 10%.
SELECT product_name, SUM(quantity * unit_price) AS revenue
FROM order_details
JOIN products ON order_details.product_id = products.product_id
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
AND discount <= 0.1
GROUP BY product_name
ORDER
