Why aggregation matters in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count the number of orders in the orders table.

PostgreSQL

SELECT [1](order_id) FROM orders;

Drag options to blanks, or click blank then click option'

AMIN

BSUM

CMAX

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of COUNT will try to add order IDs, which may not make sense.

Using MAX or MIN returns the highest or lowest order ID, not the count.

The COUNT function counts the number of rows or non-null values in a column. Here, it counts all order IDs.

2fill in blank

medium

Complete the code to find the average price from the products table.

PostgreSQL

SELECT [1](price) FROM products;

Drag options to blanks, or click blank then click option'

AAVG

BMIN

CSUM

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM returns the total price, not the average.

Using COUNT returns the number of prices, not their average.

The AVG function calculates the average (mean) of numeric values in a column.

3fill in blank

hard

Fix the error in the query to get the total quantity sold from sales.

PostgreSQL

SELECT [1](quantity) FROM sales;

Drag options to blanks, or click blank then click option'

ACOUNT

BSUM

CAVG

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT counts rows, not the total quantity.

Using AVG returns the average quantity, not the total.

The SUM function adds all values in the quantity column to get the total sold.

4fill in blank

hard

Fill both blanks to get the number of orders per customer.

PostgreSQL

SELECT customer_id, [1](order_id) FROM orders GROUP BY [2];

Drag options to blanks, or click blank then click option'

ACOUNT

BSUM

Ccustomer_id

Dorder_id

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by order_id would group each order separately.

Using SUM instead of COUNT counts values, not rows.

We count orders per customer, so use COUNT(order_id) and group by customer_id.

5fill in blank

hard

Fill all three blanks to find the average price per category for products priced above 100.

PostgreSQL

SELECT [1], [2](price) FROM products WHERE price [3] 100 GROUP BY category;

Drag options to blanks, or click blank then click option'

Acategory

BAVG

C>

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of AVG calculates total price, not average.

Using < instead of > filters prices below 100.

We select the category, calculate the average price with AVG(price), filter prices greater than 100, and group by category.