Database Performance Tuning Code Challenges & Tips

1. Write a SQL query to find the top 5 products with the highest sales revenue in the last quarter.

2. Given a table of user logins, write a SQL query to identify users who have not logged in for more than 30 days.

3. Write a SQL query to calculate the average transaction amount for each customer over the last year, and display only the customers who have more than 10 transactions.

4. Create an index for a table containing user information that optimizes query performance for searches based on last names.

5. Write a SQL query that retrieves all departments that have more than 10 employees and sorts them by the number of employees in descending order.

6. Using a `CROSS JOIN`, write a SQL query to create a list of all possible combinations of products and discounts.

7. Optimize the following SQL query for better performance: ```sql SELECT * FROM orders WHERE order_date >= '2021-01-01' AND order_date <= '2021-12-31'; ```

8. Write a SQL query to identify duplicate email addresses in a user account table.

9. Given a large dataset, write a SQL query that partitions data by month and retrieves monthly totals for the sales revenue.

10. Write a query using window functions to rank products based on their sales amount per category, showing only the top 3 products per category.