Instruction
Excellent! Looks like you've mastered all parts of the Basics of PostgreSQL course. Congratulations!
Before you finish, we have one final task for you. It's a bit more complicated than the previous tasks and you should combine the knowledge from different parts of the course to solve it. Let's take a look into a database at a florist's shop:
customer (id, name, country)
purchase (id, customer_id, year)
purchase_item (id, purchase_id, name, quantity)
Here customers purchase flowers.
Each purchase consists of one or more purchase_items. Each purchase_item belongs to one purchase (column purchase_id).
For example, if John Smith places an order for 2 roses and 1 lily, there will be two purchase_items in this specific purchase: one identifying a rose (the column quantity would contain the value 2 here) and one identifying a lily (column quantity would contain the value 1 here).
All purchase IDs are stored chronologically, i.e., the last ID placed in the shop has the highest ID.
Exercise
The owner of the shop would like to see each customer's
- ID (name the column
cus_id). - name (name the column
cus_name). - ID of their latest purchase (name the column
latest_purchase_id). - the total quantity of all flowers purchased by the customer, in all purchases, not just the last purchase (name the column all_items_purchased).
Remember, you need not use all columns from all the tables here – choose them carefully.
Stuck? Here's a hint!
Split building the query into parts:
- Select the first two columns, ID and name. This should be straightforward.
- In a subquery, select the ID of the latest purchase for each customer.
- In a subquery, select the total quantity of all flowers purchased by each customer.
- Combine the three parts to get the final query.
