Postgres Problem-Solving and Analytical Questions

1. Given a table of sales records with columns for product_id, sale_date, and sale_amount, how would you efficiently find the product with the highest total sales for a specific month?

2. You have a table containing user login timestamps. How would you write a query to determine the average number of logins per user in the last month?

3. Given a table of employees, write a SQL query that identifies the top 5 employees who have the highest total project hours, along with their corresponding department names.

4. Assume you have two tables: orders and customers. How would you write a query to list all customers who have never placed an order?

5. Explain how you would approach optimizing a slow-running query that aggregates large data sets. What tools and strategies would you employ?

6. You have a table of transactions with a timestamp column. How would you write a query to find all transactions that occurred during the last week, grouped by day?

7. Design a schema for a simple online bookstore, and explain how you would enforce data integrity across the tables.

8. Given a complex query that joins multiple tables, how would you analyze and break down the query execution plan to identify bottlenecks?

9. How would you implement a rolling average for daily sales over a 30-day window in a SQL query? Provide the relevant SQL syntax.

10. You have a JSONB column that contains various user preferences. How would you write a query to extract and count specific preference values from that column?