Assignment: SQL Practice Questions | SQL for Beginners - Software Development PDF Download

Q1: Write an SQL query to find customers who have placed more than 3 orders in the last 6 months. Return the customer’s customer_id and the total number of orders they placed.

Sol: 
SELECT customer_id, COUNT(order_id) AS total_orders
FROM Orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY customer_id
HAVING total_orders > 3;

Q2:  Write an SQL query to return the employee_id and percentage increase in salary for employees who received a salary increase compared to their previous salary.
Tables:

Sol:
SELECT s1.employee_id,
    ROUND(((s2.salary - s1.salary) / s1.salary) * 100, 2) AS percentage_increase
FROM Salaries s1
JOIN Salaries s2
    ON s1.employee_id = s2.employee_id
    AND s1.salary_date < s2.salary_date
WHERE s2.salary > s1.salary;

Q3:  Find the top 3 users with the highest number of logins in the last 30 days.
Tables:

Sol: 
SELECT user_id, COUNT(*) AS login_count
FROM UserLogins
WHERE login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY user_id
ORDER BY login_count DESC
LIMIT 3;

Q4: Find transactions where the same user made identical purchases (same amount and transaction_date) more than once.
Sol: 
SELECT user_id, amount, transaction_date, COUNT(*) AS duplicate_count
FROM Transactions
GROUP BY user_id, amount, transaction_date
HAVING duplicate_count > 1;

Q5: Write an SQL query to calculate the cumulative sales for each product by date.
Tables:

Sol:
SELECT product_id, sale_date, amount,
     SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM Sales;