The SQL Interview Blueprint: 30 Questions to Guarantee Your Success

Get ready to transform your SQL skills from basic to brilliant with these 30 must-know interview questions and answers, covering everything from data manipulation to advanced analytics, and guarantee your success in your next Big Data engineering interview

1. Top N Products by Sales, Top N Products within Each Category, Top N Employees by Salaries

Question: Write a SQL query to find the top 5 products by sales, and then extend this to find the top 5 products within each category.

-- Top 5 products by sales
SELECT product_id, SUM(sales) AS total_sales
FROM sales_data
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 5;

-- Top 5 products within each category
WITH ranked_products AS (
  SELECT product_id, category_id, SUM(sales) AS total_sales,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY SUM(sales) DESC) AS rank
  FROM sales_data
  GROUP BY product_id, category_id
)
SELECT product_id, category_id, total_sales
FROM ranked_products
WHERE rank <= 5;

Explanation:

The first query uses a simple GROUP BY and ORDER BY to find the top 5 products by sales. The second query uses a Common Table Expression (CTE) to rank products within each category…