Write a query to find the top 10 most frequent words in a table of text data.
SELECT word, COUNT(*) AS frequency
FROM (
SELECT regexp_split_to_table(text_column, '\s+') AS word
FROM table_name
) words
GROUP BY word
ORDER BY frequency DESC
LIMIT 10;
Write a query to find the 3rd highest salary in an employee table.
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;
Write a query to find the names of employees who have joined the company in the last 6 months.
SELECT name
FROM employees
WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 6 MONTH);
Write a query to find the number of distinct IP addresses that have accessed a website in the last 24 hours.
SELECT COUNT(DISTINCT ip_address) AS num_distinct_ips
FROM website_access_log
WHERE access_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR);
Write a query to find the top 5 countries with the highest number of active users on a website in the last month.
SELECT country, COUNT(DISTINCT user_id) AS num_active_users
FROM website_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 average response time for requests to a website in the last hour, grouped by hour.
SELECT DATE_TRUNC('hour', request_time) AS hour, AVG(response_time) AS avg_response_time
FROM website_request_log
WHERE request_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY hour;
Write a query to find the names of employees who have worked on more than 3 projects.
SELECT name
FROM employees
JOIN project_assignments ON employees.employee_id = project_assignments.employee_id
GROUP BY name
HAVING COUNT(DISTINCT project_id) > 3;
Write a query to find the products that have been ordered together with a specific product in more than 50% of orders.
SELECT p2.product_name, COUNT(*) AS num_orders
FROM order_details od1
JOIN order_details od2 ON od1.order_id = od2.order_id AND od1.product_id <> od2.product_id
JOIN products p1 ON od1.product_id = p1.product_id
JOIN products p2 ON od2.product_id = p2.product_id
GROUP BY p2.product_name
HAVING COUNT(*) >= 0.5 * (
SELECT COUNT(*)
FROM orders
)
ORDER BY num_orders DESC;
Write a query to find the average time between consecutive purchases for each customer.
SELECT customer_id, AVG(DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))) AS avg_time_between_purchases
FROM orders
GROUP BY customer_id;
Write a query to find the percentage of users who completed a specific action on a website in the last week, grouped by country.
SELECT country, 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 country;
Thanks for Reading