Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Quiz

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:

  1. Select the first two columns, ID and name. This should be straightforward.
  2. In a subquery, select the ID of the latest purchase for each customer.
  3. In a subquery, select the total quantity of all flowers purchased by each customer.
  4. Combine the three parts to get the final query.