Data Science Intern SQL Assessment
Instructions:
• Use SQL to answer the following questions.
• Assume the following database schema.
• Write optimized and readable queries.
Tables
Table: sales
sale_id (INT)
product_id (INT)
store_id (INT)
quantity (INT)
price (FLOAT)
sale_date (DATE)
Table: products
product_id (INT)
product_name (VARCHAR)
category (VARCHAR)
Table: stores
store_id (INT)
store_name (VARCHAR)
city (VARCHAR)
Questions
Q1. Find the total revenue generated by each product.
Expected Output: product_id, total_revenue
Q2. List the top 5 products with highest total sales quantity.
Expected Output: product_name, total_quantity
Q3. Find the total sales per city.
Expected Output: city, total_sales
Q4. Find the average order value per store.
Formula: SUM(quantity * price) / COUNT(sale_id)
Expected Output: store_name, avg_order_value
Q5. Find the top selling product in each category based on revenue.
Expected Output: category, product_name, revenue
Q6. Find stores where total sales are above the average sales of all stores.
Expected Output: store_name, total_sales
Q7. Find the day with the highest sales revenue.
Expected Output: sale_date, total_revenue
Q8. Find products that were not sold in the last 30 days.
Expected Output: product_id, product_name
Q9. Find month-over-month revenue growth.
Expected Output: month, revenue, previous_month_revenue, growth_percentage
Q10. Calculate share of sales per product category.
Formula: category_sales / total_sales * 100
Expected Output: category, category_sales, share_percentage