Analysing Book Store Sample Data with PostgreSQL

Writing complex SQL queries

Requirements

The Product Owner needs  a daily report showing :

• the order date
• the number of orders for that date
• the number of books ordered
• the total price of the orders
• the running total of books for the month
• the number of books from the same day last week (e.g. how this Monday compares to last Monday)

An annual report showing :

• year
• the book title
• the number of books sold
• the total price of the orders

Customer History Report showing :

• customer name
• month - year
• number of books purchased each month
• monthly expenditure by customer

DAILY REPORT

Output

I've created a status_updated view to filter the current status of the orders. So this view can be easily used in the following queries.

ANNUAL REPORT

Output

CUSTOMER HISTORY REPORT

Output