SQL interview Questions with answers that have been asked in Accenture:
Question 1: Year-over-Year Growth Rate of Total Sales for Each Product Category
Tables: sales (sale_id, product_id, sale_amount, sale_date), products (product_id, category)
Solution:
WITH yearly_sales AS (
SELECT
[Link],
EXTRACT(YEAR FROM s.sale_date) AS sale_year,
SUM(s.sale_amount) AS total_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY [Link], EXTRACT(YEAR FROM s.sale_date)
),
sales_growth AS (
SELECT
category,
sale_year,
total_sales,
LAG(total_sales) OVER (PARTITION BY category ORDER BY sale_year) AS
previous_year_sales
FROM yearly_sales
)
SELECT
category,
sale_year,
total_sales,
((total_sales - previous_year_sales) / previous_year_sales) * 100 AS yoy_growth_rate
FROM sales_growth
WHERE previous_year_sales IS NOT NULL
AND category NOT IN (
SELECT category
FROM yearly_sales
WHERE total_sales < 10000
)
ORDER BY category, sale_year;
Question 2: Top 3 Products by Sales Amount in Each Quarter
Tables: sales (sale_id, product_id, sale_amount, sale_date)
Solution:
WITH quarterly_sales AS (
SELECT
product_id,
EXTRACT(YEAR FROM sale_date) AS sale_year,
EXTRACT(QUARTER FROM sale_date) AS sale_quarter,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY product_id, EXTRACT(YEAR FROM sale_date), EXTRACT(QUARTER FROM
sale_date)
),
ranked_sales AS (
SELECT
product_id,
sale_year,
sale_quarter,
total_sales,
RANK() OVER (PARTITION BY sale_year, sale_quarter ORDER BY total_sales DESC) AS
sales_rank
FROM quarterly_sales
)
SELECT
product_id,
sale_year,
sale_quarter,
total_sales
FROM ranked_sales
WHERE sales_rank <= 3
ORDER BY sale_year, sale_quarter, sales_rank;
Question 3: Customers Who Made Purchases on at Least Three Consecutive Days
Tables: purchases (purchase_id, customer_id, purchase_date)
Solution:
WITH daily_purchases AS (
SELECT
customer_id,
purchase_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) -
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY DATEADD(day,
-ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date),
purchase_date)) AS grp
FROM purchases
),
consecutive_days AS (
SELECT
customer_id,
MIN(purchase_date) AS start_date,
MAX(purchase_date) AS end_date,
COUNT(*) AS consecutive_days
FROM daily_purchases
GROUP BY customer_id, grp
)
SELECT
customer_id,
start_date,
end_date,
consecutive_days
FROM consecutive_days
WHERE consecutive_days >= 3;
Question 4: Suppliers with an Average Delivery Time of Less Than 3 Days Over the Last Year
Tables: deliveries (delivery_id, supplier_id, delivery_date, order_date)
Solution:
SELECT
supplier_id,
AVG(DATEDIFF(day, order_date, delivery_date)) AS average_delivery_time
FROM deliveries
WHERE delivery_date >= DATEADD(year, -1, CURRENT_DATE)
GROUP BY supplier_id
HAVING AVG(DATEDIFF(day, order_date, delivery_date)) < 3;
Question 5: Employees Who Consistently Met or Exceeded Sales Targets for Each Quarter in
the Last Two Years
Tables: employee_sales (employee_id, quarter, sales_amount, target_amount)
Solution:
WITH quarterly_performance AS (
SELECT
employee_id,
quarter,
EXTRACT(YEAR FROM quarter) AS year,
SUM(sales_amount) AS total_sales,
SUM(target_amount) AS total_target
FROM employee_sales
WHERE EXTRACT(YEAR FROM quarter) >= EXTRACT(YEAR FROM CURRENT_DATE) -
2
GROUP BY employee_id, quarter, EXTRACT(YEAR FROM quarter)
),
consistent_performance AS (
SELECT
employee_id,
year,
CASE
WHEN SUM(CASE WHEN total_sales >= total_target THEN 1 ELSE 0 END) =
COUNT(*) THEN 'Met or Exceeded'
ELSE 'Not Consistent'
END AS performance
FROM quarterly_performance
GROUP BY employee_id, year
)
SELECT
employee_id
FROM consistent_performance
WHERE performance = 'Met or Exceeded';
Question 6: Moving Average of Daily Sales Over a 30-Day Window for Each Product
Tables: sales (sale_id, product_id, sale_amount, sale_date)
Solution:
SELECT
product_id,
sale_date,
AVG(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS
BETWEEN 29 PRECEDING AND CURRENT ROW) AS moving_avg_sales
FROM sales;
Join for more: 👇
[Link]
Here you can find essential SQL Interview Resources 👇
[Link]
All the best 👍👍