Google SQL Interview Questions


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